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!

What is the intent behind `& "" &` in the formula? In VBA `""` is an empty string, which makes `& "" &` a pointless concatenation (adds nothing). — Toddleson, Jun 02 '21 at 15:37
Hmm, I haven't noticed this, that part of the code was created by the excel macro recorder. — Myro, Jun 03 '21 at 16:51

Toddleson · Accepted Answer · 2021-06-03T17:36:52.770

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!

Yes, this was the problem. Additionally I had to add Quotation Marks in the string before and after myPath and myFile, and remove a parenthesis. So the final format which works is this: File.Contents(""" & myPath & myFile & """) Thank you for your help! — Myro, Jun 03 '21 at 17:30
Oh yes, since `myPath & myFile ` is to be inserted as a string into the function inside the formula, it needs to be enclosed in quotes. I'll correct my answer to have that. — Toddleson, Jun 03 '21 at 17:36

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

1 Answers1