Reginal setting and export txt

[TZ Saic]

Dear all,

I have issue with creating txt file from xlsm. The thig is, my excel set up is as follows:

"," as tousand separator,
"." as decimal separator,

but in Regional settings there is "." as tousand separator and "," as decimal separator.

Therefore numbers in Excel are visible as e.g 4,500.00 and in txt is 4500,00 instad of 4500.00.

Please Login or Register  to view this content.

Is there a workaround?

Thank you

Tanja

[AlphaFrog]

You could swap them in the Text like this...

Please Login or Register  to view this content.

[Marc L]

Hi !

Or just well set International property according to your need …

[TZ Saic]

Thank you alphaFrog, but I am not sure it will be applicable for my needs. There will be more users using this macro and some of them will have set reg.options as I do, some will have US settings. Therefore, I belive this will not work for me.

Hi marc L,
As I said before, more users will use this macro and we can not force all of them to have reg.settings this way (US). If they will have to change it each time while generating txt that whats the point of automatization.

Tanja

[AlphaFrog]

This will record the current Decimal Separator and Thousands Separator settings, temporarily set them to (US), and restore the original settings after the text file export.

Please Login or Register  to view this content.

[TZ Saic]

Hi alpha,

This code changes system separators of excel and not regional language settings. Txt is still generated with sepaarators defined in reginal settings (control panel).

Tanja

[AlphaFrog]

Hi alpha,

This code changes system separators of excel and not regional language settings. Txt is still generated with sepaarators defined in reginal settings (control panel).

Tanja

It worked for me if I had formatted the cells as currency.

Otherwise, use my original suggestion to swap the separators but test if the user's system separators are not US style.

Please Login or Register  to view this content.

[TZ Saic]

Thank you alphafrog