Jump to content
Sign in to follow this  
Happy82

_Excel_RangeFind

Recommended Posts

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.

Share this post


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

Share this post


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

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


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

Share this post


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

 


Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


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

Share this post


Link to post
Share on other sites
Happy82

yes.... it is working

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  

×