Saturday, January 26, 2019

excel - VBA won't close workbooks opened programatically

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

plot explanation - Why did Peaches&#39; mom hang on the tree? - Movies &amp; TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...