Hi everyone,
I'm getting a strange error when trying to refresh one of my Power Query connections using some VBA (apologies if this should be in the VBA forum - I wasn't sure where to post it!).
The line "ActiveWorkbook.Connections("MyQueryName").Refresh" gets "Run-time error 1004: Initialization of the data source failed"
To try to troubleshoot, I stripped out all of the other code so my sub consists of just that line - same error. Then I switched on the Macro Recorder and refreshed the query using the "Queries and Connections" pane. That didn't throw any errors, and seems to have refreshed the data successfully. But when I checked the output from the Macro Recorder it had recorded precisely the same line as I had used in my code! Then, when I tried to run my code a second time - no error. But, if I close the file and re-launch it, then try to run my code again - I get the same run-time error. In other words, this line seems to work fine, but only if I have used the 'Refresh Connection' button in Excel to do the same thing first.
There are other connections in the workbook, and all of them refresh fine using the same line of VBA - it is just this one that fails in this way. The strange thing is that this is the only one that does does not have any external connections. The previous queries (which refresh fine using this code) do, and this one (which fails) is a merge of all of the previous ones.
I'm baffled by this. Any tips on what might be causing it and how I can resolve / work around? I need to be able to do this refresh programmatically because (when functional!) it will be part of a more elaborate macro pulling data in from many workbooks
Thanks in advance (and if this should be in the other forum, obviously please feel free to move it!)
Ryan
If works from excel, it should work from vba as well.
Try with background query set to false, otherwise vba will not wait until the query finishes. You can change that setting from query settings.
Thanks Catalin! I knew it should - but could not see why it wasn't!
That seems to have sorted it, thanks for your help
Sorry Catalin - I thought your answer had resolved the problem, but I've just returned to the file and tried to run the code again and found that it has in fact persisted. All of the queries have background refresh set to false, but it doesn't help.
Still the same (maddening!) issue - when I refresh the query using the refresh button in the Queries and Connections pane in Excel, it works fine, but when I try to do it via VBA (even using the precise syntax generated by the macro recorder) I get that Initialization of data source error. This all only happens for the first instance that I try to refresh after the file opens. If I open the file and try to refresh via VBA - error. If I open and refresh via Excel - no error. If I open, refresh via Excel, and then again via VBA - no error.
This is driving me crazy! Any thoughts?
Have you tried.
ActiveWorkbook.RefreshAll
Thanks Alan! I feel very silly but RefreshAll seems to work! Obviously the simplest solution, which I should have thought of ages ago. Will monitor the file for next few days, but I'm sure it will be fine
(I used ThisWorkBook instead of ActiveWorkbook because there's some other parts of my code that open source workbooks in the background so I can unlock and load from password-protected sheets)
I still have no idea why my original version had that strange bug, but don't really care to be honest. Thanks both for your help
