VBA: CDbl ignores comma decimal separator

Question

I am running a macro that copies specific cell values from a spreadsheet to another. The files are csv, semicolon separated, containing general formatted cells, including both literal items and numerical items. Numerical items have comma as decimal separator. The script imports properly everything, however, it ignores commas when importing the numerical values.

Here is the code:

Public Function importCsv2(strPath As String)
Dim filePath As String
filePath = strPath
Dim fileArray(5, 5) As String
Dim startDate As String
Dim Product As String
Application.DecimalSeparator = ","
If Range("B14").Value <> "" Then
    Range("B13").End(xlDown).offset(1, 0).Select
Else:
    Range("B13:B" & Range("B" & Rows.Count).End(xlUp).Row).offset(1, 0).Select
End If
currentRow = 0
rowNumber = 0

Open filePath For Input As #1

Do Until EOF(1)
    Line Input #1, lineFromFile
    fileStr = Split(lineFromFile, vbLf)
    Dim item As Variant
    For Each item In fileStr
    'For item = LBound(fileStr) To UBound(fileStr)
        lineitems = Split(item, ";")
        'Debug.Print (item)
        If rowNumber = 1 Then
            startDate = lineitems(6)
            Product = lineitems(9)
        End If
        If rowNumber > 3 And item <> "" Then

            If Not doesOfferExist(CStr(lineitems(2))) And CInt(lineitems(0)) <> 0 Then
                ActiveCell.offset(currentRow, 0) = startDate
                ActiveCell.offset(currentRow, 1) = lineitems(4)
                ActiveCell.offset(currentRow, 2) = lineitems(3)
                ActiveCell.offset(currentRow, 3) = CDbl(lineitems(6))
                ActiveCell.offset(currentRow, 4) = CDbl(lineitems(7))
                ActiveCell.offset(currentRow, 5) = lineitems(8)
                ActiveCell.offset(currentRow, 6) = lineitems(1)
                ActiveCell.offset(currentRow, 7) = lineitems(2)
                ActiveCell.offset(currentRow, 8) = "New"
                ActiveCell.offset(currentRow, 9) = Product
                currentRow = currentRow + 1
            End If
        End If


    Next item
    rowNumber = rowNumber + 1
Loop
Close #1

Call setImportLastUpdate
End Function

The critical part is:

ActiveCell.offset(currentRow, 3) = CDbl(lineitems(6))
ActiveCell.offset(currentRow, 4) = CDbl(lineitems(7))

The excel options have comma set as decimal separator.

For instance, when importing 84,55 I get 8455 as a result. I have added Application.DecimalSeparator = "," but it doesn't solve the problem.

Any help? thank you in advance

It's not clear what you mean by "it ignores commas when importing the numerical values". Do you mean the imported values are correct but displayed without the decimal separator? Or do you mean they are imported with incorrect values, e.g. the comma is being interpreted as a thousands separator? — Ben, Mar 08 '19 at 08:40
It looks like you also have to set `ThousandsSeparator` and also set `Application.UseSystemSeparators = False` . https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.DecimalSeparator — Ben, Mar 08 '19 at 08:42
I also put an example...anyway, I repeat, from a value like 1,2 it imports 12. Everything else is correct: correct cell, correct splitting, correct numbers, etc. It is just the comma disappearing the problem. — Luca91, Mar 08 '19 at 08:43
Seems like the comma is being interpreted as a thousands separator — Ben, Mar 08 '19 at 08:46
That's not how thousands separators work. They are used for display only but are ignored on input. — Ben, Mar 08 '19 at 08:48
OK look I've read your code now. CDbl doesn't care about Application.ThousandsSeparator or Application.DecimalSeparator. It just uses the system locale. — Ben, Mar 08 '19 at 08:50
Since you are writing the import code just write your own ConvertDouble code — Ben, Mar 08 '19 at 08:51
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/189653/discussion-between-ben-and-luca91). — Ben, Mar 08 '19 at 08:51
Possible duplicate of [Visual basic handle decimal comma](https://stackoverflow.com/questions/6215267/visual-basic-handle-decimal-comma) — Ben, Mar 08 '19 at 09:05
Seems you are trying to re-invent the wheel. Either the legacy CSV import tool, or Power Query, have options to set the desired decimal and thousands separators when importing a CSV file. Also look at the `QueryTables.Add` method — Ron Rosenfeld, Mar 08 '19 at 10:43

score 3 · Answer 1 · answered Dec 14 '20 at 09:13

3

A bit late but for future reference.

Set both the ThousandsSeparator and the DecimalSeparator like this:

Application.ThousandsSeparator = "."
Application.DecimalSeparator = ","

answered Dec 14 '20 at 09:13

Tveitan

288
5
14

VBA: CDbl ignores comma decimal separator

1 Answers1

Linked