Change decimal separator in VBA

Hello,

I want to write some value from Excel cells to a MySQL table.
The value of one cell is a double with a comma as a separator (european format 10,20) and when i try to save the value to MySQL the value is written as a integer value. This is because MySQL sees a dot as a separator (10.20)

How can i change the separator to save the value correct in the MySQL table.
I have the following code which i use:

Dim strPersnum As String
Dim strKenmerk As String
Dim strProjectnr As Long
Dim strCode As Long
Dim strOmschrijving As String
'Dim strUren As Long
Dim strUren As String

On Error Resume Next
KoppelingMetAccessMaken

'Gegevens uitlezen en kenmerkcode bepalen
strPersnum = Cells(4, 5).Value
strKenmerk = fcRijNaarCode(r)
strProjectnr = Cells(r, 2).Value
strCode = Cells(r, 3).Value
strAannemer = Cells(r, 4)
strOmschrijving = Cells(r, 5).Value
strUren = Cells(r, 6).Value


'Controleren of het projectnummer in aannemer reeds bestaat zoniet dan deze aanmaken
strSQL = "SELECT * FROM Aannemer WHERE Projectnr =" & strProjectnr
Set rs = Database.Execute(strSQL)
If Err Then MsgBox Err.Description
Err.Clear

srtest = rs.Fields.Item(0).Value
If rs.Fields.Item(0).Value = "" Then
    strSQL = "INSERT INTO Aannemer VALUES(" & strProjectnr & ",'" & strAannemer & "','')"
    Database.Execute (strSQL)
    If Err Then MsgBox Err.Description
    Err.Clear
End If

'de gegevens uitlezen en in de Urenlijst stoppen
strSQL = "INSERT INTO Urenlijst VALUES('" & strKenmerk & " '," & strProjectnr & "," & strCode & ",'" & strOmschrijving & "'," & strUren & "'," & strPersnum & ")"
Database.Execute (strSQL)
If Err Then 'als de record reeds bestaat dan deze updaten
    Err.Clear
    strSQL1 = "UPDATE Urenlijst SET Projectnr=" & strProjectnr & " WHERE Kenmerk='" & strKenmerk & " AND Personeelsnr='" & strPersnum & "'"
    strSQL2 = "UPDATE Urenlijst SET Code=" & strCode & " WHERE Kenmerk='" & strKenmerk & " AND Personeelsnr='" & strPersnum & "'"
    strSQL3 = "UPDATE Urenlijst SET Omschrijving='" & strOmschrijving & "' WHERE Kenmerk='" & strKenmerk & " AND Personeelsnr='" & strPersnum & "'"
    strSQL4 = "UPDATE Urenlijst SET Uren=" & strUren & " WHERE Kenmerk='" & strKenmerk & " AND Personeelsnr='" & strPersnum & "'"    Database.Execute (strSQL1): Database.Execute (strSQL2): Database.Execute (strSQL3): Database.Execute (strSQL4)
    If Err Then MsgBox Err.Description
End If



marco_grevenAsked:
Who is Participating?
 
andrewssd3Connect With a Mentor Commented:
If the value in the cell was a string, you may need to say strUren = Str(CDbl(Cells(r, 6).Value)).  It might help if you could post the Excel workbook so we can see exactly what's going wrong
0
 
Patrick MatthewsCommented:
In MySQL, what data type was used for the column?
0
 
marco_grevenAuthor Commented:
Hello,

In MySQL the field has a DECIMAL data type

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Patrick MatthewsCommented:
OK, and what precision and scale did you use for decimal?

Hint: if the scale is zero, or unspecified, then you did not reserve any space for the decimal portion :)
0
 
marco_grevenAuthor Commented:
The length of the field is 18 with 2 decimals...

When i put a value 10,2 in the cell... the update goes wrong (value = 10 in MySQL table)
When i put a value 10.2 in the cell... the update goes well (value = 10.2 in MySQL table)

0
 
andrewssd3Commented:
You could use the Str function, which is not locale aware and always returns . as decimal separator, e.g.

strUren = Str(Cells(r, 6).Value)

Open in new window

0
 
jan24Commented:
More generally, you can use WorksheetFunction.Text() to convert from a number to a string by apply any Excel number format, just like you use in the Format Cells dialog box.
0
 
marco_grevenAuthor Commented:
i tried the above solutions but none of them works.

Does anyone have a example how to store a Cell.value with the Decimal format "10,20" to MySQL (which works with the dot separator)

0
 
jan24Commented:
If the value you're starting with is a string, then convert it to a double first based on decimal comma format, and then back to a string based on decimal point.
0
 
andrewssd3Commented:
Are you sure the Str function does not work? - I have used it in similar circumstances before, and I'm pretty sure it does change the decimal separator to a . - what value goes into strUren when you use strUren = Str(Cells(r, 6).Value) ?
0
 
andrewssd3Commented:
At the very most basic you could use
Replace(CStr(Cells(r,6).Value),",",".")

Open in new window

, but I prefer the Str route as I think it would work in any locale (if there are any that use neither comma or period for a decimal separator)
0
 
marco_grevenAuthor Commented:
THE value in the above sample was à string, but thatcher didn't workshop.
THE originaliteit value off THE cell is 'standard' and is put in à variable of the long data type.

0
 
broro183Commented:
hi,

I agree with Andrewssdd3, it would be easier to test with an example file.

Another possible approach (in theory!) is to over-ride the current setting for the decimal separator & then reset it once the code is run, here's a snippet of code that I recorded in excel 2007 which you may be able to modify...



Sub Macro1()

Dim oriDecmlSep As String    '"ori" = original
Dim oriSysSep As Boolean
Dim r As Long
Dim strUren As String


    With Application
        oriDecmlSep = .DecimalSeparator
        .DecimalSeparator = ","
        'i'm not sure if you need the next line...
        .ThousandsSeparator = vbNullString
        oriSysSep = .UseSystemSeparators
        .UseSystemSeparators = False
    End With

    'run your code, eg...
    r = 2
'Note from the excel 2007 help files:
'The Str function recognizes only the period (.) as a valid decimal separator. When different decimal separators may be used (for example, in international applications), use CStr to convert a number to a string.
    strUren = CStr(Cells(r, 6).Value)



    'reset the user's original settings
    With Application
        .DecimalSeparator = oriDecmlSep
        'i'm not sure if you need the next line...
        '.ThousandsSeparator = vbNullString
        .UseSystemSeparators = oriSysSep
    End With

End Sub

Open in new window


hth
Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.