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:

Go to Start>Settings>Regional And Language Options
Click on the Customize button
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.

Here is a way to do it manually; http://stackoverflow.com/a/7079703/246342 replace the delimiter with ";" and the range with ActiveSheet.UsedRange — Alex K., Nov 21 '12 at 15:44
Thanks a lot for your suggestion. But I have encountered a problem when I tried to run your code. I have some value such as `1.0000000` in my spreadsheet and when it is convert to string by `CStr(c.Value)`, it is automatically rounded to `1`. Could you suggest how to deal with it? Thanks :) — user1758952, Nov 22 '12 at 11:51

score 32 · Answer 1 · edited Aug 28 '13 at 16:08

32

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.

edited Aug 28 '13 at 16:08

Joe

6,767
1
16
29

answered Aug 28 '13 at 15:44

user2726096

321
3
3

2

works like a charm... but it is necessary to have ".Close False" set up ! thx – Mr.P Apr 22 '15 at 06:38
1

I'm using Excel 2007 and only after call ".Close False" my CSV was saved using ";" as list separator. Thanks! – cantoni Jul 29 '15 at 15:42

score 4 · Answer 2 · edited Jun 21 '17 at 17:47

4

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

edited Jun 21 '17 at 17:47

Kannan Suresh

4,573
3
34
59

answered Jun 21 '17 at 17:26

Leo

61
3

score 4 · Answer 3 · answered Apr 19 '19 at 17:19

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!

what happens to the commas in the data? – Syed Ali Sep 16 '20 at 18:11 — Syed Ali, Sep 16 '20 at 18:11

score 1 · Answer 4 · answered Mar 25 '15 at 21:03

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)

score -1 · Answer 5 · answered Aug 01 '18 at 09:28

-1

Use xlSCVMSDOS instread of xlCSV

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

It worked for me

answered Aug 01 '18 at 09:28

Vincent

1

In my opinion, this does not provide any value to the answers above. – Ivan Kaloyanov Aug 01 '18 at 09:55

score -2 · Answer 6 · answered Jul 02 '13 at 09:23

-2

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

(don't use: Filename:= )

answered Jul 02 '13 at 09:23

bik128

1

2

Use "Filename:=" or not won't change anything – Lionel T. Aug 14 '15 at 18:34

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

6 Answers6

Linked

Related