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

[renaixsence]

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.

[zorphnog]

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

[renaixsence]

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 April 22, 2010 by renaixsence

[zorphnog]

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 April 21, 2010 by zorphnog

[Fulano]

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 April 21, 2010 by Fulano

[renaixsence]

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.)

Edited April 22, 2010 by renaixsence