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

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


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 FalseEnd With

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


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


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.CloseEnd Sub

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

Hope it helps someone!


matomo