CODEWITHSUNDEEP
excelvbaexport-to-csvdecimal-point
pilotflying777
pilotflying777

Reputation: 3

How to ensure dot as decimal separator when exporting excel to csv?

I want to export some data via vba from excel to a csv-file and set the decimal separator to a dot (independent from local language or system settings).

By now I already have the data in the excel file with a dot as decimal separator. It works on some systems very well, but on other ones (e.g. Swedish system settings), the decimal separator shows up as a comma after exporting to csv via excel vba.

This is what my relevant code snippet looks at the moment:

' Set Export Parameters
Application.DecimalSeparator = "."
Application.ThousandsSeparator = ""
Application.UseSystemSeparators = False
ActiveSheet.Range("D4:G1048571").NumberFormat = "0.00"

' Open (new) output file
Open fileSaveName For Output As #1

' Write date upfront
Print #1, Trim(Worksheets("Output - Upload generator").Range("A2:A2"))

' Set data range for output
Set myrng = Worksheets("Output - Upload generator").Range("A3:G" & lastExportRow)

' Write data
For i = 1 To myrng.Rows.count
    For j = 1 To myrng.Columns.count
        lineText = IIf(j = 1, "", lineText & ";") & myrng.Cells(i, j).Text
    Next j
    Print #1, lineText
Next i

' Close output file
Close #1

Until now, the export is successful with dot as decimal separator on some systems, but not on every system independent of local settings (e.g. swedish language settings - decimal separator appears as a comma). As I have tried already several things, like changing the separaor in excel advanced settings, but nothing worked for every system.

I would be glad, if someone could give me a hint what to do to ensure the dot!

Upvotes: 0

Views: 4092

Answers (1)

mbj
mbj

Reputation: 1015

I'm using the latest official version of Excel (Office 365, Excel version 1812) with Swedish regional settings, and this works for me: 

Dim myCell As Range    
Set myCell = ActiveSheet.Range("A1")
myCell.Value = CDbl(9999.999)

Application.DecimalSeparator = "."
Application.UseSystemSeparators = False

myCell.NumberFormat = "0.00"
Debug.Print "Cell Value: " & myCell.Value   '9999,999
Debug.Print "Cell Text: " & myCell.Text     '10000.00

I would expect the above to work for any version of Excel that supports this code, but in case you need a workaround, you can always do this:

First, get the decimal separator from Windows regional settings:

Dim myShell, regDecSep    
Set myShell = CreateObject("WScript.Shell")
regDecSep = myShell.RegRead("HKCU\Control Panel\International\sDecimal")

Debug.Print "System decimal separator: " & regDecSep

Then, while processing the worksheet, check what type of value is in the current cell, and, if it's numerical, use the Replace function to fix the separator:

Dim cellText    
cellText = myCell.Text

If regDecSep <> "." Then
    If TypeName(myCell.Value) = "Double" Then
        Debug.Print "Replacing the decimal separator"
        cellText = Replace(cellText, regDecSep, ".")
    End If
End If

Debug.Print "Result: " & cellText

(Note that you need to use the cell's Value instead of Text when checking the type)

Upvotes: 1

Related Questions