Refresh power query, wait till finished then save file?

[Davealot]

Greetings,
Needing some perspective if possible. I have an excel spreadsheet that has powerqueries running in it from an Access Database. I'm trying to write a macro that will refresh all data, then once that is done to save the file.

So I started off with:

activeworkbook.refreshall
thisworkbook.save

That cancels the data refresh because it goes straight to saving the file. So I split it into two separate subs, and at the end of the refreshall sub I just called the save sub, still doesn't work. Is there a way to set it, where it doesn't lock the workbook where it's something like:

activeworkbook.refreshall

Wait ten seconds

thisworkbook.save

Not sure how to make this happen, any ideas are greatly appreciated!

 

[Chihiro]

Are all your query/connections set to .BackgroundQuery = False?

If not, setting it to False should resolve your issue (it forces code to wait for query to finish before doing other operations).

 

[Davealot]

Are all your query/connections set to .BackgroundQuery = False?

If not, setting it to False should resolve your issue (it forces code to wait for query to finish before doing other operations).

TAADAAAA!!!! This is why I come to Chandoo! Thank you Chihiro!

 

[Davealot]

OK let me ask a follow-up if possible. I have this replace an existing file doing a "Save As" with the notifications turned off so that it doesn't warn me that I'm replacing an existing file. The file that I'm using is "Workbook Group Leader" and the file that is being replaced is "PowerBI Slave". When I run the macro in Workbook Group Leader, it updates data, then saves, then replaces PowerBI slave via "Save as" and keeps the same name. But when the Save as happens, It closes Workbook Group Leader and I'm then sitting in PowerBI slave. Is there a way to have it...in essence just save the file, re-write PowerBI slave and stay in Workbook Group Leader?

 

[Chihiro]

Instead of using ".SaveAs" method use ".SaveCopyAs".

https://msdn.microsoft.com/en-us/library/office/ff835014.aspx

 

[Davealot]

Thanks for reply, I made change and am now getting error, I appreciate the help, any idea of what I can do? See attached

 

[Davealot]

I guess to elaborate, The only change I made was from the "Saveas" to "Savecopyas"

 

[Chihiro]

As shown in the link, only argument for ".SaveCopyAs" is file name (full path and file name). All other properties are taken from source workbook.

 

[Davealot]

As shown in the link, only argument for ".SaveCopyAs" is file name (full path and file name). All other properties are taken from source workbook.

I fail. Thank you friend.