How to import multiple csv files into querie connections in Excel?

Question

I would like to import data in Excel from CSV files (they have the same format) using VBA to Loop data importing and formating with Queries. My first goal would be to create the connections from the files in the selected folder. I have the following code:

Sub ImportQueries()

Dim myPath As String
Dim myFile As Variant
Dim fileType As String
Dim i As Integer

 With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Source Folder"
        .AllowMultiSelect = False
        .Show

      myPath = .SelectedItems(1) & "\"
 End With
    
  fileType = "*.csv*"

  myFile = Dir(myPath & fileType)

  Do While myFile <> ""
  
ActiveWorkbook.Queries.Add Name:= _
        "Data" & i, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(& myPath & myFile)),[Delimiter="";"", Columns=6, Encoding=1250, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{" & _
        """Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
   
    i = i + 1
    myFile = Dir
    
 Loop

MsgBox "Result Import Complete"
End Sub

After executing the macro, I have the following message at Queries in Excel:

Expression.Error: Token Literal expected.
Details:
    let
    Source = Csv.Document(File.Contents(& myPath & myFile)),[Delimiter=";", Columns=6, Encoding=1250, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}})
in
    #"Changed Type"

I believe the issue is with this part:

Source = Csv.Document(File.Contents(& myPath & myFile))

I've tried several variations, but none of them worked. Could someone help me with this issue?

Thank you!

Answer 1

Looking at this a second time, I believe I found the issue. myFile and myPath are inside the quotation marks, making them literal strings "myFile" and "myPath" and not variable values myFile and myPath.

Try this for your Queries.Add:

ActiveWorkbook.Queries.Add _
    Name:="Data" & i, _
    Formula:="let" & Chr(13) & Chr(10) & _
            "    Source = Csv.Document(File.Contents(""" & myPath & myFile & """),[Delimiter="";"", Columns=6, Encoding=1250, QuoteStyle=QuoteStyle.None])," & Chr(13) & Chr(10) & _
            "    #""Changed Type"" = Table.TransformColumnTypes(Source,{" & _
                                            "{""Column1"", type text}, " & _
                                            "{""Column2"", type text}, " & _
                                            "{""Column3"", type text}, " & _
                                            "{""Column4"", type text}, " & _
                                            "{""Column5"", type text}, " & _
                                            "{""Column6"", type text}" & _
                                    "})" & Chr(13) & Chr(10) & _
            "in" & Chr(13) & Chr(10) & _
            "    #""Changed Type"""

Side notes: Chr(13) & Chr(10) has a shortcut in VBA, vbNewLine!