How to check if power query is valid from VBA

jaryszek

Board Regular
Joined
Jul 1, 2016
Messages
185
Hello,

how to check if PQ is valid via VBA code?

Cross posted here: How to check if power query is valid
You can take example files from link.

I am creating PQ formula and query via VBA code:


VBA Code:
Sub Macro1()

    ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""D:\Pulpit\Newest Pull request\Importing PQ new way\ExampleTable.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Table1_Table = Source{[Item=""Table1"",Kind=""Table""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Table1_Table,{{""Col1"", Int64.Type}, {""Col2"", Int64.Type}, {""Col3"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

End Sub

and problem is when source data is not there...i am getting of course DataSourceError. But it is possible to track this and give error via VBA? How ?
Maybe some workaround ?

I can not just check if file is on disc because i will refer to web server (and there if there is not Table.csv how i could check?)

Please help,

Best,
Jacek
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,695
Members
416,127
Latest member
MALEPINZON

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top