Solved

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

Posted on 2014-10-21
6
588 Views
Last Modified: 2014-10-22
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

0
Comment
Question by:shellysuelll
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40396202
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
 

Author Comment

by:shellysuelll
ID: 40396216
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
 

Author Comment

by:shellysuelll
ID: 40396285
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40398227
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
 

Author Comment

by:shellysuelll
ID: 40398483
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
 

Author Closing Comment

by:shellysuelll
ID: 40398485
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question