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

[USAMax]

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?

 

[bs0d]

Have you confirmed the 11th workbook exists? Or the file name and extension are an exact match? Can you show any code?

 

[USAMax]

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]

What's the error? I strongly suspect for anyone to help here, you'll need to share some code samples for evaluation.

 

[Norie]

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

Are any of them particularly large?

Have you set calculation to manual?

 

[USAMax]

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]

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.

                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]

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]

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]

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.

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?