Loop through all the Excel files in OneDrive

Question

I wrote VBA code to loop through all the Excel files (.xlsm) in OneDrive folder and perform certain actions. I used Set Wb = Application.Workbooks.Open(path) It works most of the times, for example, the loop works fine 5 times in a row, and then 6th time it would show an error

Run-time Error '1004' - Method 'Open' of object 'Workbooks' failed

on one of the random files. Then I run the macro 7th times and it works again with the same files (also with the file that the macro failed to open previously). I think the issue may be the fact that the files are in OneDrive.

Could you please let me know what could be an issue and how do I prevent this error from reoccuring?

Answer 1

If this is a timing problem for example with opening the file because the OneDrive does not answer quick enough (timout) then you could solve this by trying to open the files eg 3 times in a row waiting 2 seconds inbetween.

Option Explicit

Public Function MultiAttemptWorkbookOpen(ByVal Path As String, ByVal MaxAttempts As Long, Optional ByVal WaitBetweenAttempts As Long = 0) As Workbook
    Dim wb As Workbook
        
    Dim iAttempts As Long
    Do While iAttempts < MaxAttempts And wb Is Nothing
        iAttempts = iAttempts + 1
        On Error Resume Next
        Set wb = Application.Workbooks.Open(Path)
        On Error GoTo 0
        
        If wb Is Nothing And WaitBetweenAttempts > 0 Then
            Application.Wait DateAdd("s", WaitBetweenAttempts, Now)
        End If
    Loop
    
    If wb Is Nothing Then
        Err.Raise Err.Number
    Else
        Set MultiAttemptWorkbookOpen = wb
    End If
End Function

This can then be used like

Set wb = MultiAttemptWorkbookOpen(path, MaxAttempts:=3, WaitBetweenAttempts:=2)

instead of

Set Wb = Application.Workbooks.Open(path)

so it tries 3 times to open the file and waits 2 seconds between the attempts.

If it still cannot open the file it will throw an error.