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.
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.
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.
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.
No comments:
Post a Comment