How do I prevent my macro from hanging when Opening or Closing a workbook?

USAMax

Well-known Member
Joined
May 31, 2006
Messages
827
I have a macro that opens workbooks collects information and closes the workbook.

It runs perfectly when I have a break point in the program, I just have to hit F5 to continue.

I am running a UserForm as vbModeless to show where I am in the process.

When I let it run it locks on a few workbooks when it Opening or Closing a workbook. Currently it is always the 11th workbook.

Is there a fix for this?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
Have you confirmed the 11th workbook exists? Or the file name and extension are an exact match? Can you show any code?
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
827
The eleventh workbook does exist, when I run the program with a break it doesn't have a problem with it. I was thinking the Open is too soon after the Close but it fails on the Close too.
 

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
What's the error? I strongly suspect for anyone to help here, you'll need to share some code samples for evaluation.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,804
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is there any code in the workbook(s) you are opening?

Are any of them particularly large?

Have you set calculation to manual?
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
827
There are no errors, it just locks up. I know Excel stops updating the screen when it is running a lot of data so I just walked away for 30 minutes and it should have been done but the screen was the same.
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
827

ADVERTISEMENT

Hi Norie, it is always good to hear from you.

I do have the calculation set to manual.

The largest file is 198K and that is not a typo.

When it fails, this is the code where it fails. The first line updates the userform so I know it is OPENING WORKBOOK. Again, stepping through it always works.
Code:
                FRM.FRM_Label = vbLf & vbLf & "Workbook:  " & strWorkbookName & vbLf & vbLf & "Files(s):  " & intCounter & vbLf & "OPENING WORKBOOK"  '               Update the UserForm label with the workbook being opened
                FRM.Repaint                                     
                Workbooks.Open Filename:=strSharePointPath & strWorkbookName, ReadOnly:=True         
                intCounter = intCounter + 1
                
                Set wbCur = ActiveWorkbook                      
                Set wsCur = Sheets("Planned Obsolescence Template") 
                If ActiveSheet.Name <> Sheets(1).Name Then
                    'If InTestMode Then Stop
                    Sheets(1).Activate
                End If
                strFileAuthor = wbCur.BuiltinDocumentProperties ("Last Author") 
                
                FRM.FRM_Label = "Requestor: " & strFileAuthor & vbLf & vbLf & "Workbook:  " & strWorkbookName & vbLf & vbLf & "Files(s):  " & intCounter & vbLf & "UNPROTECT THE WORKBOOK AND SEARCH FOR THE SHORT NOTICE COLUMN'               Update the UserForm label with the workbook being opened"
                FRM.Repaint
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
827
I decided to update the userform for each line of code I have shown above and it is running without any errors. Very strange!

If it does fail in this section at least I will know what line of code it is failing on.
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
827
Spoke too soon, it crashed once when it was closing a workbook so I activated the workbook before closing it. I don't know if this will help but at this point I'll try anything.

Then after that it locked up when opening the 54th workbook and started again after five minutes. Now it is locked up again when closing the 55th workbook. Maybe a network issue as the files are on the network???

At this point I'll try just about anything.
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
827
To show everyone how desperate I am I've added this line before the Open command to mimic a user pressing F5 at a Break Point.

Code:
Application.Wait Now + TimeValue("00:00:" & Int((7) * Rnd + 1))

I don't know if it is working but I noticed this message, "updating workflow status". These files are on a SharePoint server. Could this have something to do with the long delays?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,525
Messages
5,523,377
Members
409,514
Latest member
MarkZuckerberg

This Week's Hot Topics

Top