Search a large excel file

[Andrew]

I will preface, as always, that I am not by any stretch a programmer, but I like to find new ways to use AutoIT to make my job easier. So please go easy on me.

I've been searching the forum for a quick way to search a large excel file (15950 rows, 15 columns). Using some code I found earlier, I was able to read the contents to an array but that in itself took 5 mins. I then found the following topic (http://www.autoitscript.com/forum/index.php?showtopic=106095&st=0&p=749335&hl=search%20excel&fromsearch=1&#entry749335) which introduced me to some vb (I think) that is much much faster. But since I don't know vb, I haven't found a way to loop thru all instances of the keyword I'm looking for in the file.

So to make a long story short, any suggestions how to quickly search a large excel file for all instances of a keyword or phrase?

Pointers are, as always, welcome.

Thanks!

[notsure]

I'm having the same problems, did not find any way to make it faster so i ended up in using the "search" function in excel itself.

[Juvigy]

This is how you open excel file as a database - it should be quite fast.

Will have to modify the code to suit your needs

expandcollapse popup

#Include <Array.au3>
Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001
Global $s_Filename=FileGetShortName("C:\My Documents\somefile.xls") 
Global $s_Tablename = "[January$]"
Global $test [40][7]
; Initialize COM error handler 
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

; Source XLS data
$objConnection = ObjCreate("ADODB.Connection")
$objRecordSet = ObjCreate("ADODB.Recordset")
$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source="&$s_Filename&";" & _
                "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"";")
$objRecordSet.Open ("Select * FROM"& $s_Tablename & "Order by 1 Asc" , $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText) 
$i=0
Do 
    ;ConsoleWrite ($objRecordSet.Fields(2).value &@CR)
    ;MsgBox(0,$objRecordSet.Fields(1).value,$objRecordSet.Fields(2).value)
    $test [$i][0]=$objRecordSet.Fields(0).value
    $test [$i][1]=$objRecordSet.Fields(1).value
    $test [$i][2]=$objRecordSet.Fields(2).value
    $test [$i][3]=$objRecordSet.Fields(3).value
    $test [$i][4]=$objRecordSet.Fields(4).value
    $test [$i][5]=$objRecordSet.Fields(5).value 
    $test [$i][6]=$objRecordSet.Fields(6).value
    $i=$i+1
    $objRecordSet.MoveNext()
Until $objRecordSet.EOF()
_ArrayDisplay($test)
$objConnection.Close
$objConnection = ""
$objRecordSet = ""

Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

[Spiff59]

See if this works for you...

expandcollapse popup

#include <Array.au3>

$aResult = _ExcelFindCells(@ScriptDir & "\test.xls", "34534")
_ArrayDisplay($aResult)
Exit

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelFindCells
; Description ...: Opens an existing workbook and returns an array of cells whose content matches the search string.
; Syntax.........: _ExcelFindCells($sFilePath, $sSearch)
; Parameters ....: $sFilePath - Path and filename of the file to be opened
;                  $sSearch   - search string
; Return values .: Success      - Returns a 1-based array containing cell addresses that matched the search string
;                  Failure      - Returns nothing
;                  |@error=1    - Unable to create the Excel object
;                  |@error=2    - File does not exist
; Author ........: Spiff59
; ===============================================================================================================================
Func _ExcelFindCells($sFilePath, $sSearch)
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    Local $oExcel = ObjCreate("Excel.Application"), $sReturn
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    $oExcel.Visible = 0
    $oExcel.WorkBooks.Open($sFilePath)
    $oMatch = $oExcel.Cells.Find($sSearch)
    If Isobj($oMatch) Then
        $sFirst = $oMatch.Address
        While IsObj($oMatch)
            $sReturn &= StringReplace($oMatch.Address, "$", "") & "|"
            $oMatch = $oExcel.Cells.Findnext($oMatch)
            If $oMatch.Address = $sFirst then ExitLoop
        WEnd
    EndIf
    $oExcel.Quit
    $oExcel = ""
    If $sReturn Then Return StringSplit(StringTrimRight($sReturn, 1), "|")
EndFunc

edit2: I formalized it a bit... possible addition to Excel UDF?

Edited June 30, 2010 by Spiff59

[Andrew]

See if this works for you...

Thanks, Spiff59. Your code works but I need to take it a step farther...

The first step is finding each instance of the keyword or phrase (which works, thanks!)

The second step is to collect the contents of adjacent cells on the same row which will, itself, be written to another excel spreadsheet or file.

It's probably not all that difficult (the code I referred to earlier used offsets) but I am struggling a bit trying to decipher your code (this is where I wish I had better programming skills).

Thanks!

[Spiff59]

My gripe with the Excel UDF (and why I rarely use any of it) is that it's not efficient at processing a large batch of files in one script. The Open and Attach functions cause you to be constantly starting and stopping the Excel application. If you want to insert a new column into 10,000 existing workbooks, then 99% of the execution time would be spent launching and terminating excel.exe.

The Excel UDF functions ought to work more like this "version 2" of _ExcelFindCells(). Where the function can be passed an already open workbook (as an object), repeatedly, and it executes very quickly. Or, optionally, it can be passed an unopen workbook (as a filename) and will start excel and open the workbook internally.

expandcollapse popup

#include <Array.au3>
$Workbook = @ScriptDir & "\test.xls"

;===============================================================================
; example calling function when workbook is not open, passing workbook as a pathname/filename
$aResult = _ExcelFindCells($Workbook, "2010", "A1:A9999")
_ArrayDisplay($aResult, "pathname/filename")

;===============================================================================
; example calling function (repeatedly) when workbook is already open, passing workbook as an object
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
$oExcel.WorkBooks.Open($Workbook)

$aResult = _ExcelFindCells($oExcel, "The", "*", False, True) ; case sensitive
_ArrayDisplay($aResult, "object")
$aResult = _ExcelFindCells($oExcel, "2010", "*", True, False, True) ; exact match, includes values in array
_ArrayDisplay($aResult, "object")
;$aResult = _ExcelFindCells($oExcel, "ab") ; partial match
;_ArrayDisplay($aResult, "object")

$oExcel.Quit
$oExcel = ""
Exit


; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelFindCells
; Description ...: Returns an array of workbook cell addresses [and values] whose content matches the search string.
; Syntax.........: _ExcelFindCells($sFilePath, $sSearch [, $sRange] [, $sFull] [, $sCase])
; Parameters ....: $sFilePath - Object to an open workbook, or, full path/filename of an unopened workbook
;                  $sSearch   - Search string
;                  $sRange    - Range of cells to search (Default = "*". Format = "A1:D99")
;                  $sFull     - False = allow partial match (default), True = entire cell contents must match exactly
;                  $sCase     - False = case insensitive (default), True = case sensitive
;                  $sValue    - Return a 2-dimension array with values of matching cells in second element (Default = False)
; Return values .: Success    - Returns a 1-based array containing cell addresses [and values] that match the search string
;                  @error=1   - Unable to create the Excel object
;                  @error=2   - File does not exist
; Author ........: Spiff59
; ===============================================================================================================================
Func _ExcelFindCells($sFilePath, $sSearch, $sRange = "*", $sFull = False, $sCase = False, $sValueFlag = False)
    If IsObj($sFilePath) Then
        Local $oExcel = $sFilePath
    Else
        If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
        Local $oExcel = ObjCreate("Excel.Application")
        If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
        $oExcel.Visible = 0
        $oExcel.WorkBooks.Open($sFilePath)
    EndIf
    Local $sAddress, $sValue
    If $sFull <> True Then  $sFull = False
    If $sCase <> True Then  $sCase = False
    If $sValueFlag <> True Then $sValueFlag = False
    If $sRange = "*" Then
;       $oRange = $oExcel.ActiveSheet.UsedRange
        $oRange = $oExcel.Cells
    Else
        $oRange = $oExcel.Range($sRange)
    EndIf
    $oMatch = $oRange.Find($sSearch, Default, Default, $sFull, Default, Default, $sCase)
    If Isobj($oMatch) Then
        $oFirst = $oMatch.Address
        While IsObj($oMatch)
            $sAddress &= StringReplace($oMatch.Address, "$", "") & "|"
            If $sValueFlag Then $sValue &= $oMatch.Value & "|"
            $oMatch = $oRange.Findnext($oMatch)
            If $oMatch.Address = $oFirst then ExitLoop
        WEnd
        $oFirst = ""
    EndIf
    $oMatch = ""
    $oRange = ""
    If Not IsObj($sFilePath) Then $oExcel.Quit
    $oExcel = ""
    If $sAddress Then
        $sAddress = StringSplit(StringTrimRight($sAddress, 1), "|")
        If $sValueFlag Then
            $sValue = StringSplit(StringTrimRight($sValue, 1), "|")
            Local $aTemp[$sAddress[0] + 1][2]
            $aTemp[0][0] = $sAddress[0]
            For $x = 1 to $aTemp[0][0]
                $aTemp[$x][0] = $sAddress[$x]
                $aTemp[$x][1] = $sValue[$x]
            Next
            Return $aTemp
        EndIf
    Else ; no matches, return array with 0 count
        If $sValueFlag Then
            Local $sAddress[1][2] = [[0,""]]
        Else
            Local $sAddress[1] = [0]
        EndIf
    EndIf
    Return $sAddress
    EndFunc

Edit: Added range, full/partial match, case-sensitivity, and value parameters.

Edited July 2, 2010 by Spiff59

[Tvern]

Not sure if you can still use it, but I use the following to quickly load a worksheet into an array:

Scrap that, the example from Juvigy is much cleaner and should give the same results.

[autoit]Func _DBPageRead($oExcel, $PageNameOrIndex = "", $bTranspose = True)

Local $aArray, $sLastCell

If $PageNameOrIndex Then $oExcel.ActiveWorkbook.Sheets($PageNameOrIndex).Select()

$sLastCell = $oExcel.Application.Selection.SpecialCells(11).Address(True, True, True) ;11 is the code for $xlCellTypeLastCell

$aArray = $oExcel.Activesheet.Range("A1:" & $sLastCell).Value

If $bTranspose Then

Local $TransArray[uBound($aArray, 2)][uBound($aArray)]

For $iRow = 0 To UBound($aArray) - 1

For $iColl = 0 To UBound($aArray, 2) - 1

$TransArray[$iColl][$iRow] = $aArray[$iRow][$iColl]

Next

Next

Return $TransArray

Else

Return $bTranspose

EndIf

EndFunc[/autoit

Edited July 1, 2010 by Tvern

[Juvigy]

You can even try something like :

#include <Array.au3>
        $oExcel = ObjCreate("Excel.Application")
        $oExcel.Visible = 1
        $oExcel.WorkBooks.Open ("C:\Somefile.xls")
        $aArray = $oExcel.Activesheet.UsedRange.Value
        $aArray2=$oExcel.transpose($aArray)
        If IsArray($aArray) Then _ArrayDisplay($aArray, "$aArray")
        _ArrayDisplay($aArray2, "$aArray")

It should be quite fast.

[Andrew]

$aArray2=$oExcel.transpose($aArray)

I get the following:

C:\Somefile.xls(6) : ==> The requested action with this object has failed.:

$aArray2=$oExcel.transpose($aArray)

$aArray2=$oExcel.transpose($aArray)^ ERROR

[Juvigy]

What version of excel do you have ? It works for me on 2007 and XP SP2

Try:

$aArray2=$oExcel.Application.Transpose($aArray)

or even remove the lines:

$aArray2=$oExcel.transpose($aArray)
_ArrayDisplay($aArray2, "$aArray")

They just reverse the dimensions of the array - from [x][y] to [y][x]

[Andrew]

That's what I'm running as well, but it doesn't really matter because I don't think it addresses the matter I have at hand...

Spiff59's code searches the excel file and spits out the cell locations of the keyword or phrase searched (thank you!). But I really need to take it a step further -- I need to also collect row cell data for each instance found.

To give some insight: The keyword searched is a model number, but I need to collect the model number description (one cell to the left), version number (one cell to the right), and release date (4 cells beyond that) for each instance found. That's just a sample... again, there are 15 total columns.

Any suggestions how I can go about this?

Thanks!

[Tvern]

I'd read the excel sheet to an array using juvigy's method, perhaps using the transpose from my script.

Then create an empty array with the same size as the one containing your data.

Then you can use a For...To...Next loop to loop through the data. Whenever you have a match you can add each cell on that row to the new array. Use a variable to count the amount of matches found, so you know where to insert the next row.

When you are done searching the array you redim the array to the total amount of matches and that would be it I think.

Edited July 1, 2010 by Tvern

[jchd]

Please pardon a sideways remark but you apparently use Excel as a database in this application for storing factual data about "things". It sound to me it would be more logical (and efficient) to use a small but powerful database for doing so, namely SQLite which integrates nicely within AutoIt.

[Andrew]

Please pardon a sideways remark but you apparently use Excel as a database in this application for storing factual data about "things". It sound to me it would be more logical (and efficient) to use a small but powerful database for doing so, namely SQLite which integrates nicely within AutoIt.

Unfortunately, I have no control over the data source. That is why I am trying to use AutoIT to do my job better and more efficiently.

I am away all next week so I will follow-up from there. Thank you all for your suggestions / comments. I may be back for more

[jchd]

Juvigny's solution and the right SQL statements should be all what you need. Simple, stable, efficient, reliable as far as an MS thingy.

[Spiff59]

That's what I'm running as well, but it doesn't really matter because I don't think it addresses the matter I have at hand...

Spiff59's code searches the excel file and spits out the cell locations of the keyword or phrase searched (thank you!). But I really need to take it a step further -- I need to also collect row cell data for each instance found.

To give some insight: The keyword searched is a model number, but I need to collect the model number description (one cell to the left), version number (one cell to the right), and release date (4 cells beyond that) for each instance found. That's just a sample... again, there are 15 total columns.

Any suggestions how I can go about this?

Thanks!

I've been playing with the function in post #6 a little (turning a mouse into an elephant). Using the recently added $sValue parameter and changing one line, you could pretty easily get back what you want.

You could change :

If $sValueFlag Then $sValue &= $oMatch.Value & "|"

to something like:

$sValue &= $oMatch.Value & "^"
$sValue &= $oMatch.Offset(0, -1).Value & "^"
$sValue &= $oMatch.Offset(0, 1).Value & "^"
$sValue &= $oMatch.Offset(0, 5).Value & "|"

That would put the matching cell, one cell to the left, one cell to the right and 5 cells to the right into the returned array delimited by "^".

A StringSplit() could easily break those up.

Edit: Of course you could also modify the way the returned array is built and make it hold the number of elements you want, thus dispensing with the "^" delimiters and the stringsplit.

Edit: Version 3!

You may already have a bunch coded where you've pulled the entire spreadsheet into your script, or may be halfway to using SQLite, but... I have too much time on my hands this week and this version of _ExcelReadCell will return an array of matches that include any number of user specified cell values in relation to the matched cell. The uncommented example returns the column to the left of the match, the matched column itself, and the column to the right of the match. Seems to work ok.

expandcollapse popup

#include <Array.au3>
$Workbook = @ScriptDir & "\test.xls"

;===============================================================================
; example calling function when workbook is not open, passing workbook as a pathname/filename
$aResult = _ExcelFindCells($Workbook, "The ", "B1:B65000", False, True, "0,-1|0,0|0,1") ; partial match, case sensitive
_ArrayDisplay($aResult, "object")

;===============================================================================
; example calling function (repeatedly) when workbook is already open, passing workbook as an object
;$oExcel = ObjCreate("Excel.Application")
;$oExcel.Visible = 0
;$oExcel.WorkBooks.Open($Workbook)

;$aResult = _ExcelFindCells($Workbook, "2010", "A1:A9999")
;_ArrayDisplay($aResult, "pathname/filename")
;$aResult = _ExcelFindCells($oExcel, "2010", "*", False, False) ; exact match, includes values in array
;_ArrayDisplay($aResult, "object")
;$aResult = _ExcelFindCells($oExcel, "ab") ; partial match
;_ArrayDisplay($aResult, "object")

;$oExcel.Quit
;$oExcel = ""
Exit


; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelFindCells
; Description ...: Returns an array of workbook cell addresses and values whose content match the search string.
; Syntax.........: _ExcelFindCells($sFilePath, $sSearch [, $sRange] [, $bFull] [, $bCase] [,$sValue])
; Parameters ....: $sFilePath - Object to an open workbook, or, full path/filename of an unopened workbook
;                  $sSearch   - Search string
;                  $sRange    - Range of cells to search (Default = "*". Format = "A1:D99")
;                  $bFull     - False = allow partial match (default), True = entire cell contents must match exactly
;                  $bCase     - False = case insensitive (default), True = case sensitive
;                  $sValue    - Return a 2-dimension array with a variable number of elements containing cell values in proximity to each matching cell
;                               Specify values to return as offsets in relation to the matched cell. Format "row,col[|row,col]".
;                               Example: "0,0|0,1" would return the matched cell value in element 2, and the value of the cell
;                               to the right of the matched cell in element 3. Default = "0,0" (return matched cell value only)
; Return values .: Success    - Returns a 2-dimension 1-based array containing cell addresses [and values] that match the search string, the number of elemenets is variable depending on how many cell values are requested ($sValue)
;                  @error=1   - Unable to create the Excel object
;                  @error=2   - File does not exist
; Author ........: Spiff59
; ===============================================================================================================================
Func _ExcelFindCells($sFilePath, $sSearch, $sRange = "*", $bFull = False, $bCase = False, $sValue= "")
    If IsObj($sFilePath) Then
        Local $oExcel = $sFilePath
    Else
        If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
        Local $oExcel = ObjCreate("Excel.Application")
        If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
        $oExcel.Visible = 0
        $oExcel.WorkBooks.Open($sFilePath)
    EndIf
    Local $sAddress, $aTemp
    If $bFull <> True Then  $bFull = False
    If $bCase <> True Then  $bCase = False
    If $sValue Then
        $aValue = StringSplit($sValue, "|")
    Else
        Local $aValue[2] = [1, "0,0"]
    EndIf
    $sValue = ""
    If $sRange = "*" Then
;       $oRange = $oExcel.ActiveSheet.UsedRange
        $oRange = $oExcel.Cells
    Else
        $oRange = $oExcel.Range($sRange)
    EndIf
    $oMatch = $oRange.Find($sSearch, Default, Default, $bFull, Default, Default, $bCase)
    If Isobj($oMatch) Then
        $oFirst = $oMatch.Address
        While IsObj($oMatch)
            $sAddress &= StringReplace($oMatch.Address, "$", "") & "|"
            If $aValue[0] > 1 Then
                For $x = 1 to $aValue[0] - 1
                $aTemp = StringSplit($aValue[$x], ",")
                $sValue &= $oMatch.Offset($aTemp[1], $aTemp[2]).Value & "|"
                Next
            EndIf
            $aTemp = StringSplit($aValue[$aValue[0]], ",")
            $sValue &= $oMatch.Offset($aTemp[1], $aTemp[2]).Value & "||"
            $oMatch = $oRange.Findnext($oMatch)
            If $oMatch.Address = $oFirst then ExitLoop
        WEnd
        $oFirst = ""
    EndIf

    $oMatch = ""
    $oRange = ""
    If Not IsObj($sFilePath) Then $oExcel.Quit
    $oExcel = ""

    If Not $sAddress Then  ; no matches, return array with 0 count
        Local $sReturn[1][$aValue[0] + 1]
        $sReturn[0][0] = 0
        Return $sReturn
    EndIf

    $sAddress = StringSplit(StringTrimRight($sAddress, 1), "|")
    $sValue = StringSplit(StringTrimRight($sValue, 2), "||", 1)
    Local $sReturn[$sAddress[0] + 1][$aValue[0] + 1]
    $sReturn[0][0] = $sAddress[0]
    For $x = 1 to $sReturn[0][0]
        $sReturn[$x][0] = $sAddress[$x]
        $aTemp = StringSplit($sValue[$x], "|")
        For $y = 1 to $aTemp[0]
            $sReturn[$x][$y] = $aTemp[$y]
        Next
    Next
    Return $sReturn
    EndFunc

Edited July 2, 2010 by Spiff59

[Andrew]

Edit: Version 3!

You may already have a bunch coded where you've pulled the entire spreadsheet into your script, or may be halfway to using SQLite, but... I have too much time on my hands this week and this version of _ExcelReadCell will return an array of matches that include any number of user specified cell values in relation to the matched cell. The uncommented example returns the column to the left of the match, the matched column itself, and the column to the right of the match. Seems to work ok.

Hi Spiff59,

Actually, I've been out of town so I haven't had a chance to get back to this until today. Your function does seem to work except when I try to go 6 elements to the right I get the following message (after the script runs):

: ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

If I go 5 elements to the right it works fine.

$aResult = _ExcelFindCells($Workbook, "B3701AA", "c1:c65000", False, True, "0,-1|0,0|0,5")
_ArrayDisplay($aResult, "object")

Works

$aResult = _ExcelFindCells($Workbook, "B3701AA", "c1:c65000", False, True, "0,-1|0,0|0,6")
_ArrayDisplay($aResult, "object")

Does NOT Work

Ideas?

[Spiff59]

If I go 5 elements to the right it works fine.

$aResult = _ExcelFindCells($Workbook, "B3701AA", "c1:c65000", False, True, "0,-1|0,0|0,5")
_ArrayDisplay($aResult, "object")

Works

$aResult = _ExcelFindCells($Workbook, "B3701AA", "c1:c65000", False, True, "0,-1|0,0|0,6")
_ArrayDisplay($aResult, "object")

Does NOT Work

Ideas?

I ran into the same issue, but didn't bother writing in the error handling to deal with it. It occured for me when I tried to select a cell that does not exist. One that is either prior to the first cell in the row, or beyond the last populated cell in the row. Edited July 12, 2010 by Spiff59

[Andrew]

Scratch that seems to be working now (strange)

Thanks!

[Spiff59]

I do run into problems with it, and I did before.

As it was, you had to be careful to only reference populated cells.

I've changed a couple lines now to handle requests to read unused cells.

expandcollapse popup

#include <Array.au3>
$Workbook = @ScriptDir & "\test.xls"
 
;===============================================================================
; example calling function when workbook is not open, passing workbook as a pathname/filename
$aResult = _ExcelFindCells($Workbook, "The ", "B1:B65000", False, False, "0,-1") ; search col B, return value from col A
_ArrayDisplay($aResult, "search using filename")
 
;===============================================================================
; example calling function (repeatedly) when workbook is already open, passing workbook as an object
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
$oExcel.WorkBooks.Open($Workbook)
 
$aResult = _ExcelFindCells($oExcel, 188, "*", True, False) ; exact match
_ArrayDisplay($aResult, "search using object")
$aResult = _ExcelFindCells($oExcel, "ab") ; partial match
_ArrayDisplay($aResult, "search using object")
 
$oExcel.Quit
$oExcel = ""
Exit
 
 
; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelFindCells
; Description ...: Returns an array containing the addresses of cells matching the search string, and, the values of any cells
;                  in proximity to each matched cell as specified by the $sValue parameter.
; Syntax.........: _ExcelFindCells($sFilePath, $sSearch [, $sRange] [, $bFull] [, $bCase] [,$sValue])
; Parameters ....: $sFilePath - Object to an open workbook, or, full path/filename of an unopened workbook
;                 $sSearch   - Search string
;                 $sRange   - Range of cells to search. Format = "A1:D99". Default = "*"
;                 $bFull     - False = allow partial match (default), True = entire cell contents must match exactly
;                 $bCase     - False = case insensitive (default), True = case sensitive
;                 $sValue   - Cell(s) from which to return cell values. Specified as "row,col" offsets from the matching cell,
;                               delimited by "|". Default = "0,0" (return matched cell value only)
;                              Example: "0,0|0,1" will return the values of the matched cell and one cell to the right.
; Return values .: Success  - Returns a 2-dimension, 1-based array containing matching cell addresses in column 0, and
;                               a variable number of cell values beginning in column 1 (per $sValue)
;                 @error=1   - Unable to start Excel application
;                 @error=2   - $sFilePath does not exist
; Author ........: Spiff59
; ===============================================================================================================================
Func _ExcelFindCells($sFilePath, $sSearch, $sRange = "*", $bFull = False, $bCase = False, $sValue= "")
    If IsObj($sFilePath) Then
        Local $oExcel = $sFilePath
    Else
        If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
        Local $oExcel = ObjCreate("Excel.Application")
        If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
        $oExcel.Visible = 0
        $oExcel.WorkBooks.Open($sFilePath)
    EndIf
    Local $sAddress, $aTemp
    If $bFull <> True Then  $bFull = False
    If $bCase <> True Then  $bCase = False
    If $sValue Then
        $aValue = StringSplit($sValue, "|")
    Else
        Local $aValue[2] = [1, "0,0"]
    EndIf
    $sValue = ""
    If $sRange = "*" Then
;      $oRange = $oExcel.ActiveSheet.UsedRange
        $oRange = $oExcel.Cells
    Else
        $oRange = $oExcel.Range($sRange)
    EndIf
    $oMatch = $oRange.Find($sSearch, Default, Default, $bFull, Default, Default, $bCase)
    If Isobj($oMatch) Then
        $oFirst = $oMatch.Address
        While IsObj($oMatch)
            $sAddress &= StringReplace($oMatch.Address, "$", "") & "^^"
            If $aValue[0] > 1 Then
                For $x = 1 to $aValue[0] - 1
                $aTemp = StringSplit($aValue[$x], ",")
                $sValue &= $oMatch.Offset($aTemp[1], $aTemp[2]).Value & "||"
                Next
            EndIf
            $aTemp = StringSplit($aValue[$aValue[0]], ",")
            $sValue &= $oMatch.Offset($aTemp[1], $aTemp[2]).Value & "^^"
            $oMatch = $oRange.Findnext($oMatch)
            If $oMatch.Address = $oFirst then ExitLoop
        WEnd
        $oFirst = ""
    EndIf
 
    $oMatch = ""
    $oRange = ""
    If Not IsObj($sFilePath) Then $oExcel.Quit
    $oExcel = ""
 
    If Not $sAddress Then  ; no matches, return array with 0 count
        Local $sReturn[1][$aValue[0] + 1]
        $sReturn[0][0] = 0
        Return $sReturn
    EndIf
 
    $sAddress = StringSplit(StringTrimRight($sAddress, 2), "^^", 1) ; array of matching cell addresses
    $sValue = StringSplit(StringTrimRight($sValue, 2), "^^", 1) ; array of requested cell values
    Local $sReturn[$sAddress[0] + 1][$aValue[0] + 1]
    $sReturn[0][0] = $sAddress[0]
    For $x = 1 to $sReturn[0][0]
        $sReturn[$x][0] = $sAddress[$x] ; load matching cell address into element 0
        $aTemp = StringSplit($sValue[$x], "||", 1)
        For $y = 1 to $aTemp[0]
            $sReturn[$x][$y] = $aTemp[$y] ; load requested cell values into remaining elements
        Next
    Next
    Return $sReturn
    EndFunc

Edit: It can cause an Excel COM error if you use a negative offset that results in referencing a column prior to A. A COM error handler could trap that, or possibly an internal edit. Or, just don't reference invalid columns to the left of A

1-year Edit: clarified the function header/documentation.

Edited September 21, 2011 by Spiff59