Log In
Register
Home
February 1, 2019
Offline
Hi Guys,
this is cross posted from here:
https://www.excelforum.com/exc.....valid.html
https://www.mrexcel.com/board/.....st-5643754
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:\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
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
See Answer
November 8, 2013
Offline
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.
Answers Post
November 8, 2013
Offline
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:\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"""
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
February 1, 2019
Offline
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
November 8, 2013
Offline
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?
February 1, 2019
Offline
Thank you Catalin,
you have right. I am just checking all possibilities.
I will check if file is available first.
Best,
Jacek
Active Member
April 10, 2022
Offline
There's another answer in another thread here that may also help - https://www.myonlinetraininghu.....-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
1 Guest(s)
