CODEWITHSUNDEEP
excelvbapowerquery
Carlsberg789
Carlsberg789

Reputation: 155

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

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!

Upvotes: 0

Views: 777

Answers (1)

kevin
kevin

Reputation: 2157

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

Upvotes: 4

Related Questions