Running Power Query in a Closed Workbooks

[hananak]

Hi All,

I have two workbooks. They both are directly linked with Oracle Accounting software through Power Query. Every time when we want to see latest data. I have to go to those workbooks one by one. Run the query (once the query has finished running), save the files and close. After that I have separate file which contains all the Pivot tables reports that have connections to those workbooks. All I do is "Refresh All" and all the pivot tables reports are refreshed with the new data in them.

What I am trying to do is to have a button in my Pivot tables reports workbook. When I press it. It should run the queries in those closed workbooks which are directly linked with Oracle Accounting software. and alert me once the queries have successfully fetched the new data and then refresh all the pivot reports.

Currently, I have to go several times in those workbooks to run the queries, save and close during the day. It has becomes really frustrating to open the files, run the queries, save and close and then again do the same process.
There are not set timings otherwise I would have chosen to run the queries automatically after specific time interval.

I really appreciate in advance for your help please!

 

[hananak]

Can anyone confirm whether it is possible please?

 

[RoryA]

Is there a reason you can't have the queries in the same workbook as the pivot tables?

 

[hananak]

Can anyone confirm whether it is possible please?

Thanks for your reply.

Yes, those two workbook contains huge data and very heavy in size. Previously we used have them in pivot table reports file but it made it really really slow. The files used to crash a lot, while we are analysing the data.

 

[RoryA]

Then you don't have a lot of options, I'm afraid. You can't refresh a PQ query without having the file open.

 

[hananak]

Then you don't have a lot of options, I'm afraid. You can't refresh a PQ query without having the file open.

Is it possible to automatically open the file in the background, hide it and once the PQ has finished running then close and save the file?

Is it possible by utilizing VBA, can we do that?

 

[RoryA]

If you have the queries set up in the data workbook to not refresh in the background, then you can use something like:

With GetObject("path to workbook here")
   .Refreshall
   .Close Savechanges:=true
end with

for each source workbook.

 

[hananak]

If you have the queries set up in the data workbook to not refresh in the background, then you can use something like:

With GetObject("path to workbook here")
   .Refreshall
   .Close Savechanges:=true
end with

for each source workbook.

Sorry, I am not fully familiar with VBA.

Should I post this code in VBA module in Pivot tables report file?

I tried to do that but it gives an error.

 

[RoryA]

As a full macro, it would be something like (do not include code tags when pasting to the VB editor):

Sub UpdateSourceDataFiles()
With GetObject("path to workbook here")
   .Refreshall
   .Close Savechanges:=true
end with
End Sub

 

[hananak]

I ran the code and it didn't run the PQ correctly and instead made the original file totally blank and all the data is lost. See the attached picture for your reference.