Jump to content

Searching particular values with Excel Array


Sam137
 Share

Recommended Posts

I have an Excel array, and i want to serach whether a particular value is present in the excel arry. If is present, i need to read the cell value which is on the right side namely the Report Type and the RAD Name. Please see the attached screen shot

#include <Excel.au3>
#include <Array.au3>
$sFilePath = @ScriptDir & "\Test.xlsx"
_ExcelBookOpen($sFilePath, 0)
$oExcel = _ExcelBookAttach("Test.xls", "FileName")
$aArray = _ExcelReadArray($oExcel,  1, 1, 5, 1)

$iIndex = _ArraySearch($aArray, "13a-- (3M) CDIS LOS Variance")
$Value1 = _ExcelReadCell($oExcel, $iIndex + 1, 2)
$Value2 = _ExcelReadCell($oExcel, $iIndex + 1, 3)
$Value3 = _ExcelReadCell($oExcel, $iIndex + 1, 4)
Msgbox (0, "Value1", $Value1)
Msgbox (0, "Value2", $Value2)
Msgbox (0, "Value3", $Value3)
_ExcelBookClose($oExcel, 0)
Link to comment
Share on other sites

Or you could give the UDF a try. Function _ExcelFindInRange should do what you want.

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

Taken from the UDF source:

; Description:    Finds all instances of a string in a range and returns their addresses as a two dimensional array.
; Syntax:          _ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
;                   $iDataType = 0, $iWholeOrPart = 1, $iSearchOrder = 1, $iSearchDirection = 1, $fMatchCase = False, _
;                   $fMatchFormat = False)
; Parameter(s):  $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sFindWhat - The string to search for
;                   $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;                   $iColStart - The starting column for the number format(left) (default=1)
;                   $iRowEnd - The ending row for the number format (bottom) (default=1)
;                   $iColEnd - The ending column for the number format (right) (default=1)
;                   $iDataType - Limit the search to a certain kind of data (0=all, $xlFormulas(-4123), $xlValues(-4163), or $xlNotes(-4144)) (default=0)
;                   $iWholeOrPart - Whole or part of cell must match search string (1=Whole, 2=Part) (default=2)
;                   $fMatchCase - Specify whether case should match in search (True or False) (default=False)

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

The ExcelCOM UDF doesn't come with the AutoIt installation. You have to download it from the thread I posted above and save it in AutoIt's Include directory or the directory where your script resides.

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

I have an Excel array, and i want to serach whether a particular value is present in the excel arry. If is present, i need to read the cell value which is on the right side namely the Report Type and the RAD Name. Please see the attached screen shot

#include <Excel.au3>
#include <Array.au3>
$sFilePath = @ScriptDir & "\Test.xlsx"
_ExcelBookOpen($sFilePath, 0)
$oExcel = _ExcelBookAttach("Test.xls", "FileName")
$aArray = _ExcelReadArray($oExcel,  1, 1, 5, 1)
 
$iIndex = _ArraySearch($aArray, "13a-- (3M) CDIS LOS Variance")
$Value1 = _ExcelReadCell($oExcel, $iIndex + 1, 2)
$Value2 = _ExcelReadCell($oExcel, $iIndex + 1, 3)
$Value3 = _ExcelReadCell($oExcel, $iIndex + 1, 4)
Msgbox (0, "Value1", $Value1)
Msgbox (0, "Value2", $Value2)
Msgbox (0, "Value3", $Value3)
_ExcelBookClose($oExcel, 0)

It is working for me!

Just comment this part out!

;$oExcel = _ExcelBookAttach("Test.xls", "FileName"); <<<<< ==== ??????? .xls <> .xlsx

Link to comment
Share on other sites

can you post the code please?

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

Its not working for me as you said.

$iIndex value returns -1, which means error(no match)....

All the other messages boxes returns 0....

Please advise...

If you look at this part of your script:

$aArray = _ExcelReadArray($oExcel, 1, 1, 5, 1)

the 5 = look at the first 5 lines of the spreadsheet. But in your picture in post #2 the answer is in line 7!

You don't have to limit how many lines it needs to search in.

Link to comment
Share on other sites

There's a routine in this July 2010 post from this thread:

It returns an array of all matching cell locations and values, and optionally the location/value of any number of cells in relation to the matched cell(s). It can be called with a full pathname in which it starts Excel and opens the workbook, but if being called repeatedly, you can also pass it an object to an already open workbook. It can be considerably faster than the Excel UDF when used in "object mode", as most the Excel UDF routines tend to start and stop the Excel application upon every call.

Edit: changed link to go to the specific post

Edited by Spiff59
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...