Jump to content

Recommended Posts

Posted
#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

Posted

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

 

Posted

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

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

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

Posted

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

 

Posted

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

 

Posted

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 2024-07-28 - Version 1.6.3.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 (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

 

Posted

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

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

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
  • Recently Browsing   0 members

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