Solved

Change decimal separator in VBA

Posted on 2011-09-27
13
1,651 Views
Last Modified: 2012-05-12
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



0
Comment
Question by:marco_greven
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36712915
In MySQL, what data type was used for the column?
0
 

Author Comment

by:marco_greven
ID: 36713015
Hello,

In MySQL the field has a DECIMAL data type

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36713035
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:marco_greven
ID: 36713056
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36713242
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
 
LVL 2

Expert Comment

by:jan24
ID: 36713322
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
 

Author Comment

by:marco_greven
ID: 36714688
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
 
LVL 2

Expert Comment

by:jan24
ID: 36715102
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36715110
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36715179
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
 

Author Comment

by:marco_greven
ID: 36720012
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
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36720047
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
 
LVL 10

Expert Comment

by:broro183
ID: 36720359
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

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question