Excel 2007 - Run Time Error 1004 Method 'Close' of object_Workbook

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).Close , 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 :-) )

Thanks!

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 
    Next c

....

'<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>

Open in new window

shellysuelllAsked:
Who is Participating?
 
Glenn RayExcel VBA DeveloperCommented:
I tried to replicate this across a slow network connection and didn't have any problems (closing six files).

If you did want to introduce a pause, you might try this modification to your code:
Option Explicit
Sub closer()
    Dim TrackOpenList As Range
    Dim c As Object
    Dim strBookName As String
    
    Application.DisplayAlerts = False
    For Each c In TrackOpenList
        strBookName = c.Value
        Workbooks(strBookName).Saved = True
        Workbooks(strBookName).Close savechanges:=False
        PauseIt (5) 'about 5 seconds - change the value as-needed
    Next c
    Application.DisplayAlerts = True
End Sub

Private Sub PauseIt(varPauseAmt As Variant)
    Dim varstart As Date
    varstart = Timer
    Do While Timer < varstart + varPauseAmt
        DoEvents
    Loop
End Sub

Open in new window


-Glenn
0
 
Glenn RayExcel VBA DeveloperCommented:
After you close statement add the following:
DoEvents

Open in new window

This will turn over processing to the OS and give it time to complete the operation.

See this support page for more info.
http://support.microsoft.com/kb/118468

Regards,
Glenn
Sent from my Windows Phone
0
 
shellysuelllAuthor Commented:
Glenn,  
Appreciate the suggestion.  Tried that.  Same result
     For Each c In TrackOpenList
            strBookName = c.Value
               Workbooks(strBookName).Saved = True 
                Application.DisplayAlerts = False
             Workbooks(strBookName).Close , False
              DoEvents
       Next c

Open in new window


Even tried to slow it down further with a doevents immediately before and immediately after the .close statement.
Still hit the 1004 if closing 2 files stored in the doc mgnt system back to back.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
shellysuelllAuthor Commented:
Additional info:
since DoEvents wasn't working, i tried adding in an Application.Wait statement.  Interesting to note that it wasn't until the way was for over 8-10 secs that the 2 files could close 'back to back' without a 1004 error.

Especially interesting since if i put back in a msgbox "OK" before each of the .Close loops, even if click on that box immediately (< 1 sec) it finishes fine without a 1004 error.

So it doesn't seem to be purely a timing/wait for OS to do something, since as soon as a user takes action on a msgbox popup the next loop of .close can execute without the 1004.
0
 
shellysuelllAuthor Commented:
Glenn
Thanks for the follow-up.  If I just put the files on a slow network that I hit via VPM it works fine for me too, there is something specifically 'slow' about this DocMgntSystem.

Definitely not the first quirky behavior we've seen arising from its Excel integration add-ins.  If I trap events I can tell that its intercepting the native Excel .close, .open and .save commands -- but since the code isn't exposed and we can't control it, I guess we'll just work around it.

Pause will be fine.
Thanks!
0
 
shellysuelllAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.