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:
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!
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:
VBA 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!