How to print numbers invariably using dots as decimal symbol?

[Stefano Gatto]

Hello,

I need to print to a disk file, numbers that are stored in different cells
of a worksheet, by making use of the dot as decimal symbol. I thought of
loading them in variants first and printing from there a formatted string to
the file, using the FORMAT function.

However, format "0.00" will use the decimal symbol as defined in the locale
(Regional Settings), which is the opposite of what I need.

Does someone know if FORMAT can be instructed to invariably use a dot as
decimal symbol?

Thank you.

Stefano Gatto
Have no fear of perfection - you''ll never reach it (Salvador Dali)

 

[Stefi]

Try to override system settings in Tools>Options>International by unchecking
"Use system separators" and entering dot as Decimal separator!

Regards,
Stefi

„Stefano Gatto†ezt írta:

 

[Phil Hibbs]

Dim OldSeparator As String
OldSeparator = Application.DecimalSeparator
Application.DecimalSeparator = "."
...
Application.DecimalSeparator = OldSeparator

Phil Hibbs.

 

[Phil Hibbs]

Hm, might need some work... how about this...

Dim OldSysDefault As Boolean
Dim OldSeparator As String
OldSysSep = Application.UseSystemSeparators
OldSeparator = Application.DecimalSeparator
Application.UseSystemSeparators = False
Application.DecimalSeparator = "."
...
Application.DecimalSeparator = OldSeparator
Application.UseSystemSeparators = OldSysSep

Phil Hibbs.

 

[Stefano Gatto]

Thanks Phil and thanks Stefi to suggest me to use this setting that I even
forgot about its existence...

However, I tried it this way and the numbers keep on being printed with
commas, while the system is set up with the Portuguese locale.



This is the code. Let me know if this is what you meant:


Sub test1()
Dim DebitVal As Double

Application.UseSystemSeparators = False
With Application
.DecimalSeparator = "."
.ThousandsSeparator = ""
End With

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("D:\DDT\Test1.txt", True)

DebitVal = Worksheets("JV_CC_Reclass").Cells(8, 10).Value
a.WriteLine (" <Debit>" & Format(DebitVal, "0.00") & "</Debit>")
a.Close

Application.UseSystemSeparators = True
End Sub

And this is the content of D:\DDT\Test1.txt:

<Debit>356,22</Debit>

 

[Stefano Gatto]

Looks like the Format() function does not take care of Excel's decimal
separator, but always refers to system's one.

 

[Phil Hibbs]

However, I tried it this way and the numbers keep on being printed with

commas, while the system is set up with the Portuguese locale.

Hm, it looks like the VBA Format function always uses your Windows
locale settings, and not the Excel Application settings. Could you
just write out the Cell.Text value?

Phil Hibbs.

 

[Phil Hibbs]

Hm, it looks like the VBA Format function always uses your Windows

locale settings, and not the Excel Application settings. Could you
just write out the Cell.Text value?

It just occurred to me that I should warn you that the Cell.Text value
is not always reliable. If the column is too narrow, then a cell value
of 1.999 will write out as "2". I see that you want a specific format
"0.00" so that might not be any good.

Cell.FormulaLocal might work, but that will write out the formula if
your cell is calculated.

Phil Hibbs.

 

[Phil Hibbs]

1. Store the Excel Format setting
2. Set the Excel Format settings to what you want
3. Store the Cell Format and ColumnWidth
4, Set the Cell Number Format to "0.00"
5. AutoFit the Column
6. Write out the Text value of the Cell
7. Set the Excel Format, Cell Format, and Column Width back to how
they were

Sub test1()
Dim DebitVal As Double
Dim OldCellFormat As String
Dim OldWidth As Variant

Application.UseSystemSeparators = False
With Application
.DecimalSeparator = "."
.ThousandsSeparator = ""
End With

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("D:\DDT\Test1.txt", True)

OldCellFormat = Worksheets("JV_CC_Reclass").Cells(8,
10).NumberFormat
Worksheets("JV_CC_Reclass").Cells(8, 10).NumberFormat = "0.00"
OldWidth = Worksheets("JV_CC_Reclass").Cells(8, 10).ColumnWidth
Worksheets("JV_CC_Reclass").Cells(8, 10).EntireColumn.AutoFit

DebitVal = Worksheets("JV_CC_Reclass").Cells(8, 10).Text
a.WriteLine (" <Debit>" & Format(DebitVal, "0.00") & "</Debit>")
a.Close

Application.UseSystemSeparators = True
Worksheets("JV_CC_Reclass").Cells(8, 10).ColumnWidth = OldWidth
Worksheets("JV_CC_Reclass").Cells(8, 10).NumberFormat =
OldCellFormat
End Sub


You don't have to restore everything after if you don't want to, but I
like to be careful not to mess with things.

Phil Hibbs.

 

[Stefano Gatto]

Looks like I can. Thank you very much I did not know this function, I guess
it's new.