excel - How to save semi-colon delimited csv file using VBA?

Question

I copy data into a spreadsheet, use VBA to format it, then save that sheet into a CSV file.

I use the following code:

ws.SaveAs Filename:=filestr, Fileformat:=xlCSV

ws is the worksheet that I saved.

This gives me a comma-delimited CSV file.

I would like to save that sheet into a semicolon-delimited file.

I found the following:

1. Go to Start>Settings>Regional And Language Options
2. Click on the Customize button
3. Next to List Separator type in a semi-colon (;)

I followed the procedure above and changed my code to:

ws.SaveAs Filename:=filestr, Fileformat:=xlCSV, Local:=True

I still get a comma-delimited CSV file as output.

I am using Excel 2003 and my OS is Windows XP.

Answer 1

i've just checked this because had same problem. Filename has no functionality in this case.

This is what worked for me:

With ActiveWorkbook
    .SaveAs Filename:="My File.csv", FileFormat:=xlCSV, Local:=True
    .Close False
End With

In regional settings -> ; <- as list separator. It is also important not to save changes when closing -> with Close you have to use False.