Combining many .CSV files with a ";" delimiter into a single .XLS file with header row

Originally Posted by jindon

Th problem is the character used in the file name "ø".
You need to delete/change it.

Try change to

Sub test()
    Dim myDir As String, fn As String, LastC As Range
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then myDir = .SelectedItems(1) & "\"
    End With
    If myDir = "" Then Exit Sub
    Application.DisplayAlerts = False
    Cells.Delete
    Application.DisplayAlerts = True
    fn = Dir(myDir & "*.csv")
    Do While fn <> ""
        If IsEmpty([a1]) Then
            Set LastC = [a1]
        Else
            Set LastC = Cells.Find("*", , , , 2, 2)(, 2).EntireColumn.Range("a1")
        End If
        LastC.Value = fn
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & myDir & fn, Destination:=LastC(2))
            .TextFileSemicolonDelimiter = True
            .TextFileDecimalSeparator = "."
            .TextFileThousandsSeparator = ","
            .Refresh BackgroundQuery:=False
        End With
        fn = Dir
    Loop
End Sub

Hi Jindon

Your code worked beautifully and fast Thank you very much to everybody that has helped me solve this problem. I hope I one day can contribute to this forum in such a helpfull manner as you did!

Have a great weekend, Emil