Export to CSV file problems

[Ixtreme]

Hi all,

I have MS Excel 2002 SP3 installed.
I need to create a CSV file with ~ as the delimiter. I customized my
current (dutch) Regional settings List delimiter to ~. So far so good.
Once I open my excel file and do a save as, select CSV file and click
on Save, I have exactly what I need. Upon opening the file with
Notepad, all records are separeted with ~.

However, I would like to automate this. I record a macro of the above
action which resulted in something like this:

ActiveWorkbook.SaveAs Filename:= _
"C:\FILES\TEST.csv", _
FileFormat:=xlCSV, CreateBackup:=False

But If I open that file with Notepad, all fields are separeted by
comma's :-(

The questions is: WHY? and how can I prevent this from happening? I
want my ~ as the delimiter.

 

[Joel]

I don't know why in Denmark they would call a file with ~ seperating
characters when CSV means Comma Seprated Values. does ~ mean Comma in Demark?

Any way, below is code that you can use to manually generatte the CSV file
with ~


Sub WriteCSV()

Const Delimiter = "~"

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fswrite = CreateObject("Scripting.FileSystemObject")

WriteFileName = "text.csv"


'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

Exit Sub
End Sub

 

[SeanC UK]

Hi Ixtreme,

The problem is that when you automate the save process it is using the
language of VBA and not the regional settings.

Simply add the extra argument to your command and it should use the regional
settings:

ActiveWorkbook.SaveAs Filename:= _
"C:\FILES\TEST.csv", _
FileFormat:=xlCSV, CreateBackup:=False _
Local:=True


This should do the trick (in The Netherlands or Denmark ;-) ).

Hope this helps,

Sean.

 

[SeanC UK]

PS

I missed a comma in my argument list:

ActiveWorkbook.SaveAs Filename:= _
"C:\FILES\TEST.csv", _
FileFormat:=xlCSV, CreateBackup:=False, _
Local:=True

Sean.

 

[Joergen Bondesen]

Hi Sean.

I'm using Danish Excel 2003/2007.

For years I have used the hard (and slow) way to get semicolon in a csv
file, because Danish VBA gives comma.

Today, when I had the same probleme with a MS-DOS csv-file, I found your
answer and I'm very happy, THANKS.