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
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