Macro errors when connections update - but only sometimes

[fouraces]

My macro opens up a couple of files, then updates Connections in one of those files. These connections are all pulls from website html tables. The macro worked just fine for a while. Now, when I run the macro, I get this error:

Run-time error '1004':
Application-defined or object-defined error

When debugging, the error points to the first connection update line of the macro (Advanced2). Here's the code:

    Workbooks.Open (ThisWorkbook.Path & "\TheFormulaFinal V5.xlsm")
    Workbooks.Open (ThisWorkbook.Path & "\WebScraper.xlsx")
    Windows("WebScraper.xlsx").Activate
    ActiveWorkbook.Connections("Advanced2").Refresh
    ActiveWorkbook.Connections("DVP").Refresh
    ActiveWorkbook.Connections("PrSolu").Refresh
    ActiveWorkbook.Connections("Misc").Refresh
    ActiveWorkbook.Connections("NF Project").Refresh
    ActiveWorkbook.Connections("OppTot").Refresh
    ActiveWorkbook.Connections("PlrTot2").Refresh
    ActiveWorkbook.Connections("TeamTot").Refresh
    ActiveWorkbook.Connections("RotoGuru").Refresh
    Sheets("PlrTot2").Select

However, the very odd thing is that if I close the two files my code opened (WebScraper and TheFormula V5) without saving, then run the exact same macro, the next time it works! I suspect it has to be some kind of time-related error. I have turned off all background connection refresh as well as any kind of refresh upon file open, so none of these connections should ever update unless they are manually called to do so. I tried using Application Wait to add some time after the WebScraper file opened, and/or after the first refresh, but no avail. I also tried adding some dummy activities after the WebScraper file is activated to see if the file needed some time to load the connections, but also no luck.

Any help is appreciated, thanks!

 

[Yongle]

Try inserting

Application.Wait Now + TimeValue("00:00:05")

immediately above the line

Windows("WebScraper.xlsx").Activate

 

[fouraces]

As mentioned in the OP, I had already tried the Application.Wait.

I found the solution for anyone else who is looking, it's to call the WebScraper file specifically in the updates instead of using ActiveWorkbook:

Dim wb As Workbook
Set wb = Workbooks.Open(ThisWorkbook.Path & "\WebScraper.xlsx")
wb.Connetions("Advanced2").Refresh
...

 

[Yongle]

found the solution for anyone else who is looking, it's to call the WebScraper file specifically in the updates instead of using ActiveWorkbook:

Thanks for posting the solution

Using variables rather than relying on ActiveWorkbook is good practice where multiple files are involved
Your original code is correct - but VBA is impatient and gets on with the next task too early sometimes !