Save .csv file. Decimals separated by commas

[belitre]

Hi I've made a macro that save an excel file in .csv format.

I've several columns that have numeric format, with two decimal
separated by commas.

I don't have any problem when using Excel SaveAs Dialog.

But when I execute that action in the macro, I get decimals separate
BY POINT and not BY COMMA.

I include the final part of my macro:

' varText has the name of the file

columns("A:M").Select
ActiveWorkbook.SaveAs Filename:= _
"\\Mtdisa\directory\" + varText + ".csv" _
, FileFormat:=xlCSVMSDOS, CreateBackup:=False

Thanks for the attention. Regards

 

[Jim Rech]

You should always include the Excel version you're using in a post.

VBA uses the English (American) settings when a macro is run. For the
SaveAs method you can have VBA use your local settings with the Local
parameter:

ActiveWorkbook.SaveAs Filename:= _
"c:\test", FileFormat:=xlCSVMSDOS, Local:=True

This is only available in Excel 2002 and later.

If you have an earlier version of Excel I think you have to use a macro.
Here's one I wrote sometime ago:

''Outputs the selection if more than one cell is selected, else entire sheet
Sub OutputActiveSheetAsCSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
If FName <> False Then
ListSep = Application.International(xlListSeparator)
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & CurrCell.Value & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub


--
Jim Rech
Excel MVP
| Hi I've made a macro that save an excel file in .csv format.
|
| I've several columns that have numeric format, with two decimals
| separated by commas.
|
| I don't have any problem when using Excel SaveAs Dialog.
|
| But when I execute that action in the macro, I get decimals separated
| BY POINT and not BY COMMA.
|
| I include the final part of my macro:
|
| ' varText has the name of the file
|
| columns("A:M").Select
| ActiveWorkbook.SaveAs Filename:= _
| "\\Mtdisa\directory\" + varText + ".csv" _
| , FileFormat:=xlCSVMSDOS, CreateBackup:=False
|
| Thanks for the attention. Regards.
|
|
| ---
| Message posted
|

 

[belitre]

Thanks a lot. I'm using Excel 2000, so I should use a macro.

I've a really low knowledge of VBA. How could I use your macro?

I've to use a fixed directory to save the *.csv file.

Also I have the name of the file in a string variable, varText.

The selection is fixed too (Columns("A:M").Select) , so I think wha
I need must be very simple.

Any help would be much appreciated. Thanks again