How to save semi-colon delimited csv file using VB

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.

Answer 2

Use xlSCVMSDOS instread of xlCSV

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

It worked for me

Answer 3

To use vbs script following construction succeded:

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

where arguments are:

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/ru-ru/library/microsoft.office.tools.excel.workbook.saveas.aspx

Last "1" in "SaveAs" function is equal to Local=True

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

Answer 4

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

(don't use: Filename:= )

Answer 5

No need to declare all this variables, just add local:=true in the end of your SaveAs method, like so:

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

Answer 6

I ran into the same issue and after contemplating trying to change the "line separator" in Regional Settings using VBA code and Kernel calls I decided it would be way more of a pain, so instead I just found some examples of using the Scripting.FileSystemObject to accomplish my needs instead.

The following code will take an existing csv file and replace all the 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, e.g.
    '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

You can then just call the commaReplace sub routine from your sub routine which is creating the csv file.

Hope it helps someone!