Solved

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

Posted on 2014-10-21
6
401 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
  • 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now