In case you missed this important functionality in Excel 2016, today’s short blog post is written to raise some attention to a wonderful feature. You can write VBA code or PowerShell scripts to automate the creation of queries, change their M expression and copy queries from one workbook to another.
Here are few common scenarios you can achieve with automation:
- Manage queries, copy, delete or search for specific query name, description or M formula.
- Toggle queries’ data sources from staging to production environments.
- Automatically manipulate the M expression on massive number of queries.
Download my sample workbook here to learn how to manage queries, load them to the worksheet or Data Model. The sample was downloaded 3,976 times so far (11/22/2016).
Download my sample PowerShell scripts here to learn how you can copy and paste queries from one workbook the another.
If you are interested in Power Pivot automation, there are many new related interfaces in Excel 2016 that can be found here (anything that starts with Model.*).
Are you familiar with this functionality? Share the way you used it in the comments below.
I’ve been using this code for a while now, it’s been really great!
Is there anyway to get the status of the query to give a visual representation of the query refresh using the QueryTable.Refreshing method?
Sadly the links seem no longer to be active. Is there any chance this could get reposted/updated?
I am sorry. Microsoft retired Technet gallery and at this stage I cannot have access to these files. I will look for backups and update soon https://docs.microsoft.com/en-us/teamblog/technet-gallery-retirement
any update?
Sorry. I don’t have access to this code.
I found this page via mer_curius’ answer in StackOverflow (https://stackoverflow.com/questions/51386600/how-to-automate-a-power-query-in-vba/51641109). I found the mentioned pages by using the Wayback Machine:
https://web.archive.org/web/20200316183058/https://gallery.technet.microsoft.com/office/VBA-to-automate-Power-956a52d1
Thank you Web Archive and thank you Gil for the excellent content!