https://community.blueprism.com/t5/Product-Forum/Automating-Power-Query/m-p/66035#M18640 Wed, 25 May 2022 08:28:00 GMT devneetmohanty07 2022-05-25T08:28:00Z https://community.blueprism.com/t5/Product-Forum/Automating-Power-Query/m-p/66034#M18639 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'?&nbsp;<BR /><BR />Any suggestions welcomed.<BR /><BR />------------------------------<BR />John Hammond<BR />------------------------------<BR /> Wed, 25 May 2022 08:23:00 GMT https://community.blueprism.com/t5/Product-Forum/Automating-Power-Query/m-p/66034#M18639 john.hammond 2022-05-25T08:23:00Z https://community.blueprism.com/t5/Product-Forum/Automating-Power-Query/m-p/66035#M18640 Wed, 25 May 2022 08:28:00 GMT https://community.blueprism.com/t5/Product-Forum/Automating-Power-Query/m-p/66035#M18640 devneetmohanty07 2022-05-25T08:28:00Z https://community.blueprism.com/t5/Product-Forum/Automating-Power-Query/m-p/66036#M18641 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.<BR /><BR />------------------------------<BR />John Hammond<BR />------------------------------<BR /> Fri, 27 May 2022 11:01:00 GMT https://community.blueprism.com/t5/Product-Forum/Automating-Power-Query/m-p/66036#M18641 john.hammond 2022-05-27T11:01:00Z https://community.blueprism.com/t5/Product-Forum/Automating-Power-Query/m-p/66037#M18642 <P>Hi Devneet,<BR /><BR /><BR />I tried your code but it brings following errors.<BR /><BR />What does it mean by it is not diclared?<BR /><BR />Do you know how I can fix this?&nbsp;<BR /><BR />Thanks : )<BR /><BR /><span class="lia-inline-image-display-wrapper" image-alt="17530.png"><img src="https://community.blueprism.com/t5/image/serverpage/image-id/17683iDD5C401DC6FA3FC3/image-size/large?v=v2&amp;px=999" role="button" title="17530.png" alt="17530.png" /></span></P><BR /><BR />------------------------------<BR />Ndrtim Dauti<BR />------------------------------<BR /> Thu, 06 Apr 2023 07:02:00 GMT https://community.blueprism.com/t5/Product-Forum/Automating-Power-Query/m-p/66037#M18642 NdrtimDauti 2023-04-06T07:02:00Z https://community.blueprism.com/t5/Product-Forum/Automating-Power-Query/m-p/66038#M18643 <P>Hi John,<BR /><BR />it seems a nice method.<BR />I basically have 2 queries that will bring 2 separate tables and both are merging with 4 connections only files.<BR /><BR />In such a case do you think the ODC file can be a solution?<BR /><BR />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?<BR /><BR /><BR />Thanks : )</P><BR /><BR />------------------------------<BR />Ndrtim Dauti<BR />------------------------------<BR /> Thu, 06 Apr 2023 07:07:00 GMT https://community.blueprism.com/t5/Product-Forum/Automating-Power-Query/m-p/66038#M18643 NdrtimDauti 2023-04-06T07:07:00Z