Automating Power Query

[john.hammond]

Morning all. We're investigating the possibility of picking up a project which requires us to merge a large number of spreadsheets into a single 'master' sheet, with all the sheets stored on a Sharepoint site. Having not automated anything via Sharepoint, we're investigating the use of the Sharepoint VBO from the Digital Exchange, but in the mean time, I've shown the business area requesting the automation how to essentially run the merge via Power Query from within the desktop version of Excel. This has got me wondering, is it possible to, rather than grab the sheets from Sharepoint on a one-by-one basis before merging, would it be preferable to essentially automate the desktop version of Excel and getting it to run the Power Query commands? Or is there some sort of VBO that can run this 'headlessly'? 

Any suggestions welcomed.

------------------------------
John Hammond
------------------------------

[john.hammond]

Just replying to myself, in case anybody else is in need - the method I've found works best for our needs is to saved the Query itself as an ODC file. This can be run using the MS Excel VBO to grab the latest set of data from the query specified.

------------------------------
John Hammond
------------------------------

[NdrtimDauti]

Hi Devneet,


I tried your code but it brings following errors.

What does it mean by it is not diclared?

Do you know how I can fix this? 

Thanks : )

------------------------------
Ndrtim Dauti
------------------------------

[NdrtimDauti]

Hi John,

it seems a nice method.
I basically have 2 queries that will bring 2 separate tables and both are merging with 4 connections only files.

In such a case do you think the ODC file can be a solution?

Because I saw that it is possible to save only separate queries as ODC in my case there are 2 real and 4 connections . So it seems hard, or am I missing a part?


Thanks : )

------------------------------
Ndrtim Dauti
------------------------------