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!

 

[RoryA]

It's not lost it's just that the workbook is hidden. Click Unhide on the View tab, then select the workbook and save and close it. Then amend the update macro to:

Sub UpdateSourceDataFiles()
Application.screenupdating = false
With Workbooks.Open("path to workbook here")
   .Refreshall
   .Close Savechanges:=true
end with
Application.screenupdating = true
End Sub

 

[hananak]

It's not lost it's just that the workbook is hidden. Click Unhide on the View tab, then select the workbook and save and close it. Then amend the update macro to:

Sub UpdateSourceDataFiles()
Application.screenupdating = false
With Workbooks.Open("path to workbook here")
   .Refreshall
   .Close Savechanges:=true
end with
Application.screenupdating = true
End Sub

Normally, the PQ takes few minutes to fetch the new data but in this case the query run quickly and did not fetch any data. Pivot tables reports are also blank.

Any Thoughts?

 

[RoryA]

Did you turn off the background refresh for the queries as I mentioned earlier?

 

[hananak]

Did you turn off the background refresh for the queries as I mentioned earlier?

Yes I did turn off the background refresh. Please see the images. When I refresh the Pivots tables reports. I get these errors.

 

[RoryA]

That looks like a problem with your queries, not the code. Can you refresh them manually without error?

 

[hananak]

That looks like a problem with your queries, not the code. Can you refresh them manually without error?

Yes, I checked. The queries are working without error. It does take few minutes to update the queries as the data is large.

 

[RoryA]

If you turned off the background refresh then the queries should take the same time as normal.

 

[hananak]

If you turned off the background refresh then the queries should take the same time as normal.

Now no errors. but the query does not refresh the data when I run the macro to run the query.?