VBA - How to modify Power Query data source with vba?

Question

I have a problem where I have to change the Power Query data source when a specific error occurs in my code. Basically my code needs to perform the following:

If no error, then refresh using "Default data source". If Error, then perform query to "Modified Data Source".

In total, I have 8 queries but 7 of them are joined to the "main query" and therefore the main query is dependent on the 7 queries. All 8 queries have the 1st step in Power Query:

Excel.Workbook(File.Contents("FILE PATH"), null, true)

How do I change the "FILE PATH" to "FILE PATH 2" by using VBA?

Thanks for the help!

kevin · Accepted Answer · 2023-04-16 17:26:23Z

4

Instead of VBA it's easier to do this within Power Query. Power Query has a function similar to IFERROR called try ... otherwise. In your example you would use:

try Excel.Workbook(File.Contents("FILE PATH"), null, true) otherwise Excel.Workbook(File.Contents("FILE PATH 2"), null, true)

This tells power query to try getting the file contents from "FILE PATH". If that results in an error, power query should use "FILE PATH 2".

Microsoft page on error handling with try...otherwise and some other error handling tips: https://learn.microsoft.com/en-us/power-query/error-handling

edited Apr 16, 2023 at 17:26

answered Apr 16, 2023 at 10:05

kevin

2,1981 gold badge6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA - How to modify Power Query data source with vba?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related