vba - Use comma instead of dot for decimals when saving as text

Question

This question looks similar to Visual basic handle decimal comma, but that one is about an entirely different problem.

I am writing a VBA macro to save my Excel file to a .txt file. This code line was generated by actually recording a macro where I saved the file to .txt:

ActiveWorkbook.SaveAs Filename, FileFormat:=xlText

Now, the issue is that the .txt file has all decimals formatted with dots, while I require them to be commas. For instance, it writes 32.7 while I am expecting 32,7.

Some interesting details:

• I have made sure that Windows and Excel are setup to use the correct local settings. Everywhere I look, decimals are displayed correctly with commas.
• When manually saving the workbook, it correctly writes commas to the file as well. However, when executing the VBA code I recorded when doing this, I get dots.

Is there some nifty argument or option that I forgot? I am truly at a loss here -- even my fourth cup of coffee is not bringing any inspiration.

Answer 1

This works for me (if your local separator is comma):

ActiveWorkbook.SaveAs Filename, FileFormat:=xlText, Local:=True

You can also specify any separator you want explicitly:

Application.DecimalSeparator = "," ' or any other separator, e.g. "-"
ActiveWorkbook.SaveAs Filename, FileFormat:=xlText, Local:=True

but it affects entire workbook, you can then change it back after saving txt file, if it differs from your local separator