wrong divider in csv-file

**ptrolle** · 01-23-2010, 12:06 PM

Hi

I am searching for a solution to this little problem:

when saving a spreadsheet in csv-format from the user interface of Excel (a Danish version), the file is saved with a ";" as divider (Danish), which is what I want, but when the saving is executed in code like the code below, the file is saved with a "," as divider (USEnglish). When the file then is opened in Excel, each row of data are placed in one cell instead of multiple cells.

Code:

ActiveWorkbook.Worksheets(2).SaveAs Filename:= _
        "etrade.csv", _
        FileFormat:=xlCSV, CreateBackup:=False, local:=True

Please help me as I have a deadline in two days

trolle

**Richard Buttrey** · 01-23-2010, 12:21 PM

Hi,

Probably the simplest way is to just select the single column and use the Data Text to Columns functionality and pick the ';' delimiter.

Alternatively you could temporarily change the list separator in the regional settings before opening the file.

HTH

**ptrolle** · 01-23-2010, 01:52 PM

Thank you for the fast answer.

Unfortunately your solutions are not applicable in this situation as the csv file is to be used by another application of which I know nothing. So I am forced to deliver the csv file in the "proper" format. The strange thing is, that it is only when the saving is done by the VB-code the problem arises. When saving is done from the Excel user interface, the csv file is delivered in the wanted format. I have checked the settings with this snippet:

Code:

Public Sub language()
If Application.LanguageSettings.LanguageID(msoLanguageIDExeMode) _
    > Application.LanguageSettings.LanguageID(msoLanguageIDUI) _
    Then
    MsgBox "The user interface language and execution " & _
    "mode are different."
    Else
    MsgBox Application.LanguageSettings.LanguageID(msoLanguageIDExeMode)
    End If
    MsgBox "The column separator is " & _
    Application.International(xlCountrySetting)
End Sub

and the settings are correctly set (Danish) but the code executor insists on delivering in USEnglish!!

greetings trolle

**protonLeah** · 01-24-2010, 12:12 AM

The macro below will build a string of semi-colon delimited string from each row of your file and put that string in the first cell of the corresponding row of a new sheet. So when it is done, you will have a single column of semi-colon delimited values. The sheet is moved and saved as a CSV file.

Code:

Option Explicit
Sub xl2csv()
    Dim TempString      As String, _
        ExportSource    As String, _
        DestSheet       As String, _
        DestFileName    As String, _
        RootPath        As String, _
        ShCell          As Range, _
        ShRecord        As Range, _
        RowControl      As Long, _
        LastCol         As Long, _
        LastRow         As Long
        
        ExportSource = "Archive"
        DestSheet = "export"
        DestFileName = "etrade.csv"
        RootPath = "C:\Downloads\Excel Forum\"
        
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Select
        ActiveSheet.Name = DestSheet
        
        Sheets(ExportSource).Select
        TempString = ActiveSheet.UsedRange.Address(0, 0)
        Debug.Print TempString
        LastCol = ActiveSheet.UsedRange.Columns.Count
        LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        
        For RowControl = 1 To LastRow
            TempString = ""
            Set ShRecord = Range(Cells(RowControl, 1), Cells(RowControl, LastCol))
            
            For Each ShCell In ShRecord     'add the data in each cell to the string separated by semi-colon
                TempString = TempString & Chr(59)
            Next ShCell
            
            'strip excess semi's from the end if the string
            While Right(TempString, 1) = Chr(59)
                TempString = Left(TempString, Len(TempString) - 1)
            Wend
            
            Sheets(DestSheet).Cells(RowControl, 1).Value = TempString
        Next RowControl
            
        Sheets(DestSheet).Select
        Sheets(DestSheet).Move
        
        ActiveWorkbook.SaveAs _
            Filename:=RootPath & DestFileName, _
            FileFormat:=xlCSV, _
            CreateBackup:=False
    
        ActiveWorkbook.Save
        ActiveWindow.Close True
End Sub

**ptrolle** · 01-25-2010, 07:23 AM

Hi

Thank you for your inputs. I couldn't use any of your solutions but they inspired me to a simple and workable way to create the wanted output:

Code:

Public Sub WriteData(ro, co) 
        Format LineToWrite
        Format SemiKolon
        
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set a = fs.CreateTextFile("etrade.csv", True)
    r = 0
        Do While CostumerData(r, 0) <> ""
            LineToWrite = ""
            SemiKolon = ";"
            For m = 1 To co
                If m = co Then SemiKolon = ""
                LineToWrite = LineToWrite & CostumerData(r, m) & SemiKolon
            Next
            a.writeline LineToWrite
            r = r + 1
        Loop
        a.Close
End Sub

Thank you

greetings trolle