Jump to content
Sign in to follow this  
Bert

_Excel_BookAttach, _Excel_RangeRead

Recommended Posts

Bert

I'm getting intermitted behavior on this and I'm not sure what is causing it.

Here is my code

In a nutshell this is what my entire script does:

1. I create a GUI that ask for what row in the Excel spreadsheet to read.

2. Populate the fields in the web page

(yes, I should have written a function for the repeating code....I got lazy in that regard..  >_<

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=H:\AutoIt scripts_local\icons\SafetyHelmet.ico
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <ie.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <WindowsConstants.au3>
#include <GUIConstantsEx.au3>
#include <EditConstants.au3>
Local $sWorkbook = @ScriptDir & "\Web App testing.xlsx"
Local $RowX = IniRead(@ScriptDir&"/settings.ini","1", "1","2")
Local $hGUI = GUICreate("Row?", 200, 110,-1,-1,-1,$WS_EX_TOPMOST )
Local $button1 = GUICtrlCreateButton("OK",40, 80, 50, 20)
Local $button2 = GUICtrlCreateButton("Cancel",110, 80, 50, 20)
Local $lable = GUICtrlCreateLabel("Enter in the Excel Row", 50, 10)
Local $input = GUICtrlCreateInput("", 75, 30, 50, 25,BitOR($ES_NUMBER,$ES_RIGHT))
local $update = GUICtrlSetData(-1,$RowX, "2")
GUISetState(@SW_SHOW, $hGUI)
    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                GUIDelete($hGUI)
                Exit
            Case $button1
                $RowX = GUICtrlRead($input)
                IniWrite(@ScriptDir&"/settings.ini","1", "1",$RowX)
                GUIDelete($hGUI)
                ExitLoop
            Case $button2
                GUIDelete($hGUI)
                Exit
        EndSwitch
    WEnd

$oExcel1  = _Excel_BookAttach($sWorkbook)
$oIE = _IECreate ("http://xxxxxxxxxxxxxxxxxxxxxxxxxxxx/",1 ) ;removed for security reasons
$oForm = _IEFormGetObjByName ($oIE, "Form")
$oQuery1 = _IEFormElementGetObjByName ($oForm, "txtentityType")
$sResult1 = _Excel_RangeRead($oExcel1, "Test data setups", "F"&$RowX)
_IEFormElementOptionSelect ($oQuery1, $sResult1, 1, "byText")
$oQuery2 = _IEFormElementGetObjByName ($oForm, "txtCIN")
$sResult = _Excel_RangeRead($oExcel1, "Test data setups", "C"&$RowX)
_IEFormElementSetValue ($oQuery2, $sResult)
$oQuery3 = _IEFormElementGetObjByName ($oForm, "txtAppId")
$sResult3 = _Excel_RangeRead($oExcel1, "Test data setups", "D"&$RowX)
_IEFormElementSetValue ($oQuery3, $sResult3)
$oQuery4 = _IEFormElementGetObjByName ($oForm, "txtLOBId")
$sResult4 = _Excel_RangeRead($oExcel1, "Test data setups", "E"&$RowX)
_IEFormElementSetValue ($oQuery4, $sResult4)
$sResult5 = _Excel_RangeRead($oExcel1, "Test data setups", "G"&$RowX)
$oQuery5 = _IEFormElementGetObjByName ($oForm, "txtClientOpeningChannel")
_IEFormElementOptionSelect ($oQuery5, $sResult5, 1, "byText")
$sResult6 = _Excel_RangeRead($oExcel1, "Test data setups", "H"&$RowX)
$oQuery6 = _IEFormElementGetObjByName ($oForm, "txtAddressType")
_IEFormElementOptionSelect ($oQuery6, $sResult6, 1, "byText")
$oQuery7 = _IEFormElementGetObjByName ($oForm, "txtAddressLine1")
$sResult7 = _Excel_RangeRead($oExcel1, "Test data setups", "I"&$RowX)
_IEFormElementSetValue ($oQuery7, $sResult7)
$oQuery8 = _IEFormElementGetObjByName ($oForm, "txtAddressLine2")
$sResult8 = _Excel_RangeRead($oExcel1, "Test data setups", "J"&$RowX)
_IEFormElementSetValue ($oQuery8, $sResult8)
$oQuery9 = _IEFormElementGetObjByName ($oForm, "txtZipcode")
$sResult9 = _Excel_RangeRead($oExcel1, "Test data setups", "K"&$RowX)
_IEFormElementSetValue ($oQuery9, $sResult9)
$oQuery10 = _IEFormElementGetObjByName ($oForm, "txtCity")
$sResult10 = _Excel_RangeRead($oExcel1, "Test data setups", "L"&$RowX)
_IEFormElementSetValue ($oQuery10, $sResult10)
$oQuery11 = _IEFormElementGetObjByName ($oForm, "txtState")
$sResult11 = _Excel_RangeRead($oExcel1, "Test data setups", "M"&$RowX)
_IEFormElementOptionSelect ($oQuery11, $sResult11, 1, "byText")
$oQuery12 = _IEFormElementGetObjByName ($oForm, "txtOtherState")
$sResult12 = _Excel_RangeRead($oExcel1, "Test data setups", "N"&$RowX)
_IEFormElementSetValue ($oQuery12, $sResult12)
$oQuery13 = _IEFormElementGetObjByName ($oForm, "txtCountry")
$sResult13 = _Excel_RangeRead($oExcel1, "Test data setups", "O"&$RowX)
_IEFormElementOptionSelect ($oQuery13, $sResult13, 1, "byText")
$oQuery14 = _IEFormElementGetObjByName ($oForm, "txtDate")
$sResult14 = _Excel_RangeRead($oExcel1, "Test data setups", "P"&$RowX)
_IEFormElementSetValue ($oQuery14, $sResult14)
$oQuery15 = _IEFormElementGetObjByName ($oForm, "txtRelationShipLength")
$sResult15 = _Excel_RangeRead($oExcel1, "Test data setups", "Q"&$RowX)
_IEFormElementSetValue ($oQuery15, $sResult15)
$oQuery16 = _IEFormElementGetObjByName ($oForm, "txtTinType")
$sResult16 = _Excel_RangeRead($oExcel1, "Test data setups", "R"&$RowX)
_IEFormElementOptionSelect ($oQuery16, $sResult16, 1, "byText")
$oQuery17 = _IEFormElementGetObjByName ($oForm, "txtTIN")
$sResult17 = _Excel_RangeRead($oExcel1, "Test data setups", "S"&$RowX)
_IEFormElementSetValue ($oQuery17, $sResult17)
$oQuery18 = _IEFormElementGetObjByName ($oForm, "txtPhoneNumber")
$sResult18 = _Excel_RangeRead($oExcel1, "Test data setups", "T"&$RowX)
_IEFormElementSetValue ($oQuery18, $sResult18)
$oQuery19 = _IEFormElementGetObjByName ($oForm, "txtEmail")
$sResult19 = _Excel_RangeRead($oExcel1, "Test data setups", "U"&$RowX)
_IEFormElementSetValue ($oQuery19, $sResult19)
$oQuery20 = _IEFormElementGetObjByName ($oForm, "txtPrefix")
$sResult20 = _Excel_RangeRead($oExcel1, "Test data setups", "V"&$RowX)
_IEFormElementSetValue ($oQuery20, $sResult20)
$oQuery21 = _IEFormElementGetObjByName ($oForm, "txtsuffix")
$sResult21 = _Excel_RangeRead($oExcel1, "Test data setups", "W"&$RowX)
_IEFormElementSetValue ($oQuery21, $sResult21)
$oQuery22 = _IEFormElementGetObjByName ($oForm, "txtFistName")
$sResult22= _Excel_RangeRead($oExcel1, "Test data setups", "X"&$RowX)
_IEFormElementSetValue ($oQuery22, $sResult22)
$oQuery23 = _IEFormElementGetObjByName ($oForm, "txtmiddleName")
$sResult23= _Excel_RangeRead($oExcel1, "Test data setups", "Y"&$RowX)
_IEFormElementSetValue ($oQuery23, $sResult23)
$oQuery24 = _IEFormElementGetObjByName ($oForm, "txtLasttName")
$sResult24= _Excel_RangeRead($oExcel1, "Test data setups", "Z"&$RowX)
_IEFormElementSetValue ($oQuery24, $sResult24)
$oQuery25 = _IEFormElementGetObjByName ($oForm, "txtOccupation")
$sResult25 = _Excel_RangeRead($oExcel1,"Test data setups", "AA"&$RowX)
_IEFormElementOptionSelect ($oQuery25, $sResult25, 1, "byText")
$oQuery26 = _IEFormElementGetObjByName ($oForm, "txtEmployer")
$sResult26= _Excel_RangeRead($oExcel1, "Test data setups", "AB"&$RowX)
_IEFormElementSetValue ($oQuery26, $sResult26)
$oQuery27 = _IEFormElementGetObjByName ($oForm, "txtCitizenshipCountry")
$sResult27 = _Excel_RangeRead($oExcel1, "Test data setups", "AC"&$RowX)
_IEFormElementOptionSelect ($oQuery27, $sResult27, 1, "byText")

What is happening here is sometimes the IE form fields are getting populated, and sometimes not. I notice that when the Excel work page is not selected in the workbook this problem sometimes appears. If the Excel work page is selected, the problem never appears. If when it the problem appears (wrong work page) and I then switch focus to the correct work page, then the problem corrects itself.

It sometimes will work as intended when the excel workbook does NOT set to the correct work page. That is what is so odd

Second weird thing - I can simply recompile my code and then the code works again as intended. I can run the exe a few times then my exe fails again.

Thoughts?

Share this post


Link to post
Share on other sites
water

I will check as soon as I return from vacation.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

I would like you to add some error checking after calling any of the _Excel* functions. Could you please write the value of @error and the address of the read cell to a file or the console so we know what happens?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Bert

Now the script is working perfectly. I can't explain what is different other than I rebooted my PC. I put error checking on the first 4 and this is what I got as a return:

Excel CIN:   Error: 0     Extended: 0
   IE CIN:   Error: 0     Extended: 0

Excel AppID:   Error: 0     Extended: 0
   IE AppID:   Error: 0     Extended: 0

Excel LOB:   Error: 0     Extended: 0
   IE LOB:   Error: 0     Extended: 0

Excel Client Opening Channel:   Error: 0     Extended: 0
                IE TEST DATA:   Error: 0     Extended: 0

Excel Address Type  :   Error: 0     Extended: 0
     IE Address Type:   Error: 0     Extended: 0

 

I tried every which way to get it to fail like it did last week. Maybe it was just my PC. I'll keep you looped in in case it breaks again. Thanks.

Share this post


Link to post
Share on other sites
water

To make debugging easier and to enhance performance I suggest to read the whole worksheet in one go and then process the resulting array.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Fine :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Bert

I just figured out the bug. I think it is an Excel problem. If I'm editing a cell directly and then run the script, the cells can't be read by the script for the cell is locked for editing.

To recreate the issue, create a simple script to access a cell and echo the contents in a msgbox or consolewrite. Have the spreadsheet open and double click on the cell in question to place your mouse cursor in it. Make sure there is data in the cell. Run your script. The script should fail to read the cell and return 0.

Edited by MBALZESHARI

Share this post


Link to post
Share on other sites
water

That's correct. You can'tt access a cell by the UDF and the GUI at the same time.

That's why _Excel_Open has parameter $bInteractive. If set to False, no user input is possible.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

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  

×