save all csv as xls

[HarryKlein]

Hello,

I would like to save all csv files as xls in a directory.
I have created the attached macro, but the problem is, that in case this macro opens and save the csv files, the separation will be lost, data are separated by semicolon will be merged in one cel.
I tried to use the opentext method, but I couldn't manage to use it on all files in a directory.

Could anybody help, how to deal with this problem?


Thanks

Sub CsvToXls()
Dim sfil As String
Dim sPath As String
Dim sPath2 As String
Dim owbk As Workbook
Dim fName As String
Dim fnewName As String
Dim fNameLength As Integer
Dim newExt As String
newExt = ".xls"

sPath = ThisWorkbook.Sheets("PARAM").Range("A1")
sPath2 = ThisWorkbook.Sheets("PARAM").Range("A2")

ChDir sPath
sfil = Dir("*.csv")

Do While sfil <> ""
      
    Set owbk = Workbooks.Open(sPath & "\" & sfil)
    fName = owbk.Name
    fNameLength = Len(fName) - 4
    fnewName = Left(fName, fNameLength)
    owbk.SaveAs Filename:=sPath2 & "\" & fnewName & newExt, FileFormat:=xlExcel8
       
    owbk.Close False
    
    sfil = Dir
Loop

End Sub