Convert XLS to TXT decimal sign problem 1

[tinoni]

Hi,
I'm using a macro to convert a *.XLS to *.TXT (Tab separator) file, but I've a (big) problem: the decimal sign allways changes from a comma (,) to a dot (.). I already try to change the regional settings but no good. This is driving me crazy!!!

Macro statement:
ActiveWorkbook.SaveAs Filename:="test.txt" _
, FileFormat:=xlText, CreateBackup:=False

Thanks for the help.
Rafael

 

[SkipVought]

Hi,

Short of finding a built-in work-around, consider

1. insert a new sheet

2. write a procedure to convert each numeric value to a string, replacing the separators.

3. save as with the new sheet active - will ONLY save that sheet

Skip,

_[red][/red]

 

[mintjulep]

In

Tools | Options | International

There is a check box that allows you to choose between using the Windows regional settings, or define the decimal separater for excel.

Perhaps you have that box unchecked?

 

[SkipVought]

I knew that an expert would post the REAL solution. ==> *

Skip,

_[red][/red]

 

[tinoni]

Hi mintjulep,
I already tried to change that options but the result still the same. The oddest thing is: If I manually save as an TXT (tab separator)- file | save as, works fine, but if I use a macro, fails, change from a comma to a dot.

Rafael

 

[HughLerwill]

Have you tried recording a macro when you SaveAs manually (when it 'works fine') and using the code produced in your macro?

 

[tinoni]

yes, it's the code that I used:

ActiveWorkbook.SaveAs Filename:="test.txt" _
, FileFormat:=xlText, CreateBackup:=False

 

[HughLerwill]

Have you exhausted all the arguments available to SaveAs; The 'Local' argument looks interesting.

 

[tinoni]

Hi HughLerwill,
Thanks for your tip, it work perfectly.

Rafael

 

[HughLerwill]

Good!