适用于 MS Access 的 VBA：无需打开即可写入 excel 文件，写入最后一行，保存并关闭
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42241747/
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
VBA for MS Access: write to an excel file without opening, write to last row, save and close
提问by user3426335
I am using VBA in MS Access for the first time and cannot get the following right: Launch an excel file(without actually opening the file), then write to the last rowin the excel file, and then savethe file (with the same path and name as before, essentially replace the previous file), then closethe excel file.
我第一次在 MS Access 中使用 VBA 并且无法获得以下正确结果： 启动一个 excel 文件（没有实际打开文件），然后写入excel 文件的最后一行，然后保存文件（使用相同的path 和 name 和以前一样，本质上是替换以前的文件），然后关闭excel 文件。
Please assist! So far I can write to an excel file, but cannot save and close without closing the whole MS Access application. If you could please give a sample of working code to do the above, I will tailor it for my requirements.
请协助！到目前为止，我可以写入一个 excel 文件，但如果不关闭整个 MS Access 应用程序，就无法保存和关闭。如果您可以提供一个工作代码示例来执行上述操作，我将根据我的要求对其进行定制。
Thanks! Christine
谢谢！克里斯汀
回答by TheSilkCode
First of all, in order to update and save a file like you want to, you have to open it first- so it is a little confusing/contradictory when you say that you don't want to 'actually open' an excel file... I took it to meant that you just don't want the excel application showing- which you would want something like this:
首先，为了像您想要的那样更新和保存文件，您必须先打开它——所以当您说您不想“实际打开”一个 excel 文件时，这有点令人困惑/矛盾。 .. 我认为它的意思是你只是不希望 excel 应用程序显示 - 你想要这样的东西：
Public Sub demoCode()
Dim excelApp As Excel.Application
Dim targetWB As Workbook
Dim targetRange As Range
'Create new Excel Application
Set excelApp = New Excel.Application
'Keep hidden
excelApp.Visible = False
'Have new Excel App open workbook
Set targetWB = excelApp.Workbooks.Open("C:\Filename.xlsm")
'Set targetRange to 1 row past the first sheet's usedrange
Set targetRange = targetWB.Sheets(1).Range(targetWB.Sheets(1).UsedRange.address)(targetWB.Sheets(1).UsedRange.Rows.Count + 1, 1)
'Paste @ targetRange
'Close and save workbook
targetWB.Close (True)
'Close Excel App
excelApp.Quit
End Sub
Hope this helps, TheSilkCode
希望这会有所帮助，TheSilkCode
