Solved

Change decimal separator in VBA

Posted on 2011-09-27
13
1,513 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
  • 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now