VBA code to open an excel workbook, Refresh the query (PQ), once it has finished running, save and close the workbook

[hananak]

Dear All,

I have a workbook (Transactions) in a folder which is connected to another source file using power query. The original source file has 200 columns, so the power query is used to do some transformation on the "Transactions file" to get rid of most of the columns and for other transformation. Currently, I have to manually open it every time to refresh and save it.

This Transactions file is connected to another file via Power Pivot using a data model relation via unique ID in the Power Pivot. This is done to take the benefit of of the Power Pivot feature "Drill Through". As the Transactions are huge in volume. I have searched a lot and to take advantage of the "Drill Through", the data has to be connected via OLAP - that's the only option.

I am looking for a VBA code, which I'll use in the file, which has the Power Pivot in it, on running the code, it should in the background open the "Transactions file", refresh the query, once it has finished running, save and close the file.
Then I will refresh the Power Pivot (Pivot table) and able to complete my various analysis.

I hope this makes sense.

Thanks for your help!

 

[hananak]

I would really appreciate if anyone can help!

Thanks.

 

[hananak]

Any help please?