Split function in Excel VBA returning odd characters-CodePudding

I have a text file containing danish characters

"Næsby IF afdeling * Badminton * Sport *"

When splitting and placing them in an array the danish characters gets "messed up"

This is the complete Text string in a *.TXT file to be split up in Excel columns:

"Ulrich*wiingreen*BenPauWin05 Aps*Søballehøjen 12*5270*Odense N* 4530212215*[email protected]*Næsby IF afdeling*Badminton*Sport* *Hal 1*Hal 2*99*11/03/2022 13:00*11/03/2022 17:00*kkkk"

The code doing this is:

    If InStr(FileName, "forespoerg_") <> 0 Then
        OrderArr = Split(OrderDetails, "*")
        OrderRow = OrdersDB.Range("A99999").End(xlUp).Row   1 
                      
        OrdersDB.Cells(OrderRow, 1).Value = Application.WorksheetFunction.Max(Range("A4:A9999"))   1
         OrdersDB.Cells(OrderRow, 2).Value = Date
         For OrderCol = 3 To 20
            OrdersDB.Cells(OrderRow, OrderCol).Value = OrderArr(OrderCol - 3)
         Next OrderCol
    End If

The splitting works just fine. Unfortiunately the characters gets messed up. Example: "Søballehøjen 12" imports as: "SÃ¸ballehÃ¸jen 12"

Can anyone give a hint to solve this character issue.

CodePudding user response：

Not sure but I suspect encoding mismatch. You can try opening your text file with VS Code and watch at the bottom right what is its encoding.
You can then use StrConv(yourTextVar, someconversion) wher someconversion is a value like vbUnicode or vbFromUnicode (see options here)

How do you import the text file ? If your file is a unix or another non Windows flavour you could try reading the file using an ADODB.Stream, which offers fine control on the encoding. I quickly found a sample here.