How to avoid Power Query error with empty query from VBA Excel?

Question

I would like to import a text file into Excel filtering just what I want through a VBA macro. The amount of data is large so I use efficently the Power queries. I have a list of several things to filter and process differently and this list could change. So for each "feature" to filter I reload the query in a new sheet. If the filter makes the query empty I get an error from the Power Query that I am not able to skip with:

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Debugging I see that the error comes out between the query creation and the paste to the sheet, see (*) in the code below.

Does somebody know if there is a way to have the number of records into the query in order to be able to use an if statement and skip the paste phase?

The only other idea that I have is to write automatically a row for each feature into the txt file to filter but it is not an elegant method

A thing that I do not understand is that the problem appear using a function, see below, but not using directly a macro. When I use the function the error shown does not appear always but in any case the code finish the function but the main macro stops.

test.txt

946737295   9CE78280    FF  1   5   FF  FF  FF  FF  FF
946737295   9CE78280    C0  FF  0   0   0   0   FF  FF
946737295   9CE68082    C0  4   0   FF  FF  FF  FF  FF

and the macro is:

Function readTxt(input_path As String, Pgn As String, B2 As String, B3 As String) As Boolean

    Dim Wb As Workbook
    Dim Ws As Worksheet
    Dim Conn As WorkbookConnection
    Dim mFormula As String
    Dim query As WorkbookQuery
    
    Set Wb = ActiveWorkbook
    Set Ws = Wb.ActiveSheet
    
    On Error Resume Next
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
       

    mFormula = "let " & _
        "Source = Csv.Document(File.Contents(""" & input_path & """),[Delimiter=""#(tab)"", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & _
        "#""Step1"" = Table.SelectRows(Source, each Text.Contains([Column2], """ & Pgn & """) and [Column5] = """ & B3 & """ and [Column4] = """ & B2 & """)," & _
        "#""Step2"" = Table.RemoveColumns(Step1,{""Column2"", ""Column3"", ""Column4"", ""Column5"", ""Column9"", ""Column10""})" & _
        "in #""Step2"""
    
    Set query = Wb.Queries.Add("test_7", mFormula)

    With Ws.ListObjects.Add(SourceType:=0, Source:= _
         "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & "test_7" & ";Extended Properties=""""", Destination:=Ws.Range("A3"), XlListObjectHasHeaders:=xlYes).QueryTable
         '.ListObject.TotalsRowRange
         .CommandType = xlCmdSql
         .AdjustColumnWidth = False
         .ListObject.Name = "test"
         .CommandText = "SELECT * FROM [" & "test_7" & "]" 
         .Refresh BackgroundQuery:=False
     End With
         
     If Err.Number <> 0 Then
            Err.Clear
     End If
      
      query.Delete
         
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    readTxt = True 'output
    
    On Error GoTo 0

End Function

Sub readTxt()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim Conn As WorkbookConnection
Dim mFormula As String
Dim query As WorkbookQuery

Set Wb = ActiveWorkbook

Dim i As Integer
Dim C3 As String

    On Error Resume Next
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

C3 = "F2"

For i = 1 To 2
       
    If i = 2 Then
        C3 = "FF"
        Sheets.Add After:=ActiveSheet
    End If
    Set Ws = Wb.ActiveSheet

    mFormula = "let " & _
        "Source = Csv.Document(File.Contents(""C:\test.txt""),[Delimiter=""#(tab)"", Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & _
        "#""Step1"" = Table.SelectRows(Source, each Text.Contains([Column2], ""E7"") and [Column3] = """ & C3 & """)" & _
        "in #""Step1"""
        
    Set query = Wb.Queries.Add("Test_text", mFormula)
    
    ' (*) THE ERROR OF POWER QUERY APPEARS HERE
    
    With Ws.ListObjects.Add(SourceType:=0, Source:= _
         "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & "Test_text" & ";Extended Properties=""""", Destination:=Ws.Range("A3"), XlListObjectHasHeaders:=xlYes).QueryTable
         .CommandType = xlCmdSql
         .AdjustColumnWidth = False
         .ListObject.Name = "test"
         .CommandText = "SELECT * FROM [" & "Test_text" & "]"
         .Refresh BackgroundQuery:=False
     End With
     
     query.Delete
 
Next

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    On Error GoTo 0
      
End Sub

Thanks, Ruggero

So, if I understand correctly, you're saying the error raises when there's no data in the txt file? — Ricardo Diaz
– Ricardo Diaz, Commented Nov 21, 2020 at 14:18
Code is working for me with the example provivded. Query does not return any data as the filter you add in the second step returns an empyt table. And that's what I get in the excel sheet as well. Code also works for an empty text file. Maybe behaviour is version dependant. — Storax
– Storax, Commented Nov 21, 2020 at 14:21
Thanks, have you tried a step with data after the one without data? For example a sheet with [Column3] = ""F1"" and a second one with [Column3] = ""FF"" — Ruggero Bini
– Ruggero Bini, Commented Nov 21, 2020 at 14:24
Why should I? You provided the M-Code, so I took that. And you are saying you get the error with the code above, aren't you? BTW, I got version2.86.725.0 32-bit of PQ. — Storax
– Storax, Commented Nov 21, 2020 at 14:25
I edited the code to be more clear but in this way it seems to work. Actually I am using this code inside a function, I try to load that — Ruggero Bini
– Ruggero Bini, Commented Nov 21, 2020 at 14:59

Ricardo Diaz · Accepted Answer · 2020-11-21 14:27:13Z

1

You can check if a step (table) has some specific columns using this code:

let Source = Csv.Document(File.Contents("C:\temp\test.txt"),[Delimiter=";", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    #"Step1" = Table.SelectRows(Source, each Text.Contains([Column2], "E7") and [Column3] = "F1"),
    result_error = "Some error",
    check_columns = Table.HasColumns(#"Step1", {"Column2", "Column3"}),
    result = if check_columns = true then #"Step1" else result_error
in result

See the check_columns step and the conditional result

answered Nov 21, 2020 at 14:27

Ricardo Diaz

5,7262 gold badges24 silver badges34 bronze badges

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

8 Comments

Ruggero Bini Over a year ago

Thanks Ricardo, it could be useful for another check that I could have to do. In the described case I have more or less always the same columns, but the rows are 0 in some cases

Ricardo Diaz Over a year ago

I’m not at my desk right now, but following the same logic, use Table.RowCount function

Ruggero Bini Over a year ago

I tried with Table.RowCount and it works to have at least a line but the code still stop. I saw that the problem appears also with other keys for the fileter. Reading another thread I understand that could be a bug related to the big dimension of the txt. link italic bold The error messagge into the details is "The MashupResource's PageReader is not available". Power Query 2.78.5740.481 64bit

Ricardo Diaz Over a year ago

Maybe provide a link to the txt sample file that has all the columns

Ruggero Bini Over a year ago

link to txt 30Mb file, the macro above gives the error: "The MashupResource's PageReader is not available" and the F2 is empty altough there are data

|

Collectives™ on Stack Overflow

How to avoid Power Query error with empty query from VBA Excel?

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related