Sql string with OLEDB gives error 1004 in VBA

I would like to import a text file into excel filtering just what I want through a VBA macro. When I use LIKE operator in the sql string I get the error 1004. I have tried both * and % as wildcard and ALike instead of Like but there is no difference.

test_7.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 import_txt()

Dim input_path As String
input_path = "C:test_7.txt"

Dim strSql As Variant
strSql = "SELECT * FROM [test_7]" & _
   " WHERE Column2 Like '*E7*' AND" & _
   " Column4='FF'"
   
    ActiveWorkbook.Queries.Add Name:="test_7", Formula:= _
    "let" & "    Origine = Csv.Document(File.Contents(""" & input_path & """),[Delimiter=""#(tab)"", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None])," & "    #""Modifica tipo"" = Table.TransformColumnTypes(Origine,{{""Column1"", type text}, {""Column2"", type text}, {""Colum" & _
    "n3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}})" & "in" & "    #""Modifica tipo"""
    
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=test_7;Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array(strSql) 'Array("SELECT * FROM [test_7]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "test_7"
    .Refresh BackgroundQuery:=False
End With
End Sub

Answer

SQL statements do not work well with the Power Query provider. But you could totally achieve what you want via the formula associated with your query.

See this example:

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:UsersLoïcDesktoptesttest.txt""),[Delimiter="";"", Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & _
    "#""Step1"" = Table.SelectRows(Source, each Text.Contains([Column2], ""E7"") and [Column3] = ""FF"")" & _
    "in #""Step1"""
    
Set query = Wb.Queries.Add("Test text", mFormula)

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

You might want to add some error handling mechanisms (in case the file is already open etc) and to delete the connection to the text file if it’s not further needed.

You’ll find the documentation for Microsoft’s Power Query M formula language here.

A last tip to help you build you M formulas with VBA: use the macro recorder!