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

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 

Power query error

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

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:

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
Set Ws = Wb.ActiveSheet

mFormula = "let " & _
    "Source = Csv.Document(File.Contents(""C:\test.txt""),[Delimiter="";"", Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & _
    "#""Step1"" = Table.SelectRows(Source, each Text.Contains([Column2], ""E7"") and [Column3] = ""F1"")" & _
    "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("A1"), XlListObjectHasHeaders:=xlYes).QueryTable
     .CommandType = xlCmdSql
     .AdjustColumnWidth = False
     .ListObject.Name = "test"
     .CommandText = "SELECT * FROM [" & "Test text" & "]"
     .Refresh BackgroundQuery:=False
 End With
      
End Sub

Thanks, Ruggero



Read more here: https://stackoverflow.com/questions/64943996/how-to-avoid-power-query-error-with-empty-query-from-vba-excel

Content Attribution

This content was originally published by Ruggero Bini at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: