[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Change decimal separator in VBA

Posted on 2011-09-27
13
Medium Priority
?
1,744 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 93

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 93

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

656 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