How can I specify the column deliniator in a macro created .csv file?

Question

I am using the macro given as the accepted answer to this question. Saving excel worksheet to CSV files with filename+worksheet name using VB

However, I have edited windows default line separator from a comma to a semicolon.

Thus if I save a single worksheet excel workbook as .csv the txt generated is correct, using semicolons as I need.

However, when I use the macro to auto-generate individual .csv files from a multi worksheet excel workbook, all files generate with only comma delineation.

I can only assume that the macro is not referencing the same system variables excel is, and would really appreciate if someone can point me to a solution. Or if you wouldn't mind, simply post an edited version of the macro solution from the issue linked above.

I appreciate your time.

edit 14_0412 #1: I stumbled onto this:

Application.PathSeparator

And attempted to add it here:

For Each WS In ThisWorkbook.Worksheets
     WS.SaveAs SaveToDirectory & WS.Name, xlCSV, "", "", False, False, False, Application.PathSeparator
Next

I also attempted to place a simple string ";".

The macro completes without error but output is the same.

RogueDeus · Answer 1 · 2014-04-13T03:50:10.883

I finely figured it out. I can't claim this is how its supposed to be done, I can only say that it works as I need it to.

Taking the example I linked to above, here is what works for me.

I extended the SaveAs to include reference to Application.PathSeparator in the Local parameter. http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas.aspx

Public Sub SaveWorksheetsAsCsv()

Dim WS As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

 CurrentWorkbook = ThisWorkbook.FullName
 CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook
 Application.DisplayAlerts = False
      SaveToDirectory = "C:\"

      For Each WS In ThisWorkbook.Worksheets
          WS.SaveAs SaveToDirectory & WS.Name, xlCSV, "", "", False, False, False, False, False, Application.PathSeparator
      Next

' Application.DisplayAlerts = False
  ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub

If someone can supply a more correct version, please do! (I have no clue about VB syntax)

How can I specify the column deliniator in a macro created .csv file?

1 Answers1