_Excel_RangeFind

[Happy82]

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Debug.au3>
_DebugSetup()
_DebugCOMError()
$slptime = 500
Local $oAppl = _Excel_Open()
   If @error Then Exit ConsoleWrite("Error creating application object" & @CRLF)

Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Unassigned Credit Card Transactions (MAIN REPORT).xlsx")
    If @error Then Exit ConsoleWrite("Error creating workbook object" & @CRLF)


Local $aWorkSheets = _Excel_SheetList($oWorkbook)
    If @error Then Exit ConsoleWrite("Error enumerating worksheets" & @CRLF)

For $i = 0 To UBound($aWorkSheets) - 1
    Sleep($slptime)
    Local $StrVal = "Employee Org Unit 1 - Name"
    Local $aResult = _Excel_RangeFind($aWorkSheets, $StrVal)
    Local $vResult = $aResult[0][2]
    MsgBox(0, "AutoIt - Concur", "Beginning The processing of file : " & $vResult)
Next
Exit

I am trying to find the Address of the string in the spread sheet.

But when i am executing it i ma getting error

"C:\Testing.au3" (22) :" ==> Subscript used on non-accessible variable.:
Local $vResult = $aResult[0][2]
Local $vResult = $aResult^ ERROR

Not sure what the mistake i am making.

Any help is appreciated.

[Jfish]

Are you sure it is finding that string?  Perhaps check to see what is in your array with _arrayDisplay($aResult).   Check for @error also ...

[Happy82]

Thanks for the help.... I fixed the issue. It was due to

$aWorkSheets.

changed it

$oWorkbook

it is working now.

Still i am not getting. I have assigned the $oWorkBook to $aWorkSheets (active sheet). Why would it throw this error (1 - $oWorkbook is not an object or not a workbook object)

[Jfish]

Is the path to the workbook correct?  Also, where do you get that error specifically?

Can you show the full code now so I can see what you changed?  It is not clear from your post.

Edited July 15, 2015 by Jfish

[Happy82]

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Debug.au3>
_DebugSetup()
_DebugCOMError()
$slptime = 500
Local $oAppl = _Excel_Open()
   If @error Then Exit ConsoleWrite("Error creating application object" & @CRLF)

Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Unassigned Credit Card Transactions (MAIN REPORT).xlsx")
    If @error Then Exit ConsoleWrite("Error creating workbook object" & @CRLF)


Local $aWorkSheets = _Excel_SheetList($oWorkbook)
    If @error Then Exit ConsoleWrite("Error enumerating worksheets" & @CRLF)

For $i = 0 To UBound($aWorkSheets) - 1
    Sleep($slptime)
    Local $StrVal = "Employee Org Unit 1 - Name"
    Local $aResult = _Excel_RangeFind($oWorkbook, $StrVal)
    If @error Then Exit ConsoleWrite("Error Reading the workbook: " & @error)
    Local $vResult = $aResult[0][2]
    ;_arrayDisplay($aResult)
    ;If @error Then Exit ConsoleWrite("Error enumerating worksheets" & @error)
    MsgBox(0, "AutoIt - Concur", "Beginning The processing of file : " & $vResult)
Next
Exit

The code is above.

The change was at line 22.

[Jfish]

This works fine for me with a test file.  If you are getting that error on the workbook maybe your path is wrong.  Do you get any errors before that when you try to open the XLSX?  Does it open when you run the script?

Place a test file called "test.xlsx" in the same directory as the below test script.  Put the word "test" in one or more cells then run it.

#include <Array.au3>
#include <Excel.au3>

Local $oAppl = _Excel_Open()
   If @error Then Exit ConsoleWrite("Error creating application object" & @CRLF)

Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\test.xlsx")
    If @error Then Exit ConsoleWrite("Error creating workbook object" & @CRLF)

Local $aWorkSheets = _Excel_SheetList($oWorkbook)
    If @error Then Exit ConsoleWrite("Error enumerating worksheets" & @CRLF)

For $i = 0 To UBound($aWorkSheets) - 1
    Local $StrVal = "test"
    Local $aResult = _Excel_RangeFind($oWorkbook, $StrVal)
    If @error Then Exit ConsoleWrite("Error Reading the workbook: " & @error)
    Local $vResult = $aResult[0][2]
    _arrayDisplay($aResult)
    ;If @error Then Exit ConsoleWrite("Error enumerating worksheets" & @error)
    MsgBox(0, "AutoIt - Concur", "Beginning The processing of file : " & $vResult)
Next

 

[Happy82]

This was the code initially,

Local $aResult = _Excel_RangeFind($aWorkSheets, $StrVal)

This was not working, I was trying to work on the worksheet. when i changed it to the following it worked.

Local $aResult = _Excel_RangeFind($oWorkbook, $StrVal)

so when i use the workbook it working as that was object created while opening the xcel.

when i assigned a variable of workseeht from the workbook, it did not work.

In my post it might has caused the confusion. I was trying to understand why $oWorkBook was working, while $aWorkSheet was not working.

[Jfish]

Is it working now?

[Happy82]

yes.... it is working