Jump to content

Search in Excel file


Recommended Posts

#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

Link to comment
Share on other sites

@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

ALWAYS GOOD TO READ:

 

Link to comment
Share on other sites

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.
 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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])

 

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.
 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

#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
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...