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