Sign in to follow this  
Followers 0
Sam137

Searching particular values with Excel Array

12 posts in this topic

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)

Share this post


Link to post
Share on other sites



Oops... Here you go with the attachment...

post-65245-0-64319200-1316467264_thumb.j

Share this post


Link to post
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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

How to code that??

Share this post


Link to post
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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Will thus function supported by AutoIT?

It says its an unknown function

Share this post


Link to post
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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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


Share this post


Link to post
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...

Share this post


Link to post
Share on other sites

can you post the code please?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
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.


Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

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

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
Sign in to follow this  
Followers 0