I have a problem with a very specific fact pattern and am looking for any guidance.
As part of a large XLAM, there is a portion of code that cycles thru a list of workbooks that were opened and closes them automatically for the user using a basic "Workbooks(strBookName).Cl
ose , False" statment inside a For each ... Next loop.
Normally the logic works fine, but if there are multiple files being closed back-to-back that are stored in the company's document managment system (which causes a slight delay as the file's checkout status is released), as the code loops through to the next file to close it generates a Run Time Error 1004 Method 'Close of object_workbook' error.
Behavior seems pretty isolated so far to only having an issue on closing workbooks where there are other .com add-ins installed on the machine that are executing logic for checkin of the workbook as part of any closing of those files.
Debug brings you in exactly to the .close statment for the next file being looped through. and if you click to continue it finishes fine.
If i just step thru the code loops using F8, it works fine.
If it loops thru multiple locally saved (non doc mgt system stored files) and just a single doc mgt stored file, it works fine.
The 1004 error only surfaces if 2 or more doc mgnt stored files are closed back to back.
Likewise, even if i loop thru workbooks stored in the doc mgnt system, if i slow down the loop execution by inserting a msgbox "Click OK" immediately before each books .close command in the loop, it works fine.
So it is behaving like the .close commands are executing too quickly back to back for Excel to handle. Perhaps while the 3rd party COM addin is running to see if the file needs to be checked in upon close. Is that possible? Any thoughts or suggestions of how to handle? Users are not going to tolerate me putting a msgbox in each loop to slow it down. I've never had much luck with building in timer-based delays (but maybe i was just doing it wrong :-) )
Relevant Code (just for example)
For Each c In TrackOpenList
strBookName = c.Value
Workbooks(strBookName).Saved = True
Application.DisplayAlerts = False
'Msgbox '"Click OK" 'If this is uncommented so that the user clicks OK between loops, no issue.
Workbooks(strBookName).Close , False
'<Note: Error always occurs afer the first loop thru, and always results in DEBUG
'at the 'Workbooks(strBookName).Close, False' line of code; and always continues
to finish fine if you click to PLAY at the point of DEBUG>