Export to CSV via macro does not use local regional settings
 

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

Export to CSV via macro does not use local regional settings
 

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" wrote:

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

Export to CSV via macro does not use local regional settings
 

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


On 1 Feb., 17:15, Joel wrote:
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" wrote:
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- Zitierten Text ausblenden -

- Zitierten Text anzeigen -