Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...