Merge several csv files into one workbook

[Justbob]

I have some code that merges several csv files into one worksheet. It works but I found a flaw that needs to be corrected. The csv files I was merging were all comma delimited. Then I ran across a csv that was semi-colon delimited. I recorded a macro to see how that would look and tried to add it to this macro but it does not seem to like it. I found other macros that work but they won't let me select the files like this one, they want to bring in all the files from a single folder. Can someone see what I am doing wrong?

Sub Test_MergeCSVFiles()
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook
Dim i As String

fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.csv),*.csv", Title:="Choose Excel files to merge", MultiSelect:=True)

If (vbBoolean <> VarType(fnameList)) Then

If (UBound(fnameList) > 0) Then
countFiles = 0
countSheets = 0

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wbkCurBook = ActiveWorkbook

i = 1

For Each fnameCurFile In fnameList
countFiles = countFiles + 1

'right after the next step you can see the commented out parts I tried

Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile) '_

', Origin:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
'xlDoubleQuote , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
'Comma:=True

For Each wksCurSheet In wbkSrcBook.Sheets
countSheets = countSheets + 1
wksCurSheet.Copy After:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)

i = i + 1
Next

wbkSrcBook.Close SaveChanges:=False

Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

On Error Resume Next


MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
End If

Else
MsgBox "No files selected", Title:="Merge Excel files"
End If
End Sub

 

[Sergius]

You have a separator ”;”, and in your macro you indicate “Tab” in addition to him. The CONSECTIVEDELIMITER parameter is in your FALSE parameter. May only indicate the separator ";" In True.

 

[Justbob]

This is how it records when opening one csv, somehow I need to open multiple csv's and apply the delimiter. When I cut and paste past the file name and put it after "Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)", its red and won't even run at all.

Sub Macro2()
'
' Macro2 Macro
'

'
Workbooks.OpenText Filename:= _
"C:\test.csv" _
, Origin:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1), Array(18, 1)), TrailingMinusNumbers:=True
End Sub

 

[Justbob]

I discovered that csv in different regions use different delimiters.

The files I am getting are UTF-8, from Germany, that is what the Origin:=65001.

Is there a way to detect, in vba, what kind of csv I have and convert it?

 

[alansidman]

Here is a tutorial on how to achieve this with Power Query. No coding required. All achieved in the UI

 

[Justbob]

Here is a tutorial on how to achieve this with Power Query. No coding required. All achieved in the UI

thanks but I need it in vba. this needs to be an automated thing, not manual

 

[alansidman]

Once it is set up the Mcode is automated. Any additions or deletions to the folder will automatically update the output.