VBA 不会关闭以编程方式打开的工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10012199/
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 won't close workbooks opened programatically
提问by Danny Oancea
I have written a VBA macro in an excel spreadsheet that opens all workbooks in a specific location and pulls data out of those workbooks and validates and copies it to the active workbook. It then opens the next workbook in the directory and repeats the process until all files in the directory have been read through.
我在 Excel 电子表格中编写了一个 VBA 宏，该宏可打开特定位置的所有工作簿，并从这些工作簿中提取数据并验证并将其复制到活动工作簿中。然后它打开目录中的下一个工作簿并重复该过程，直到读完目录中的所有文件。
Everything works, except I cant seem to get the target workbooks to close once opened. This includes closing Excel. I have to kill the process in the task manager or in powershell to free the workbook from system memory.
一切正常，除了我似乎无法在打开后关闭目标工作簿。这包括关闭 Excel。我必须在任务管理器或 powershell 中终止进程才能从系统内存中释放工作簿。
Set fs = CreateObject("Scripting.FileSystemObject")
strExcelFileName = Dir(ThisWorkbook.Path & "\Certs\*.xls", vbNormal)
Set xlApp = CreateObject("Excel.Application")
Do While (strExcelFileName <> "")
xlApp.Workbooks.Open (ThisWorkbook.Path & "\Certs\" + strExcelFileName)
'code to run for each workbook opened
***xlApp.Workbooks.Close*** 'when present, Excel freezes
strExcelFileName = Dir 'next file in directory
Loop
When the xlApp.Workbooks.Close line is present and called in the program, excel freezes every time. Without it, I can run through 3-5 workbooks before the system is overwhelmed and freezes. I then must kill those processes, move those files out, move 3 more in and repeat until all files have been processed this way. It takes about an hour and a half to go through 50.
当 xlApp.Workbooks.Close 行出现并在程序中调用时，excel 每次都会冻结。没有它，我可以在系统不堪重负和冻结之前浏览 3-5 个工作簿。然后我必须终止这些进程，将这些文件移出，再移入 3 个并重复，直到所有文件都以这种方式处理完毕。50级大约需要一个半小时。
What I am trying to do is have the workbook where the data is grabbed from closed before the next one is opened.
我想要做的是在打开下一个之前关闭从其中抓取数据的工作簿。
ActiveWorkbook.Close
This attempts to close the workbook where the macro is running, not the workbook that has been opened to be read from.
这会尝试关闭运行宏的工作簿，而不是已打开以供读取的工作簿。
回答by Tony Dallimore
You already have Excel open so you do not need to open another copy. Try:
您已经打开了 Excel，因此您无需再打开另一个副本。尝试：
Set WBookOther = Workbooks.Open(PathCrnt & FileNameCrnt)
:
:
WBookOther.Close SaveChanges:=False
This earlier answer of minewhich cycles though every workbook in the current folder may help further.
我的这个较早的答案在当前文件夹中的每个工作簿中循环可能会进一步提供帮助。
回答by Jean-Fran?ois Corbett
I ran your code without any problem whatsoever, but here's a cleaner alternative:
我运行你的代码没有任何问题，但这里有一个更干净的选择：
Dim strExcelFileName As String
Dim xlApp As Object
Dim wbk As Workbook
strExcelFileName = Dir(ThisWorkbook.Path & "\Certs\*.xls", vbNormal)
Set xlApp = CreateObject("Excel.Application")
Do While (strExcelFileName <> "")
Set wbk = xlApp.Workbooks.Open(ThisWorkbook.Path & "\Certs\" _
+ strExcelFileName) ' set a reference to the workbook you're opening
'code to run for each workbook opened
'Your code should use the "wbk" reference to make sure
' you're accessing the correct workbook.
wbk.Close ' close the workbook using the reference you set earlier
strExcelFileName = Dir 'next file in directory
Loop
