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
.
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!