0

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!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
pilotflying777
  • 3
  • 1
  • 2
  • Just to clarify: By "shows up", I hope you're talking about the generated file, as viewed in a text editor, and not what it looks like when you then open that file in Excel again? – mbj Jan 15 '19 at 12:23
  • @mbj Yes, of course. I am talking about the results in the generated file, opened with a text editor. – pilotflying777 Jan 15 '19 at 12:33
  • What version of Excel are you using? Setting `Application.DecimalSeparator = "."` and `Application.UseSystemSeparators = False` works for me, and I'm running Excel with Swedish regional settings. I have Office 365 with Excel version 1812. – mbj Jan 15 '19 at 13:09
  • @mbj Okay, I see. In my case, Microsoft Excel for Office 365 MSO (16.0.11126.20192) 32 bit is in use. – pilotflying777 Jan 16 '19 at 07:44

1 Answers1

1

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)

mbj
  • 987
  • 5
  • 18
  • Thank you very much for your quick response and the valuable feedback. I will try your solutions today and will give feedback, if that works for me. – pilotflying777 Jan 16 '19 at 07:46
  • 1
    I have now integrated your second solution approach, were the system decimal separator is replaced by the dot manually at it works perfectly fine on the Swedish system as well as on systems with other language settings. Thank you very much, your help is highly appreciated. – pilotflying777 Jan 16 '19 at 14:30
  • @mbj hello, is it any way not just to read separator `myShell.RegRead("HKCU\Control Panel\International\sDecimal")`, but write ? – Mik Jul 21 '22 at 08:03
  • 1
    Hi @Mik, yes, the WScript.Shell object has a RegWrite method that you could probably use: https://learn.microsoft.com/en-us/previous-versions//yfdfhz1b(v=vs.85) – mbj Jul 24 '22 at 11:56