xlCSV = comma, Save As CSV = semicolon delimited?

[Felix Atagong]

I have daily Excel files to convert in 'European' CSV format, this is delimited with a semicolon.

If I open the file and do a 'manual' Save As CSV my file is allright.

When I write a macro and do a
ActiveWorkbook.SaveAs Filename:=Firma, FileFormat:=xlCSV, CreateBackup:=True
the delimiter is a comma. I tried with CSVWindows but the macro does NOT follow my 'default' windows separator which is semicolon (Excel 2000).

Anyone has a VBA solution or isn't there any?

PS: I can't change all commas in a semicolon afterwards because some of my 'fields' have commas in them, that's why I use semicolon as a separator!

 

[henkrek]

I guess::
ActiveWorkbook.SaveAs Filename:= "driveletter:\folder\subfolder\sub-subfolder\" + Replace(ActiveWorkbook.Name, ".xls", ".csv"), FileFormat:= xlCSV, CreateBackup:=False

 

[ssqrensen]

Hello
I'm sorry to open this otherwise closed thread :/
I need to make a file such as the thread author.
But I need to save it as DOS.
I hope someone can help me out

 

[CVDK]

Hi, I found a very simple solution to the problem that at least works for me. If your regional delimiter setting in the control panel is ; (semicolon). Just add Local:=True to the saveas statement in our VBA code or follow the link and read on if your not shure how to add it.

I got the solution from the last post in this thread http://social.msdn.microsoft.com/Forums/da/isvvba/thread/6c0c087e-4d9f-4b8e-844c-5803492542bd

Regards,
Christian

 

[alcalina]

I want to thank you kakzhetak
Your code help me a lot.
I just did a small change to jump lines that has errors

Function CreateCSV2(caminho As String)


    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 = caminho
    lFnum = FreeFile
   
    
    Open sFname For Output As lFnum
    Application.DisplayAlerts = True
    'Loop through the rows'
        For Each rRow In ActiveSheet.UsedRange.Rows
        'Loop through the cells in the rows'
        For Each rCell In rRow.Cells
            On Error GoTo ErrHandler: 'Error if has cell with error
            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


      
ErrHandler:
Resume NextLine
NextLine:
        sOutput = ""
     Next rRow
    
    'Close the file'
    Close lFnum
    
Exit Function


End Function