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)

Try to override system settings in ToolsOptionsInternational by unchecking
"Use system separators" and entering dot as Decimal separator!

Regards,
Stefi

€žStefano Gatto€� ezt Ã*rta:

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)

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

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.

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:

<Debit356,22</Debit



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


"Phil Hibbs" wrote:

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.
.

Looks like the Format() function does not take care of Excel's decimal
separator, but always refers to system's one.
--
Stefano Gatto
Have no fear of perfection - you''ll never reach it (Salvador Dali)


"Stefano Gatto" wrote:

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:

<Debit356,22</Debit



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


"Phil Hibbs" wrote:

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.
.

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.

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.

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.

Looks like I can. Thank you very much I did not know this function, I guess
it's new.
--
Stefano Gatto
Have no fear of perfection - you''ll never reach it (Salvador Dali)


"Phil Hibbs" wrote:

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.
.