Excel NumberFormat not working correctly

[stick3r]

Hi,

I have excel with couple of sheets with numbers. I do some stuff with excel before and then I have this NumberFormat:

49252138,87	49252138,87	0,00
-22099,24	-22099,24	0,00
	49230039,63
4,90	4,90	0,00
228390,07	0,00	228390,07
0,00	0,00	0,00
	4,90
360000000,00	360000000,00	0,00
	360000000,00
3349269,10	3349269,10	0,00

I change NumberFormat to Number, with 1000 separator and I get what I need - separated by dot and decimals separated by comma:

49.252.138,87	49.252.138,87	0,00
-22.099,24	-22.099,24	0,00
	49.230.039,63
4,90	4,90	0,00
228.390,07	0,00	228.390,07
0,00	0,00	0,00
	4,90
360.000.000,00	360.000.000,00	0,00
	360.000.000,00
3.349.269,10	3.349.269,10	0,00

Macro recorded is this:

    Range("F42:H51").Select
    Selection.NumberFormat = "#,##0.00"

I added this line to code:

$o_Excel.Worksheets(2).Range("F42:H51").NumberFormat = "#,##0.00"

What I get is this:

49252138,87000	49252138,87000	,000
-22099,24000	-22099,24000	,000
	49230039,63000
4,9000	4,9000	,000
228390,07000	,000	228390,07000
,000	,000	,000
	4,9000
360000000,000	360000000,000	,000
	360000000,000
3349269,1000	3349269,1000	,000

 

Any ideas how to fix this to show correctly?

Thanks

 

 

 

 

[water]

Which language setting do you use for your system (US-english, German ...)?

[stick3r]

There are several of those. Country and Region - Sweden, Windows display language - English (United States), and in Region > Formats > Format: English (Sweden)

 

[water]

What do you get when you try:

$o_Excel.Worksheets(2).Range("F42:H51").NumberFormat = "#.##0,00"

 

[water]

In Excel you can check the currently used separators by: File -> Options -> Advanced -> Use system seprator

[stick3r]

1 hour ago, water said:

What do you get when you try:

$o_Excel.Worksheets(2).Range("F42:H51").NumberFormat = "#.##0,00"

 

Whoo, this worked. Thanks!

[water]