VBA query table looping-CodePudding

Bonjour!

Hi all! I am new here.

I am doing a Macro that import data in .out text file to an excel sheet. I may have 10 output files that are organised in the same way so I want to make a loop of this. I used PowerQuery What I have done know is below. The txt files names are Compr1, Compr2...

But I still have some redondant problems about it. THe line keeps have some issue and can't make it work. Without it I have no problems but nothing shows up in excel. .Refresh BackgroundQuery:=False

If someone could help me I would be very grateful. Thanks a lot, Matthieu.

Sub BONJOUR()


Dim chemin As String
Dim querie As String
Dim i As Integer

For i = 1 To 3

chemin = "C:\Users\matth\Desktop\OUTPUT FILES\Compr" & i
querie = "quera" & i
ActiveWorkbook.Queries.Add name:=querie, Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""chemin""),[Delimiter="","", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Kept Range of Rows"" = Table.Range(#""Changed Type"",2109" & _
    ",20)," & Chr(13) & "" & Chr(10) & "    #""Split Column by Delimiter"" = Table.SplitColumn(#""Kept Range of Rows"", ""Column1"", Splitter.SplitTextByDelimiter("" * "", QuoteStyle.Csv), {""Column1.1"", ""Column1.2"", ""Column1.3"", ""Column1.4"", ""Column1.5"", ""Column1.6""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type1"" = Table.TransformColumnTypes(#""Split Column by Delimiter"",{{""Column1.1"", type text}, {""Col" & _
    "umn1.2"", type text}, {""Column1.3"", type text}, {""Column1.4"", type text}, {""Column1.5"", type text}, {""Column1.6"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Changed Type1"",{""Column1.1""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Removed Columns"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=querie;Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [queries]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = querie
    .Refresh BackgroundQuery:=False
End With
Range("C27").Select
Next

End Sub

CodePudding user response：

Move the variables outside the quotes here Source = Csv.Document(File.Contents(""" & chemin & """) and Location=" & querie & ";Extended Properties and Array("SELECT * FROM [" & querie & "]")

Option Explicit
Sub BONJOUR()

    Dim chemin As String, querie As String
    Dim q As Object, i As Integer
    Dim PQ As String, f As String
    
    Const DOSSIER = "C:\Users\matth\Desktop\OUTPUT FILES\"
    
    ' power query
    PQ = "    #'Changed Type' = Table.TransformColumnTypes(Source,{{'Column1', type text}, " & vbCrLf & _
    "{'Column2', type text}, {'Column3', type text}})," & vbCrLf & _
    "    #'Kept Range of Rows' = Table.Range(#'Changed Type',2109,20)," & vbCrLf & _
    "    #'Split Column by Delimiter' = Table.SplitColumn(#'Kept Range of Rows', 'Column1', " & vbCrLf & _
    "Splitter.SplitTextByDelimiter(' * ', QuoteStyle.Csv), {'Column1.1', 'Column1.2', 'Column1.3'," & vbCrLf & _
    "'Column1.4', 'Column1.5', 'Column1.6'})," & vbCrLf & _
    "    #'Changed Type1' = Table.TransformColumnTypes(#'Split Column by Delimiter',{{'Column1.1', type text}," & vbCrLf & _
    " {'Column1.2', type text}, {'Column1.3', type text}, {'Column1.4', type text}, " & vbCrLf & _
    "{'Column1.5', type text}, {'Column1.6', type text}})," & vbCrLf & _
    "    #'Removed Columns' = Table.RemoveColumns(#'Changed Type1',{'Column1.1'})" & vbCrLf & _
    "in " & vbCrLf & _
    "    #'Removed Columns'"
    PQ = Replace(PQ, "'", """")
    
    For i = 1 To 3
    
        chemin = DOSSIER & "Compr" & i
        querie = "quera" & i
        
        ' check if existing
        For Each q In ActiveWorkbook.Queries
            If q.Name = querie Then
                q.Delete
                MsgBox querie & " recreated"
            End If
        Next
       
        '  query formula
        f = "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & chemin & """)," & _
            "[Delimiter="","", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None])," & vbCrLf & PQ
                  
        ActiveWorkbook.Queries.Add Name:=querie, Formula:=f
        
        ActiveWorkbook.Worksheets.Add
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & querie & ";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [" & querie & "]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = querie
            .Refresh BackgroundQuery:=False
        End With
        
        Range("C27").Select
    Next
       
End Sub