_Excel_BookAttach, _Excel_RangeRead

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

expandcollapse popup

#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?

[water]

I will check as soon as I return from vacation.

[Bert]

Thanks!

[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?

[Bert]

sure. Let me do a few test and I'll report back my results

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

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

[Bert]

ok, I can do that. However it is working as expected so if it gets all dodgy again I will do more debugging.

[water]

Fine

[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 August 25, 2014 by MBALZESHARI

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

[Bert]

Thanks!