Export to CSV via macro does not use local regional settings

[Stefan]

Hi,

I try to export my data from Excel 2003 to a CSV file. I use "German/
Deutsch" regional settings (so a semicolon ";" is my list separator)
but changed the decimal point and group character to be "." for
decimal point and "," for group as in English/US regional settings. I
need to do this, to import the data in an ORACLE database. When doing
a Save-As from the menu this works, but not in a macro.

I found the postings about using "Local:=true" as additional parameter
for SaveAs method. But still no luck. Excel ignores this and still
exports using a comma "," as the field delimiter.

Any idea what goes wrong?

TIA,
Stefan

 

[Joel]

If you don't get a better solution, the macro below wil create your file.
Just change the delimiter to what ever you need

Sub WriteCSV()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")

'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutPutLine = Cells(RowCount, ColCount)
Else
OutPutLine = OutPutLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutPutLine
Next RowCount

tswrite.Close

End Sub

 

[Stefan]

Joel,

thanks for providing your solution. I already found it as answer to
another post regarding the same problem. I did not use it, as I still
thought I made something wrong. But now on a second thought I
integrated it, as it gives me more control of what gets exported.

Thanks again,
Stefan