Help with ExcelCOM_UDF

[tman]

Long Time Reader, First Time Posting..

Scite Version 1.77, AutoIT (latest version/Beta, Excel 2007, ExcelCOM_UDF version 1.4

Quick Summary:

I created a GUI to make configuring a router that is serially attached to a PC a little idiot proof. It has a few functions like resetting router to OEM defaults and configuring router based on specific data found in excel spreadsheet. I support about 3500 retail stores each with a FW/Router and I have a few jr. engineers who have to configure a new router for each store due to a massive tech refresh. One of the steps in the GUI is to enter a store number. My Excel spreadsheet has all the stores listed in column 1. I am trying to use _ExcelFindInRange but I always end up with a result of "0" or nothing (depending on how I write the script. The ultimate goal is to search for a store number in column 1, identify the row and read that row into an array so that data can replace some variables in a master config file.

I have gone through the ExcelCOM_UDF file and am pretty sure I understand what's required but I just can't seem to get the results I'm looking for. I'm far from an experienced programmer but never afraid to learn from my failures.....until now...

Here is the last attempt which results in a MSGBox with a result of '0'

==CODE==

#include <ExcelCOM_UDF.au3>; Include the collection

#include <Array.au3>

$file = "C:\Documents and Settings\tsbaiti\My Documents\Desktop\3GMaster.xls"

$oExcel = _ExcelBookOpen($File, 1, False, "", "");Open the Excel document

$sFindWhat = "2705290"

$sRangeOrRowStart = "A1"

$StrRow = _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, 1, 2115, 1, -4163, 1, False, "")

MsgBox(4096, "Test", $StrRow, "")

_ExcelBookClose($oExcel, 1, 0)

Kindly requesting any help or advice

[exodius]

If it were me, and if it were possible, I'd export the whole spreadsheet out to a csv (comma delimited) file and then read that into an array when you needed to do something using _FileReadToArray...

Excel's kind of buggy with referencing it in a "hidden" fashion because it reuses it's windows, unlike Word for example which uses a new window for each new document you open.

However, if you want to use Excel, I'm going to point out the Return section of the _ExcelFindInRange function from the ExcelCOM_UDF:

; Return Value(s):  On Success - Returns a two dimensional array with addresses of matching cells.  If no matches found, returns null string
;                       $array[0][0] - The number of found cells
;                       $array[x][0] - The address of found cell x in A1 format
;                       $array[x][1] - The address of found cell x in R1C1 format
;                       $array[x][2] - The row of found cell x as an integer
;                       $array[x][3] - The column of found cell x as an integer
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Starting row or column invalid
;                           @extended=0 - Starting row invalid
;                           @extended=1 - Starting column invalid
;                       @error=3 - Ending row or column invalid
;                           @extended=0 - Ending row invalid
;                           @extended=1 - Ending column invalid
;                       @error=4 - Data type parameter invalid
;                       @error=5 - Whole or part parameter invalid

See what you get with this code:

#include 'ExcelCOM_UDF.au3'; Include the collection
#include <Array.au3>

$file = "C:\Documents and Settings\tsbaiti\My Documents\Desktop\3GMaster.xls" 
$oExcel = _ExcelBookOpen($File);Open the Excel document

$sFindWhat = "2705290"
$sRangeOrRowStart = "A1"
$StrRow = _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart)
If Not @error Then
    MsgBox(4096, "Test", $StrRow[1][0], "")
Else
    MsgBox (0, "", @error)
EndIf

_ExcelBookClose($oExcel, 1, 0)

Bear in mind that the return (StrRow in this case) is going to be an array based on what's listed above. By the way, the error that was returning from your original code was 1 which, based on the error codes above, means that the _ExcelFindInRange function wasn't getting a valid object to work with (i.e. what you were typing in for _ExcelBookOpen wasn't working right).

Edited April 6, 2009 by exodius

[picaxe]

Try this to troubleshoot

$StrRow = _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, 1, 2115, 1, -4163, 1, False, "")
_ArrayDisplay($StrRow)

_ArrayDisplay requires include Array.au3

; $array[0][0] - The number of found cells

; $array[x][0] - The address of found cell x in A1 format

; $array[x][1] - The address of found cell x in R1C1 format

; $array[x][2] - The row of found cell x as an integer

; $array[x][3] - The column of found cell x as an integer

Edited April 6, 2009 by picaxe

[tman]

great big thanks to Exodius and Picaxe!

Based on the error code returned I adjusted the code and placed the xls file in the Working directory. The results were spot on. Truly appreciate the advice.

This is the code that ended up working for me just in case someone else has the same issue's I did.

=====CODE=====

#include 'ExcelCOM_UDF.au3'; Include the collection

#include <Array.au3>

$file = @WorkingDir & "\3GMaster.xls"

$oExcel = _ExcelBookOpen($File)

$sFindWhat = "2705290"

$sRangeOrRowStart = "A1"

$StrRow = _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart)

If Not @error Then

MsgBox(4096, "Test", $StrRow[1][0], "")

Else

MsgBox (0, "", @error)

EndIf

_ExcelBookClose($oExcel, 1, 0)