VBA: Range values not saved in CSV file as Encoding issue possible.

[jbesclapez]

Hello Family!

I am using a script to save a Range into a XML file in my Documents folder.
I just realised now that when I save it with german characters like ö then Excel does not save it corrrectly.
I do not know why it does that. I presume Encoding, but I do not fully understand it.
I would like it to be saved exactly the way it displays in Excel.

Here is the guilty script below. How would you change it to make it work?

Sub btn_ExportADDXML()

    Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer
    Dim LastRow As Long
    Dim LastCol As Long
    Dim ws As Worksheet
    Dim dt As String
       
    Set ws = ActiveSheet
'    LastCol = WS.Cells(2, Columns.Count).End(xlToLeft).Column
'    LastRow = WS.Cells(Rows.Count, LastCol).End(xlUp).row
    LastRowBA = ws.Cells(Rows.Count, "BA").End(xlUp).row


    myFile = Application.DefaultFilePath & "\ADD_" & ActiveSheet.name & " " & Range("B2") & Format(CStr(Now), " yyy_mm_dd_hh_mm") & ".xml"
'    Set rng = WS.Range(Cells(3, LastCol), Cells(LastRow, LastCol))
    Set rng = ws.Range("BA3:BA" & LastRowBA)

    Open myFile For Output As #1

    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count
            cellValue = rng.Cells(i, j).Value
       
            If j = rng.Columns.Count Then
                Print #1, cellValue
            Else
                Print #1, cellValue,
            End If
        Next j
    Next i

    Close #1

MsgBox ("File exported successfully. Check in your Documents folder")

End Sub

 

[rlv01]

I ran your code and it appears to save the ö character correctly for me.

Book11
	A	B	C
1	Tööl	unmanning	flunkies
2	agendums	adhara	fricti
3	tampang	plasm	absciss
4	cadaverin	deplant	portgrave
Sheet1

 

[jbesclapez]

Hi rlv!
Sorry for the late reply and thanks for your efforts.
Do you think the issue could come then from the windows regional settings of each users? On my side I am using English US settings.
Anyone has an idea why that is happening?

 

[jbesclapez]

I would like to add something that could make some sense :
There is a formula in the cell and this formula retrieves a value with that contains this special character....
It displays correctly in Excel in notepad++ it becomes :

Then I did a bit of research and I changed a setting in Notepad++ and I did select Encoding->Character Set->Western European->Windows-1252? And now in Notepad it displays ö

I am now puzzled ! I do not know where my issue is anymore because then when I load this into another system, then it also displays

 

[rlv01]

You should check the encoding on your PC.

Sub ListRegionalSettings1()
    Dim Msg As String, SepCh As String, SepChDesc As String, SepType As String, SepMsg As String
    Dim ACP As String
    Dim OEMCP As String
    Dim CopySuccess As Boolean
    
    ACP = CreateObject("WScript.Shell").RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage\ACP")
    OEMCP = CreateObject("WScript.Shell").RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage\OEMCP")
    
    Msg = "ANSI Code Page setting on this PC is " & ACP & vbCr
    Msg = Msg & "OEM Console App Code Page setting on this PC is " & OEMCP & vbCr & vbCr
    SepType = xlDecimalSeparator
    GoSub SepDef
    SepType = xlListSeparator
    GoSub SepDef
    SepType = xlThousandsSeparator
    GoSub SepDef
    
    Select Case MsgBox(Msg & vbCr & vbCr & "Copy to windows clipboard?", vbQuestion + vbYesNo, "Windows Regional Settings")
        Case vbYes
            CopySuccess = CreateObject("htmlfile").ParentWindow.ClipboardData.SetData("Text", Msg)
    End Select
    Exit Sub
    
SepDef:
    SepCh = CStr(Application.International(SepType))
    Select Case SepCh
        Case "."
            SepChDesc = "period"
        Case ","
            SepChDesc = "comma"
        Case ";"
            SepChDesc = "semi-colon"
        Case Else
            SepChDesc = "character"
    End Select
    
    Select Case SepType
        Case xlDecimalSeparator
            SepMsg = "decimal separator:" & Chr(9)
        Case xlListSeparator
            SepMsg = "list separator:" & Chr(9) & Chr(9)
        Case xlThousandsSeparator
            SepMsg = "thousands separator:" & Chr(9)
        Case Else
    End Select
    Msg = Msg & "The as-found " & SepMsg & "The " & SepChDesc & " [" & SepCh & "] (Ascii " & Asc(SepCh) & ")" & vbCr
    Return
End Sub

 

[jbesclapez]

You should check the encoding on your PC.

Sub ListRegionalSettings1()
    Dim Msg As String, SepCh As String, SepChDesc As String, SepType As String, SepMsg As String
    Dim ACP As String
    Dim OEMCP As String
    Dim CopySuccess As Boolean
   
    ACP = CreateObject("WScript.Shell").RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage\ACP")
    OEMCP = CreateObject("WScript.Shell").RegRead("HKLM\SYSTEM\CurrentControlSet\Control\Nls\CodePage\OEMCP")
   
    Msg = "ANSI Code Page setting on this PC is " & ACP & vbCr
    Msg = Msg & "OEM Console App Code Page setting on this PC is " & OEMCP & vbCr & vbCr
    SepType = xlDecimalSeparator
    GoSub SepDef
    SepType = xlListSeparator
    GoSub SepDef
    SepType = xlThousandsSeparator
    GoSub SepDef
   
    Select Case MsgBox(Msg & vbCr & vbCr & "Copy to windows clipboard?", vbQuestion + vbYesNo, "Windows Regional Settings")
        Case vbYes
            CopySuccess = CreateObject("htmlfile").ParentWindow.ClipboardData.SetData("Text", Msg)
    End Select
    Exit Sub
   
SepDef:
    SepCh = CStr(Application.International(SepType))
    Select Case SepCh
        Case "."
            SepChDesc = "period"
        Case ","
            SepChDesc = "comma"
        Case ";"
            SepChDesc = "semi-colon"
        Case Else
            SepChDesc = "character"
    End Select
   
    Select Case SepType
        Case xlDecimalSeparator
            SepMsg = "decimal separator:" & Chr(9)
        Case xlListSeparator
            SepMsg = "list separator:" & Chr(9) & Chr(9)
        Case xlThousandsSeparator
            SepMsg = "thousands separator:" & Chr(9)
        Case Else
    End Select
    Msg = Msg & "The as-found " & SepMsg & "The " & SepChDesc & " [" & SepCh & "] (Ascii " & Asc(SepCh) & ")" & vbCr
    Return
End Sub

So I have more issue on this issue.
In my macro, Excel saves it and Notepadd++ reads it correcly in Windows-1252 encoding so what I do is to convert it back to UFT-8.
Now I did run your query and it confirms it.
I am trying to find a way to change teh ANSI code page by default to UTF-8. If you know a way to do it, in a simple manner from windows GUI pelase inform.
Thanks for your help rlv01


ANSI Code Page setting on this PC is 1252
OEM Console App Code Page setting on this PC is 437

The as-found decimal separator: The period [.] (Ascii 46)
The as-found list separator: The comma [,] (Ascii 44)
The as-found thousands separator: The comma [,] (Ascii 44)

 

[rlv01]

I think you want it in Windows 1252. My encoding is the same as yours and as I posted earlier, am displaying the the ö character correctly for me. It sounds like your problem is that "other system". I believe you can change the regional settings in the windows control panel to change the ACP, or you can just edit the registry entry directly. Google around until you find the code for UTF-8 and then experiment.

 

[jbesclapez]

I tried to change my registry to UTF8 but it is still the same.

It would be easier if the VBA forces the file to be saved in UTF8.
Have no clue on what to change in the macro above, but according to the research it is possible.
I found this macro below that seems to be doing the job but how can i integrate it in the macro above? IT is getting too complex for me now

 

[jbesclapez]

I think you want it in Windows 1252. My encoding is the same as yours and as I posted earlier, am displaying the the ö character correctly for me. It sounds like your problem is that "other system". I believe you can change the regional settings in the windows control panel to change the ACP, or you can just edit the registry entry directly. Google around until you find the code for UTF-8 and then experiment.

@rlv01 I was thinking... it might be easier to rethink the macro itself. My ultimate goal it so save a range that is in a single column as a value in UTF8- XML. (no indentation or XML format necessary as it is done in the XL). Have you got some magic for me ? I can not find a way to do it now... I will try again in the week edn.