3

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.

Community
  • 1
  • 1
Lee White
  • 3,649
  • 8
  • 37
  • 62
  • 1
    It's already there, @Bathsheba. This is really just a "save-as" with `xlText` as the file format. `Filename` is a string ending in `.txt`, but that should be of no importance. – Lee White Apr 02 '14 at 07:50
  • 2
    Four cups of coffee is _never_ the threshold for inspiration. It will require at least seven, or one glass of Drambuie. – paxdiablo Apr 02 '14 at 07:53
  • I would +10 that comment if I could, @paxdidablo. – Lee White Apr 02 '14 at 07:58

1 Answers1

9

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

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80