Sign in to follow this  
Followers 0
muncherw

_ExcelFindInRange returns null

7 posts in this topic

When I use _ExcelFindInRange it returns null. I must not be using this function correctly. What am I doing wrong?

CODE
#include <ExcelCOM_UDF.au3> ; Include the collection

$sFilePath = @WorkingDir & "\Cardcount.xls"

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

$sFindWhat = "Epic" ;This is what I'm searching for

$sRangeOrRowStart = "A1" ;This is the field I want the search to start at.

$whatever = _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, 1, 1, 1, 0, 2, False, "")

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

_ExcelBookClose($oExcel, 1, 0)


Other People's Stuff:Andy Flesner's AutoIt v3: Your Quick Guide[topic="34302"]Locodarwin's ExcelCom_UDF[/topic][topic="61090"]MrCreatorR's Opera Library[/topic]

Share this post


Link to post
Share on other sites



Any Excel users out there have a grasp on this? I can't get _ExcelReadArray to work either so clearly I'm doing something wrong.


Other People's Stuff:Andy Flesner's AutoIt v3: Your Quick Guide[topic="34302"]Locodarwin's ExcelCom_UDF[/topic][topic="61090"]MrCreatorR's Opera Library[/topic]

Share this post


Link to post
Share on other sites

Here are the notes on _ExcelFindInRange:

;===============================================================================

;

; Description: Finds all instances of a string in a range and returns their addresses as a two dimensional array.

; Syntax: _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _

; $iDataType = 0, $iWholeOrPart = 1, $iSearchOrder = 1, $iSearchDirection = 1, $fMatchCase = False, _

; $fMatchFormat = False)

; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()

; $sFindWhat - The string to search for

; $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1

; $iColStart - The starting column for the number format(left) (default=1)

; $iRowEnd - The ending row for the number format (bottom) (default=1)

; $iColEnd - The ending column for the number format (right) (default=1)

; $iDataType - Limit the search to a certain kind of data (0=all, $xlFormulas(-4123), $xlValues(-4163), or $xlNotes(-4144)) (default=0)

; $iWholeOrPart - Whole or part of cell must match search string (1=Whole, 2=Part) (default=2)

; $fMatchCase - Specify whether case should match in search (True or False) (default=False)

; $fMatchFormat - Specify whether cell formatting should match in search (True, False, or empty string) (default=empty string=do not use parameter)

; Requirement(s): AutoIt Beta 3.2.1.12

; 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

; Author(s): SEO <locodarwin at yahoo dot com> and MRDev, many thanks to DaLiMan

; Note(s): None

;

;===============================================================================

The function returns an array of data. You'll need to reference the proper piece of the array to get the data you are after. The following code is a modified version of your script; the MsgBox will show the number of cells found matching your query.

#include <ExcelCOM_UDF.au3>; Include the collection

$sFilePath = @WorkingDir & "\Cardcount.xls"
$oExcel = _ExcelBookOpen($sFilePath, 1, False, "", "");Open the Excel document

$sFindWhat = "Epic";This is what I'm searching for
$sRangeOrRowStart = "A1";This is the field I want the search to start at.
$whatever = _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, 1, 1, 1, 0, 2, False, "")
MsgBox(4096, "Test", $whatever[0][0], "")

_ExcelBookClose($oExcel, 1, 0)

Certifications: A+, Network+, Security+, Linux+, LPIC-1, MCSA | Languages: AutoIt, C, SQL, .NETBooks: AutoIt v3: Your Quick Guide - $7.99 - O'Reilly Media - September 2007-------->[u]AutoIt v3 Development - newbie to g33k[/u] - Coming Soon - Fate Publishing - Spring 2013UDF Libraries: SkypeCOM UDF Library | ADUC Computers OU Cleanup | Find PixelChecksumExamples: Skype COM Examples - Skype4COMLib Examples converted from VBS to AutoIt

Share this post


Link to post
Share on other sites

The function returns an array of data. You'll need to reference the proper piece of the array to get the data you are after. The following code is a modified version of your script; the MsgBox will show the number of cells found matching your query.

Ooooooh! *slaps head* Gotcha. I'm not very good with arrays but it definately helps if you pay enough attention to realize you have to reference the array. Thanks Airwolf. I'd appreciate if you'd keep an eye out for me because I'm sure that I'll have plenty more questions about this. Thanks a lot.

Just noticed we are neighbors. I just moved to St Charles less than three weeks ago.


Other People's Stuff:Andy Flesner's AutoIt v3: Your Quick Guide[topic="34302"]Locodarwin's ExcelCom_UDF[/topic][topic="61090"]MrCreatorR's Opera Library[/topic]

Share this post


Link to post
Share on other sites

Ooooooh! *slaps head* Gotcha. I'm not very good with arrays but it definately helps if you pay enough attention to realize you have to reference the array. Thanks Airwolf. I'd appreciate if you'd keep an eye out for me because I'm sure that I'll have plenty more questions about this. Thanks a lot.

Just noticed we are neighbors. I just moved to St Charles less than three weeks ago.

Welcome to the neighborhood! :)

Feel free to PM me if you need anything.


Certifications: A+, Network+, Security+, Linux+, LPIC-1, MCSA | Languages: AutoIt, C, SQL, .NETBooks: AutoIt v3: Your Quick Guide - $7.99 - O'Reilly Media - September 2007-------->[u]AutoIt v3 Development - newbie to g33k[/u] - Coming Soon - Fate Publishing - Spring 2013UDF Libraries: SkypeCOM UDF Library | ADUC Computers OU Cleanup | Find PixelChecksumExamples: Skype COM Examples - Skype4COMLib Examples converted from VBS to AutoIt

Share this post


Link to post
Share on other sites

And incidentally I did read the UDF (many times). So I'm saying I wasn't being lazy, however that does mean I admit to being stupid.


Other People's Stuff:Andy Flesner's AutoIt v3: Your Quick Guide[topic="34302"]Locodarwin's ExcelCom_UDF[/topic][topic="61090"]MrCreatorR's Opera Library[/topic]

Share this post


Link to post
Share on other sites

And incidentally I did read the UDF (many times). So I'm saying I wasn't being lazy, however that does mean I admit to being stupid.

Don't be so hard on yourself. Everybody overlooks a detail now and again. You're not the only one who has stared at code for a while and then asked for help, only to end up saying, "Duh!" :)

Certifications: A+, Network+, Security+, Linux+, LPIC-1, MCSA | Languages: AutoIt, C, SQL, .NETBooks: AutoIt v3: Your Quick Guide - $7.99 - O'Reilly Media - September 2007-------->[u]AutoIt v3 Development - newbie to g33k[/u] - Coming Soon - Fate Publishing - Spring 2013UDF Libraries: SkypeCOM UDF Library | ADUC Computers OU Cleanup | Find PixelChecksumExamples: Skype COM Examples - Skype4COMLib Examples converted from VBS to AutoIt

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  
Followers 0