在 VBA 中的 Access 2007 中编辑后如何保存 Excel 电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21870416/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how do i save a excel spreadsheet after editing it in access 2007 in VBA
提问by Scuttle
I am trying to have Access 2007 open an Excel spreadsheet, add data to the cells, and then save the spreadsheet. I have a button in an Access 2007 form which activates the following code. Here is what I have so far, but every "save command" doesn't work. Does any one have any idea what to use?
我试图让 Access 2007 打开 Excel 电子表格，将数据添加到单元格，然后保存电子表格。我在 Access 2007 表单中有一个按钮，可以激活以下代码。这是我到目前为止所拥有的，但每个“保存命令”都不起作用。有没有人知道要使用什么？
Private Sub buttonExcel_Click()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
' Create a New Excel WorkBook
Set oExcel = CreateObject("Excel.Application")
' Optional, Open a current Workbook from a file directory
oExcel.Workbooks.Open ("Z:_Volume Management\ACCESS\EMAILTEMPLATES\test.xlsx")
' Make the Excel Workbook visible to the users
oExcel.Visible = True
' Define the Workbook from Excel
Set oBook = oExcel.ActiveWorkbook
' Define the Workskeet from the Workbook (1="Sheet1", 2="Sheet2", etc...)
Set oSheet = oBook.Worksheets(1)
' Write Data to the Worksheet (Block "A1" is the first row, first column)
oSheet.Range("A1").Value = "Hello World"
' Save the Excel Workbook
oExcel.SaveAs ("Z:_Volume Management\ACCESS\EMAILTEMPLATES\test.xlsx") ' DOESNT WORK
oExcel("Z:_Volume Management\ACCESS\EMAILTEMPLATES\test.xlsx").Save ' DOESNT WORK
oBook.SaveAs ("Z:_Volume Management\ACCESS\EMAILTEMPLATES\test.xlsx") ' DOESNT WORK
oBook("Z:_Volume Management\ACCESS\EMAILTEMPLATES\test.xlsx").Save ' DOESNT WORK
' Closes Excel as asks to save the Workbook
oExcel.Workbooks.Close
' Quit Excel
'oExcel.Quit
End Sub
Thanx!
谢谢！
采纳答案by Tim Williams
Private Sub buttonExcel_Click()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open ("Z:_Volume Management\" & _
"ACCESS\EMAILTEMPLATES\test.xlsx")
oExcel.Visible = True
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Hello World"
oBook.Save
oBook.Close
'or...
'oBook.Close True 'True = save changes
oExcel.Quit
End Sub
