Export excel to csv with a different separator

Question

I want the macro to automatically save a specific workbook sheet in csv format. The macro works but uses " , " as separator. The program that reads the csv file requires the separator to be " ; ". I can't get the macro to replace the separator with the correct one.

Macro:

Sub ExportToCSV_v2()

Dim ws As Worksheet

Set ws = Worksheets("export")

Dim fileName As String

fileName = Left(ws.Parent.Name, 5)

Dim currentDir As String

currentDir = ThisWorkbook.Path

ws.SaveAs fileName:=currentDir & "" & fileName & ".csv", FileFormat:=xlCSV

End Sub

Answer 1

Hi,

Try this code-

Sub ExportToCSV_v2()     Dim ws As Worksheet     Set ws = Worksheets("export")      ' Set list separator to semicolon     Application.DecimalSeparator = ","     Application.ThousandsSeparator = " "     Application.UseSystemSeparators = False     Application.ListSeparator = ";"      ' Save the worksheet as CSV     Dim fileName As String     fileName = Left(ws.Parent.Name, 5)     Dim currentDir As String     currentDir = ThisWorkbook.Path     ws.SaveAs fileName:=currentDir & "\" & fileName & ".csv", FileFormat:=xlCSV      ' Reset list separator to default (comma)     Application.UseSystemSeparators = True End Sub