Excel value copy to external program - (Moved)

[Barthezz88]

Hello everybody,

First of all my apologies for my english writing. 

I'm new to AutoIt3 and I have a question. I want to use a excel value in a external program. I created this code:

#Include <MsgBoxConstants.au3>
#Include <Excel.au3>
#include <AutoItConstants.au3>

$oExcel = _Excel_Open()
$oWorkbook = $oExcel.ActiveWorkbook
$fData = _Excel_RangeRead($oWorkbook,Default,"B2",4)
MsgBox(0,"",$fData,1) ;just to check the value

If Not WinExists("D-Sheet Piling") Then
   MsgBox($MB_ICONINFORMATION,"D-Sheet Piling","Er is geen D-Sheet model geopend")
EndIf

If WinExists("D-Sheet Piling") Then
   WinActivate("D-Sheet Piling")
   WinWaitActive("D-Sheet Piling")
Send("!l")
Send ("h")
WinWaitActive("Horizontal Forces")
Send("{TAB 3}")
Send("{END}")
Send("{RIGHT 2}")
Send($fData)
Send("{TAB}")
Send("{ENTER}")
WinWait("[CLASS:TSHMainForm]")
Send("{F9}")
WinActive("[CLASS:TSHCalculationProgressForm]")
Sleep(2000)
Send("c")
;$oExcel = ObjGet("", "Excel.Application")
;$oExcel.Run("Error_1")
EndIf
Exit

I read the value from cell "B2" out of Excel. I convert the value in a variable ($fData). Then I send this variable to a external program using 'Send'. Now there is the problem, if the value in "B2" is for example '201.6', and I send the value to my external program (using the presented code). Then the value '2016' is copied on the specific location in the external program. Wich is obvious wrong, I miss the comma in the value.

Excel:

External program:

Can someone tell me what I'm doing wrong? I want to use a second variable en copied this one also in the external program. This is a much smaller (Level) value, so the margin of error is much bigger. I hope someone can help me with this problem..

[Jos]

Looks like you have a regional issue where the other program uses a decimal comma en excel a decimal point?
Maybe try replacing the "." in the variable $fData with a ","?

(Je Engels is prima hoor  )

Jos

 

[water]

Your Excel uses "." and the other application needs a ",".

Use something like this:

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $oWorkbook = $oExcel.ActiveWorkbook
Global $fData = _Excel_RangeRead($oWorkbook, Default, "B2", 4)
MsgBox(0, "", $fData & @CRLF & VarGetType($fData))
$fData = StringReplace($fData, ".", ",")
MsgBox(0, "", $fData & @CRLF & VarGetType($fData))

Thes MsgBox statements are just for debugging - can be removed when everything works as expected.

[water]

BTW:
When posting code, please use the AutoIt code tags in the editor (the "<>" button)

[Barthezz88]

The solution supplied by water does the trick. Thanks for the help. Can you explain what exactly happen in the code?

@Jos, bedankt hoor..

[Jos]

4 minutes ago, Barthezz88 said:

Can you explain what exactly happen in the code?

This was described by both me and Water already. Just look at the statements and press F1 in SciTE on any function you do not understand or want to know more about.

Js

[Nine]

If you prefer, you can change the workbook settings globally so you don't have to do lots of StringReplace :

#include <Constants.au3>
#include <Excel.au3>

Opt ("MustDeclareVars", 1)

Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\Test.xls"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error open")
$oExcel.DecimalSeparator = ","
$oExcel.ThousandsSeparator = ""
$oExcel.UseSystemSeparators = False

 

Edited February 3, 2020 by Nine

[JLogan3o13]

Moved to the appropriate forum.

Moderation Team