Sam137 Posted September 19, 2011 Share Posted September 19, 2011 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 More sharing options...
Sam137 Posted September 19, 2011 Author Share Posted September 19, 2011 Oops... Here you go with the attachment... Link to comment Share on other sites More sharing options...
water Posted September 19, 2011 Share Posted September 19, 2011 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Sam137 Posted September 19, 2011 Author Share Posted September 19, 2011 How to code that?? Link to comment Share on other sites More sharing options...
water Posted September 19, 2011 Share Posted September 19, 2011 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Sam137 Posted September 19, 2011 Author Share Posted September 19, 2011 Will thus function supported by AutoIT? It says its an unknown function Link to comment Share on other sites More sharing options...
water Posted September 20, 2011 Share Posted September 20, 2011 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
JoHanatCent Posted September 20, 2011 Share Posted September 20, 2011 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 More sharing options...
Sam137 Posted September 20, 2011 Author Share Posted September 20, 2011 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... Link to comment Share on other sites More sharing options...
water Posted September 20, 2011 Share Posted September 20, 2011 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
JoHanatCent Posted September 21, 2011 Share Posted September 21, 2011 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 More sharing options...
Spiff59 Posted September 21, 2011 Share Posted September 21, 2011 (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 September 21, 2011 by Spiff59 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now