Object Variable Not Set Error when opening workbooks - BUT WORKS FINE WHEN I INSERT BREAKPOINTS

[nigelandrewfoster]

Hi,

Code below works fine if I put breakpoints in the first procedure, but stops with above error on highlighted line when I don't. Why please? And how can I overcome this? Puzzled! Thanks.

Sub Compile_Megalist()    Dim wsCustomers As Worksheet
    Dim wsHistoric As Worksheet
    Dim wsNewArrivals As Worksheet
    
    [B]Set wsCustomers = OpenWorkbook(strMailChimpPath(), "Megalist A - CUSTOMERS.xlsx").Worksheets(1)[/B]
    Set wsHistoric = OpenWorkbook(strMailChimpPath(), "Megalist B - HISTORY.xlsx").Worksheets(1)
    Set wsNewArrivals = OpenWorkbook(strMailChimpPath(), "Megalist C - NEW ARRIVALS.xlsx").Worksheets(1)
    
End Sub

Function OpenWorkbook(strPath As String, strFilename As String) As Workbook

    On Error Resume Next
    Set OpenWorkbook = Workbooks(strFilename)
    If Err.Number = 9 Then Set OpenWorkbook = Workbooks.Open(Filename:=strPath & strFilename)
    On Error GoTo 0
End Function

 

[nigelandrewfoster]

The following alteration works but it's dreadfully inelegant:

Sub Compile_MegalistA()    Dim wsCustomers As Worksheet
    Dim wsHistoric As Worksheet
    Dim wsNewArrivals As Worksheet
    
    On Error Resume Next
    Set wsCustomers = OpenWorkbook(strMailChimpPath(), "Megalist A - CUSTOMERS.xlsx").Worksheets(1)
    Do While Err.Number <> 0
        Err.Clear
        Set wsCustomers = OpenWorkbook(strMailChimpPath(), "Megalist A - CUSTOMERS.xlsx").Worksheets(1)
    Loop
    Set wsHistoric = OpenWorkbook(strMailChimpPath(), "Megalist B - HISTORY.xlsx").Worksheets(1)
    Do While Err.Number <> 0
        Err.Clear
        Set wsHistoric = OpenWorkbook(strMailChimpPath(), "Megalist B - HISTORY.xlsx").Worksheets(1)
    Loop
    Set wsNewArrivals = OpenWorkbook(strMailChimpPath(), "Megalist C - NEW ARRIVALS.xlsx").Worksheets(1)
    Do While Err.Number <> 0
        Err.Clear
        Set wsNewArrivals = OpenWorkbook(strMailChimpPath(), "Megalist C - NEW ARRIVALS.xlsx").Worksheets(1)
    Loop
    
End Sub

 

[xenou]

I'm not a fan of this particular style of error handling as it is hiding what's going wrong. Are you really working a lot with workbooks that are sometimes open, sometimes not open? These workbooks appear to have problems opening, as the root cause of the trouble.

 

[nigelandrewfoster]

I'm not a fan at all. Makes my skin crawl to type in the code, but it is the only way I can get the thing to work. The only thing I can think is the workbooks are stored on OneDrive and the delay in opening causes the issue. No nearer a proper solution though. I've only seen this before when using VBA code to rename PDF files I CREATED using an external app (in the days of Excel 2003). Cheers for your interest in my post.

 

[xenou]

I don't have any experience with using files that are stored in One Drive, I'm afraid.

 

[nigelandrewfoster]

I'm guessing. I've written macros to access files on network drives before now and not had this issue.