Sign in to follow this  
Followers 0
renaixsence

Changing the decimal separator from "." to ","

6 posts in this topic

I made a small script to write data to an excelsheet. When I enter the data directly into excel with the numkeys the "." ( decimal separator ) is recognized by excel and "translated" into a ",". This causes my formulas to work.

Now when I do the same with Autoit the typed "." stays a "." when the script writes to excel and causes the formulas in excel NOT to work.

Excel doesn't longer recognize the data as a number ( even when I change the cell formatting ).

How can I type e.g. 41.23 in my GUI and let Autoit write it to Excel as 41,23 ?

Changing the regional settings is not an option because I'm not the only one using the computer and the script will be used on different computers.

Share this post


Link to post
Share on other sites



#3 ·  Posted (edited)

StringReplace("41.23", ".", ",")

Thanks, works fine but isn't there another way to do this because now I need to add the line to every line where I use a number like this :

....
_ExcelWriteCell($oExcel, StringReplace(GUICtrlRead($AbnElek), ".", ","), "C"& $iStartRow + $i-1)
_ExcelWriteCell($oExcel, StringReplace(GUICtrlRead($VoorElek),".", ","), "D"& $iStartRow + $i-1)
....
Edited by renaixsence

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Well you can just change the input on the fly as the user types it into an input. This example gets a little more complicated if you haven't handled notifications before, but it will essentially change any "." that a user types to a ",".

#include <GuiConstants.au3>
#include <EditConstants.au3>
#include <WindowsConstants.au3>
#include <StructureConstants.au3>

$gui = GUICreate("On the fly", 200, 160, -1, -1)
$inText1 = GUICtrlCreateInput("", 10, 10, 180)
$inText2 = GUICtrlCreateInput("", 10, 40, 180)
$inText3 = GUICtrlCreateInput("", 10, 70, 180)
$inText4 = GUICtrlCreateInput("", 10, 100, 180)
$inText5 = GUICtrlCreateInput("", 10, 130, 180)
GUIRegisterMsg($WM_COMMAND, "_WM_COMMAND")
GUISetState(@SW_SHOW)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
    EndSwitch
WEnd


Func _WM_COMMAND($hWnd, $iMsg, $iwParam, $ilParam)
    Local $nNotifyCode, $nID
    $nNotifyCode = BitShift($iwParam, 16)
    $nID = BitAND($iwParam, 0xFFFF)

    Switch $nID
        Case $inText1, $inText2, $inText3, $inText4, $inText5
            If $nNotifyCode = $EN_CHANGE Then GUICtrlSetData($nID, StringReplace(GUICtrlRead($nID), ".", ","))
    EndSwitch
    Return $GUI_RUNDEFMSG
EndFunc
Edited by zorphnog

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Thanks, works fine but isn't there another way to do this because now I need to add the line to every line where I use a number like this :

....
_ExcelWriteCell($oExcel, StringReplace(GUICtrlRead($AbnElek)[b], ".", ","), "C"& $iStartRow + $i-1)
_ExcelWriteCell($oExcel, StringReplace(GUICtrlRead($VoorElek)[b],".", ","), "D"& $iStartRow + $i-1)
....

Functions are a great way to do it. Normally I'd use a more ... descriptive function name, but if you are just worried about cutting down on your typing E(xcel)p(eriod) ToC(omma) isn't that horrible of a mnemonic.

Func EpToC ($string)
    Return StringReplace($string, ".", ",")
EndFunc
...
_ExcelWriteCell($oExcel, EpToC(GUICtrlRead($AbnElek)[b]), "C"& $iStartRow + $i-1)
Edited by Fulano

#fgpkerw4kcmnq2mns1ax7ilndopen (Q, $0); while ($l = <Q>){if ($l =~ m/^#.*/){$l =~ tr/a-z1-9#/Huh, Junketeer's Alternate Pro Ace /; print $l;}}close (Q);[code] tag ninja!

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Thanks guys. Don't mind the "" in the script. It's something I forgot to remove. (I wanted to make some text in the script Bold to highlight the part I used.) :idea:

Edited by renaixsence

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0