CODEWITHSUNDEEP
vbaexcelexcel-2010
Felix Schneider
Felix Schneider

Reputation: 13

how to set the decimal separator for printing to file different from the system setting

Using VBA in Excel 2010 I want to print the contents of a cell in a worksheet which is a decimal number to a file. I am using the ´Print #´ statement for this. I am working in an environment where the comma ´,´ is the system wide decimal separator. For the printout I want the point ´.´ to be the decimal separator.

The following code does NOT work:

Public Sub printDecimal()
    Application.UseSystemSeparators = False
    Application.DecimalSeparator = "."
    Open "testfile.txt" For Output As #1
    Print #1, ActiveSheet.Range("A1").Value
    Close #1
    Application.UseSystemSeparators = True
End Sub

The documentation for the Print # statement says:

All data written to the file using Print # is internationally aware; that is, the data is properly formatted using the appropriate decimal separator.

When I change the system wide settings in Windows 7 Enterprise Control Panel, I get the desired output, but this is not what I want.

Upvotes: 1

Views: 2525

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Since you are using a macro, you can take direct control of the material being output to the text file:

Public Sub printDecimal()
    Dim st As String
    Close #1
    Open "C:\TestFolder\testfile.txt" For Output As #1
    st = Range("A1").Text
    st = Replace(st, ",", ".")
    Print #1, st
    Close #1
End Sub

Using the .Text property allows you to get the cell's value WYSIWYG. The rest is just string manipulation.

EDIT#1:

The reason the posted code does not work is that applying Application.DecimalSeparator is akin to applying formats......the underlying value does not change. Run this to see the difference:

Sub demo()
    Dim st As String, st2 As String
    Application.UseSystemSeparators = False
    Application.DecimalSeparator = "|"
    Close #1
    Open "C:\TestFolder\testfile2.txt" For Output As #1
    Print #1, ActiveSheet.Range("A1").Value & vbCrLf & ActiveSheet.Range("A1").Text
    Close #1
    Application.UseSystemSeparators = True
End Sub

Upvotes: 3

Related Questions