CODEWITHSUNDEEP
vbaexcelcsv
unusualhabit
unusualhabit

Reputation: 101

How do you export to CSV from Excel with a custom separator using VBA?

I would like to export a number of excel files to .csv using a macro that specifies the column separator.

I've read that the way to get excel to export with a custom separator eg. "|" instead of "," is to change the separator in the Regional Settings in your control panel.

I did this and it worked fine when I export a single file, however when I try to use a macro to export using the following snippet, the file still saves with the default comma separators.

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

Can anyone tell me why this is happening? Is there a method to save a file through VBA that will use the separator I have specified?

Upvotes: 0

Views: 5179

Answers (1)

Abkarino
Abkarino

Reputation: 1446

Code marked as solution for your question in another stackoverflow question.

 ActiveWorkbook.SaveAs Filename:="C:\Temp\Fredi.csv", FileFormat:=xlCSV, CreateBackup:=False, local:=True

ref: Save as CSV with semicolon separator

Upvotes: 1

Related Questions