UTF-8 conversion in batch CSV to XLS process

Hello there,

I despair at integrating a conversion of UTF-8-coded files while converting them into XLS files.

    
csvFile = Dir(sourceDirectory & "*.csv")
    Do While csvFile <> ""
        Workbooks.OpenText fileName:=sourceDirectory & csvFile, Origin:=65001, DataType:=xlDelimited, Comma:=True
        Set wb = ActiveWorkbook
    
        Application.DisplayAlerts = False
        wb.SaveAs destDirectory & Replace(csvFile, ".csv", ""), xlWorkbook
        Application.DisplayAlerts = True
        
        wb.Close False
        
        csvFile = Dir
    Loop

Unfortunately it does not work altough I set the codepage to 65001 (which should be UTF-8). Using the GUI, it does work.
I read from a way using ADODB but I don't understand how to use it in the Do-While-Loop.

Can you please give me a hint?


Thank you very much!

Nico

OK I found a solution:

    Do While csvFile <> ""
        Set wb = Workbooks.Open(sourceDirectory & csvFile, Format:=xlCSV, Delimiter:=",", ReadOnly:=True)
        Set wb = ActiveWorkbook
        
        Const kBOM As String = "\xEF\xBB\xBF"
        
        'Konvertierung aus UTF-8 (siehe Sub "Ado_Qry_Csv")
        Call Ado_Qry_Csv(sourceDirectory, csvFile, kBOM)
          
        
        '... und als XLS abspeichern
        Application.DisplayAlerts = False
        wb.SaveAs destDirectory & Replace(csvFile, ".csv", ""), xlWorkbook
        Application.DisplayAlerts = True
        
        wb.Close False
        
        csvFile = Dir
    Loop

And the Sub "Ado_Qry_Csv" is modified from Stackoverflow (/questions/33820866/how-to-query-utf-8-encoded-csv-files-with-vba-in-excel-2010?rq=1).

Thanks,
Nico