Jump to content
Barthezz88

Excel value copy to external program - (Moved)

Recommended Posts

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:

image.png.e57aa8372a245c7584cb89cf1bbc7cdc.png

External program:

image.png.2738aa550bb57e83416cd85e6a21386f.png

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

Share this post


Link to post
Share on other sites

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

 


SciTE4AutoIt3 Full installer Download page   - Beta files       Read before posting     How to post scriptsource   Forum etiquette  Forum Rules 
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

@Jos, bedankt hoor..

Share this post


Link to post
Share on other sites
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


SciTE4AutoIt3 Full installer Download page   - Beta files       Read before posting     How to post scriptsource   Forum etiquette  Forum Rules 
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

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 by Nine

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

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...