Hi Guys,
this is cross posted from here:
I have DataSourceError:
error in power query - assume that source query was removed.
And my code is adding queries from VBA:
Sub Macro1() ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""D:PulpitNewest Pull requestImporting PQ new wayExampleTable.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
The issue is that VBA will add queiry but it will be invalid.
What i want to achevie is to check if PQ has error before loading into worksheet.
It is possible via VBA?
Maybe checking formula ?
Please help, i am loosing hope,
Best wishes
Jacek
I guess you want to rebuild the Power Query Intellisense in VBA, to test if the syntax is correct before running the query?
It's not realistic, this feature took a lot of time to be built by Microsoft team.
Just add the query in a different way:
let Result = try "your query here" otherwise null in Result
If the query result is null, then something was wrong, but you will not know why until you inspect the query.
If it's just the file existence you want to check, use:
Sub Macro1()
Dim TextString As String, FilePath As String, Pos As Long
TextString = "let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""D:PulpitNewest Pull requestImporting PQ new wayExampleTable.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"""
Pos = InStr(1, TextString, "File.Contents", vbTextCompare)
FilePath = Mid(TextString, Pos + 15, InStr(Pos + 15, TextString, ")", vbTextCompare) - Pos - 16)
If CreateObject("Scripting.FileSystemObject").FileExists(FilePath)=False then
MsgBox "File Does not exist!"
Exit Sub
End If
ActiveWorkbook.Queries.Add Name:="Table1", Formula:=TextString
End Sub
thank you Catalin.
2) solution is nice.
let Result = try "your query here" otherwise null in Result
This is interesting. I could do something like that in M language but still i could not pass it into VBA result yes?
Best,
Jacek
What do you mean? "i could not pass it into VBA result yes?"
Do you want to replicate the Power Query engine to run the query text in VBA as a "virtual query"? Microsoft worked on it for many years, i'm afraid it's not possible.
A workaround is possible, if you add the query to a new book just for testing, refresh the new book to load the query results to sheet. If the result is null, you will know that query failed, at this point you will know if there is an error.
Why test the entire query, if you only need the path to be checked?
Thank you Catalin,
you have right. I am just checking all possibilities.
I will check if file is available first.
Best,
Jacek
There's another answer in another thread here that may also help - https://www.myonlinetraininghub.com/excel-forum/power-query/how-do-i-know-if-there-was-a-problem-with-the-refresh-of-data
"RefreshAll works different in VBA than from user interface, in VBA will not raise error message when a query fails.
Loop through connections and refresh them one by one:
If Connection.Type = xlConnectionTypeOLEDB Then
Connection.OLEDBConnection.BackgroundQuery = False
On Error GoTo 1
Connection.Refresh
End If
2:
Next Connection
Exit Sub
1:
If Err.Number <> 0 Then
'write to a cell: ActiveWorkbook.Name & " failed to refresh!"
End If
Resume 2
