How to save CSV file separated by semicolon using VBA?

Question

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

I am using the following code:

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

ws is the sheet that I saved.

This gives me a comma delimited CSV file.

I would like to save this sheet to a file separated by a semicolon.

I found the following:

1. Choose Start> Settings> Language and Regional Options.
2. Click on the Configure button
3. Enter a semicolon (;) next to the list separator

I followed the above procedure and changed my code to:

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

I still get the comma delimited CSV file as output.

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

Answer 1

I just checked this because I had the same problem. In this case, the file name does not have functionality.

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 a list separator. It is also important not to save changes when closing -> with Close, you should use False .

Answer 2

You do not need to declare all of these variables, just add local: = true at the end of your SaveAs method, for example:

 ActiveWorkbook.SaveAs Filename:="C:/Path/TryMe.csv", FileFormat:=xlCSV, Local:=True 

Answer 3

Just use this code: ActiveWorkbook.SaveAs "My File.csv", xlCSV, Local: = True

(do not use: Filename: =)

Answer 4

To use the vbs script, the following succeded construct:

.SaveAs Filename, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1

where are the arguments:

 Object Filename, Object FileFormat, Object Password, Object WriteResPassword, Object ReadOnlyRecommended, Object CreateBackup, XlSaveAsAccessMode AccessMode, Object ConflictResolution, Object AddToMru, Object TextCodepage, Object TextVisualLayout, Object Local 

SourceLink: https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas.aspx

Last "1" in "SaveAs" is Local = True

In addition, the semicolon must be defined as a list separator in the regional OS settings (see answers above)

Answer 5

Use xlSCVMSDOS instread xlCSV

 ActiveWorkbook.SaveAs Filename:="my File.csv", FileFormat:= xlCSVMSDOS, Local:=True 

It worked for me

Answer 6

I ran into the same problem and, thinking of trying to change the “line separator” in regional settings using VBA code and kernel calls, decided that it would be much more complicated, so instead I found some examples of using Scripting.FileSystemObject. instead, to meet my needs.

The following code will take an existing CSV file and replace all commas with the tilde character "~".

 Private Sub commaReplace() Dim objFSO Dim filePath Dim migratorFileName Dim strFullPath1 Dim strFullPath2 Const ForReading = 1 'define a TextStream object Dim objTS Dim strContents As String 'note, my code actually uses the below commented out filepath 'as the location of the workbook can be arbitrary, eg 'Worksheets("FilePath").[A2:A2].Value is determined when workbook 'is opened 'filePath = Worksheets("FilePath").[A2:A2].Value filePath = "C:\Temp\" 'our original file that we've exported as csv file in another section of code migratorFileName = "MigratorInput.csv" strFullPath1 = filePath + migratorFileName 'the path and file name we want to save to, tilde separated vs. comma migratorFileName = "MigratorInput.tilde.csv" strFullPath2 = filePath + migratorFileName 'read everything from the csv file, replacing comma with tilde Set objFSO = CreateObject("Scripting.FileSystemObject") Set objTS = objFSO.OpenTextFile(strFullPath1, ForReading) strContents = objTS.ReadAll strContents = Replace(strContents, ",", "~") objTS.Close 'write everything out to another file, note, this could just overwrite 'the original file if you pass the optional overwrite flag Set objTS = objFSO.CreateTextFile(strFullPath2) objTS.Write strContents objTS.Close End Sub 

Then you can simply call the commaReplace routine from the routine that creates the csv file.

Hope this helps someone!