Open a semicolon seperated file in MS Excel, after I call Workbook.Save() via C# Addin the semicolons will be replaced with comma

Question

I've got a strange behavior in MS Excel 2013 when I develop a C# Addin. My customer open a CSV file with semicolon delimiters.

Now he can click one of my Addin buttons. One of the buttons will call "Application.ActiveWorkbook.Save()" to save changes to file.

After this call MS Excel will replace all semicolons with comma! If found no solution in Excel API to resolve this bug.

When I save the file via MS Excels blue disc, no replace will be made.

Why?

Can anyone help me?

Thanks in advance!

-- Markus

Answer 1

If your standard format for CSV-files is with semicolons, you can try

ActiveWorkbook.SaveAs Filename:="text.csv", FileFormat:=xlCSV, CreateBackup:=False, local:=True

local:=True will use whatever delimeter is used normally in your environment.

If that does not work, you can try to create the csv-file with vba directly

Adapt the following code to your needs (found at http://www.mrexcel.com/forum/excel-questions/134185-xlcsv-%3D-comma-save-csv-%3D-semicolon-delimited.html#post1724209)

Sub CreateCSV()

    Dim rCell As Range
    Dim rRow As Range
    Dim sOutput As String
    Dim sFname As String, lFnum As Long

    'Open a text file to write
    sFname = "C:\MyCsv.csv"
    lFnum = FreeFile

    Open sFname For Output As lFnum
    'Loop through the rows'
        For Each rRow In ActiveSheet.UsedRange.Rows
        'Loop through the cells in the rows'
        For Each rCell In rRow.Cells
            sOutput = sOutput & rCell.Value & ";"
        Next rCell
         'remove the last comma'
        sOutput = Left(sOutput, Len(sOutput) - 1)

        'write to the file and reinitialize the variables'
        Print #lFnum, sOutput
        sOutput = ""
     Next rRow

    'Close the file'
    Close lFnum

End Sub