Error Handeler for Workbooks.Open delay?

[USAMax]

I have a loop that goes through every xlxs & xlsm file in a SharePoint folder but every now and then it hangs while opening a random workbook. It can run fine most of the time but other times it hangs.

This is the command I use to open the file:
Workbooks.Open Filename:=strSharePointPath _
& strWorkbookName, _
ReadOnly:=True ' Open the Current Workbook as Read Only

Is there a way to detect when it hangs too long? If so, I need it to GoTo ReportWorkbookNotProcessed.

Thank you...

 

[pbornemeier]

You might be able to use Application.OnTime before the Workbooks.Open line and if the workbook is successfully opened, cancel that Application.OnTime statement.
I am not sure if the OnTime procedure will execute if the program is waiting for a file to be opened. See VBA help on this function for more info.

Option Explicit

Sub ScheduleAProcess()


    Dim dteTime As Date
    Dim strSharePointPath As String
    Dim strWorkbookName As String
    
    'Schedule the procedure to run 1 min from now
    dteTime = Now + TimeValue("00:01:00")
    Application.OnTime dteTime, "ReportWorkbookNotProcessed"   'This must be a separate sub/function, not part of the same one.
    Application.StatusBar = "RWNP Subroutine Scheduled"
    
    On Error Resume Next
    Workbooks.Open Filename:=strSharePointPath _
        & strWorkbookName, _
        ReadOnly:=True ' Open the Current Workbook as Read Only
    If Err.Number = 0 Then
        'File Opened with no problem
        Application.StatusBar = False
        'Cancel the pending procedure
        Application.OnTime EarliestTime:=dteTime, _
            Procedure:="ReportWorkbookNotProcessed", Schedule:=False
    End If
    On Error GoTo 0
    Application.StatusBar = False
    
End Sub

Check out the answers to this post as a possible way to see why they don't open:
https://answers.microsoft.com/en-us...-file-is/381e9f29-50a5-4fa3-b9f2-7b3389293063

 

[USAMax]

Phil,

I cannot thank you enough for responding. If there was an Error that I could test for then I could add an, "On Error GoTo..." but for some reason it just hangs and I have to end task. I never used the Application.OnTime before so I had to try it.

I am using the Dir() function so the files do exist but network issues and/or open files are producing long delays in opening the files.

I am very open to any suggestions as user complaints are plaguing me!

 

[USAMax]

I stand corrected, the issue is during the Dir() command where it is getting the name of the next file to open.

I created a userform to display every step so I could see where it is hanging.

I believe the question should still be the same.

 

[pbornemeier]

One possible solution would be use Dir to get all of the filenames into an array then open them using the names stored in the array.
Stick a couple of DoEvents in the Dir loop so you will be able to use Ctrl+Break to stop then restart the loop.

 

[USAMax]

Phil,

It turns out the issue was when it tried to open the file and not the Dir. After that day the issue went away for me and the users!

Just wanted to let you know and to say thank you! Your help was very much appreciated.