Jump to content

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

Link to post
Share on other sites
  • Developers

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

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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
  • Developers
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.
  :)

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