Search in Excel file

[Ahmed101]

#include <Excel.au3>

$fileName = @ScriptDir & "\TestExcel.xlsx"
$Attach = _Excel_BookAttach($fileName)
$aResult = _Excel_RangeFind($Attach,"Ahmed")

the code above returns an error :

"C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
$aResult[$iIndex][1] = $oMatch.Name.Name
$aResult[$iIndex][1] = $oMatch^ ERROR

 

here's a pic from the excel file :
Pic

[Subz]

Works fine for me:

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

Local $sFilePath = @ScriptDir & "\TestExcel.xlsx"
Local $oExcel = _Excel_Open()
Local $oWorkBook = _Excel_BookOpen($oExcel, $sFilePath)
Local $oWorkBook = _Excel_BookAttach($sFilePath)
$aResult = _Excel_RangeFind($oWorkBook, "Ahmed")
_ArrayDisplay($aResult)

 

[Ahmed101]

I am still having the same error

[Subz]

Do you have the latest version of Autoit installed?

[Ahmed101]

Yea that was the problem after updating autoit it works now, thank you.

[Ahmed101]

Sorry if i bother you, but how can i return the cell in a msgbox since the msgbox returns nothing.

[Subz]

Remember its an Array thats returned so you need to use something like:

MsgBox(4096, "Excel Result", "Sheet = " & $aResult[0][0] & @CRLF & _
    "Cell Location = " & $aResult[0][2] & @CRLF & _
    "Cell Value = " & $aResult[0][3])

 

[Ahmed101]

is that possible to make the find function works in a specific sheet like in the _Excel_RangeRead and Write,

[FrancescoDiMuro]

@Subz

He didn't initialize the Excel object, as you can see in the first comment. 

Then he copy-pasted your code, and it worked.

31 minutes ago, Ahmed101 said:

is that possible to make the find function works in a specific sheet like in the _Excel_RangeRead and Write,

If I understood you, yes, it is possible.

Take a look here.

 

Best Regards.

[Juvigy]

Ahmed, the CELL is an object. You cant display an object in the msgbox. You have to display the object properties - for example $cell.Value or $cell.Text

[Subz]

@FrancescoDiMuro

When running my code on his machine it failed first time but worked after upgrading.

Ahmed, recommend reading the help file on _Excel_RangeFind, you'll note the $vRange parameter which allows you to set which sheet/range to search, if you use "Default" then it searches all worksheets.

[Ahmed101]

7 hours ago, Juvigy said:

Ahmed, the CELL is an object. You cant display an object in the msgbox. You have to display the object properties - for example $cell.Value or $cell.Text

Thanks for the explanation i should study more about excel and the cells to use them in the best way.
 

[Ahmed101]

7 hours ago, Subz said:

@FrancescoDiMuro

Ahmed, recommend reading the help file on _Excel_RangeFind, you'll note the $vRange parameter which allows you to set which sheet/range to search, if you use "Default" then it searches all worksheets.

I never used default in $vRange parameter, the workbook i have contains more than 1000 sheet and every sheet contains a lot of data.
In _Excel_RangeRead and _Excel_RangeWrite there is ($oWorkbook,$vWorksheet) parameters.
but i didn't actually know how can i attach the sheet.

[Subz]

So if you don't set the $vRange parameter it searches every worksheet, however you can use something like below to search "Sheet2" only or whatever the name is of your sheet and then it will only search that sheet.

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

Local $sFilePath = @ScriptDir & "\TestExcel.xlsx"
Local $oExcel = _Excel_Open()
Local $oWorkBook = _Excel_BookOpen($oExcel, $sFilePath)
Local $oWorkBook = _Excel_BookAttach($sFilePath)
$aResult = _Excel_RangeFind($oWorkBook, "Ahmed", $oWorkBook.Sheets("Sheet2").UsedRange)
_ArrayDisplay($aResult)
If IsArray($aResult) Then MsgBox(4096, "Excel Result", "Sheet = " & $aResult[0][0] & @CRLF & _
    "Cell Location = " & $aResult[0][2] & @CRLF & _
    "Cell Value = " & $aResult[0][3])

You could also search for a particular sheet and column, note in the code below I've specified to only search "sheet2" and only column "D"

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

Local $sFilePath = @ScriptDir & "\TestExcel.xlsx"
Local $oExcel = _Excel_Open()
Local $oWorkBook = _Excel_BookOpen($oExcel, $sFilePath)
Local $oWorkBook = _Excel_BookAttach($sFilePath)
$aResult = _Excel_RangeFind($oWorkBook, "Ahmed", $oWorkBook.Sheets("Sheet2").Range("D:D"))
_ArrayDisplay($aResult)
If IsArray($aResult) Then MsgBox(4096, "Excel Result", "Sheet = " & $aResult[0][0] & @CRLF & _
    "Cell Location = " & $aResult[0][2] & @CRLF & _
    "Cell Value = " & $aResult[0][3])

 

[Ahmed101]

Thanks a lot @Subz,
Last thing i wanted to check if the function failed to find the text,
I tried :

$aResult = _Excel_RangeFind($attach,"ASDASDHJ","B:B")
if @error or $aResult = 0 or $aResult = 1 or $aResult = 2 or $aResult = NULL then
MsgBox(0,"","Failed")
elseif isArray($aResult) then
MsgBox(0,"","Found")
else
MsgBox(0,"","Else statement")
endif

it always returns found, why ?

[Subz]

Not sure if it was intended this way or its a bug (@water will know) but it returns an empty array when no results are found and the @error is set to 0, which means you would have to use something like Ubound($aResult) and check if it equals 0 then no items were found, see example below.

Just out of curiosity which version of Office are you using, I'm using Microsoft Office Pro Plus 2016, I'm sure that @error worked on 2013/2007 or maybe my memory is a bit foggy.

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

Local $sFilePath = @ScriptDir & "\TestExcel.xlsx"
Local $oExcel = _Excel_Open()
Local $oWorkBook = _Excel_BookOpen($oExcel, $sFilePath)
Local $oWorkBook = _Excel_BookAttach($sFilePath)
$aResult = _Excel_RangeFind($oWorkBook, "Ahmed", $oWorkBook.Sheets("Sheet2").UsedRange)
If UBound($aResult) = 0 Then
    MsgBox(0, "Excel Result", "No results were found")
Else
    MsgBox(4096, "Excel Result", "Sheet = " & $aResult[0][0] & @CRLF & _
    "Cell Location = " & $aResult[0][2] & @CRLF & _
    "Cell Value = " & $aResult[0][3])
EndIf

 

[water]

IIRC it was intended. The rewritten UDF should be compatible with the old one.

@error is only set when there is a real error. Means: Missing or invalid parameters, error returned by an Excels COM method ...

The result of a successful search operation can be everything from 0 to n hits. The Excel method does not return an error when nothing was found, so that's why @error doesn't get set but an empty array gets returned by _Excel_RangeFind.
 

[Ahmed101]

@Subz bro, is it possible to search In all the workbook for a specified range ?
something like this :

_Excel_RangeFind($Workbook,$value,$workbook.Sheets(Default).Range("C:C"))

And if used the $vRange like this :

_Excel_RangeFind($workbook,$value,"C:C")

it just searches in the opened sheet not all the workbook.

[Subz]

#include <Array.au3>
#include <Excel.au3>
Local $aResults[0][6]
Local $sFilePath = @ScriptDir & "\TestExcel.xlsx"
Local $oExcel = _Excel_Open()
Local $oWorkBook = _Excel_BookOpen($oExcel, $sFilePath)
Local $oWorkBook = _Excel_BookAttach($sFilePath)
$iSheets = $oWorkBook.Sheets.Count
For $i = 1 To $iSheets
    $aResult = _Excel_RangeFind($oWorkBook, "Ahmed", $oWorkBook.Sheets($i).Range("C:C"))
    If UBound($aResult) > 0 Then _ArrayAdd($aResults, $aResult)
Next
_ArrayDisplay($aResults)

Just loop through the sheets

Edited July 8, 2018 by Subz
Removed MsgBox, no longer required

[Ahmed101]

The _ArrayAdd() function is the magic i wasn't able to add the new arrays to the old one.
Thanks bro,