Jump to content
Ahmed101

Search in Excel file

Recommended Posts

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

Share this post


Link to post
Share on other sites
Ahmed101

I am still having the same error

Share this post


Link to post
Share on other sites
Subz

Do you have the latest version of Autoit installed?

  • Like 1

Share this post


Link to post
Share on other sites
Ahmed101

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

Share this post


Link to post
Share on other sites
Ahmed101

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

Share this post


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

 

  • Like 1

Share this post


Link to post
Share on other sites
Ahmed101

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

Share this post


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


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


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

  • Like 1

Share this post


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

  • Like 1

Share this post


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

Share this post


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

Share this post


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

 

  • Like 1

Share this post


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

Share this post


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

 

  • Like 1

Share this post


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

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites
Subz
Posted (edited)
#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 by Subz
Removed MsgBox, no longer required
  • Like 1

Share this post


Link to post
Share on other sites
Ahmed101

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

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

×