Sam137 Posted September 22, 2011 Share Posted September 22, 2011 (edited) In a excel sheet I want to search for the occurence of $currRepTitle in the range and select the cell which at the right(3 positions) of that particular occurence and mark it as "Y". See attached. How to accomplish this... $currRepTitle = "aaa" _ExcelSheetActivate($oExcel, "Report List") $oExcel.Application.ActiveSheet.Range ("A1:C200").Find ($currRepTitle).Select $oExcel.Application.ActiveCell.Offset ($aa).Select $oExcel.Application.ActiveCell.Offset (0, 3).Select _ExcelWriteCell($oExcel, "Yes", "D1:D200",4) _ExcelBookSave($oExcel) Edited September 22, 2011 by Sam137 Link to comment Share on other sites More sharing options...
water Posted September 22, 2011 Share Posted September 22, 2011 There is an available. Function _ExcelFindInRange "Finds all instances of a string in a range and returns their addresses as a two dimensional array". Move 3 cells to the right and use _ExcelWriteCell to write "Yes". 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 22, 2011 Author Share Posted September 22, 2011 Any other method without using the UDF? Link to comment Share on other sites More sharing options...
water Posted September 22, 2011 Share Posted September 22, 2011 No, because the UDF is just a wrapper around Excel COM interface (what you have already posted in your example) 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 22, 2011 Author Share Posted September 22, 2011 Can it be used with other people without downloading the UDF Link to comment Share on other sites More sharing options...
water Posted September 22, 2011 Share Posted September 22, 2011 Sure. Compile the script and pass the exe to the users. Then everything is contained in one single file. Use obfuscator to reduce the size and remove all unused functions etc. 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 22, 2011 Author Share Posted September 22, 2011 Ok, how to use the excel com UDF? I dont know... Link to comment Share on other sites More sharing options...
water Posted September 22, 2011 Share Posted September 22, 2011 Download the UDF to the AutoIt\Include directory or the directory where your script resides. The ExcelCOM UDF is just an extended Excel UDF (which is part of AutoIt), so you can use the examples there. Call _ExcelFindInRange as described in the UDF - every function has a header that describes syntax, returned values and error codes. 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 22, 2011 Author Share Posted September 22, 2011 (edited) Thanks, when i try to download Excel.au3, EXCELCOM_UDF into the autoit/include folder.... And i executed $aa = _ExcelFindInRange($oExcel, $ConcatVal, "D1:D200") MsgBox(0,"",$aa) Its saying "not a valid function" Edited September 22, 2011 by Sam137 Link to comment Share on other sites More sharing options...
water Posted September 22, 2011 Share Posted September 22, 2011 You download ExcelCOM.au3, right? Can you post the whole script? 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 22, 2011 Author Share Posted September 22, 2011 I downloaded EXCELCOM_UDF.au3, when i go and search the file is not there in the autoit/include. when i re download the same, it asking for would you want to replace ? Link to comment Share on other sites More sharing options...
water Posted September 22, 2011 Share Posted September 22, 2011 (edited) Than it has to be somewhere else. I don't know where your browser saves downloaded files. Search your disk for the UDF? Edited September 22, 2011 by water 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 22, 2011 Author Share Posted September 22, 2011 I sought out the prob and its in the autoit/include dir. Still i get the same error Link to comment Share on other sites More sharing options...
Sam137 Posted September 22, 2011 Author Share Posted September 22, 2011 Now i have included the header #include <ExcelCOM_UDF.au3>. When i run the script, its showing the error as attached screen shot. Link to comment Share on other sites More sharing options...
water Posted September 22, 2011 Share Posted September 22, 2011 I have a c:\temp\test.xls that looks like line 1 line 2 line 3 aaa when I use#include <excelcom_udf.au3> #include <array.au3> $ConcatVal = "aaa" $oExcel = _ExcelBookOpen("C:\temp\test.xls") $aFound = _ExcelFindInRange($oExcel, $ConcatVal, "A1:A200") _ArrayDisplay($aFound)I get the following display [0]|1 [1]|A4|Z4S1|1|4 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...
water Posted September 22, 2011 Share Posted September 22, 2011 You can not have both excel udfs in your script. Remove "#include <excel.au3>" 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...
water Posted September 22, 2011 Share Posted September 22, 2011 Yes, solution is in my last post. 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...
water Posted September 22, 2011 Share Posted September 22, 2011 If you use my example excel this script should write "Yes" into column 4 of the line with "aaa" #include <excelcom_udf.au3> #include <array.au3> $ConcatVal = "aaa" $oExcel = _ExcelBookOpen("C:\temp\test.xls") $aFound = _ExcelFindInRange($oExcel, $ConcatVal, "A1:A200") _ArrayDisplay($aFound) _ExcelWriteCell($oExcel, "Yes", $aFound[1][3], $aFound[1][2] + 3) 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 22, 2011 Author Share Posted September 22, 2011 when i used the same code, i am not getting the display... instead i am getting row and column as 0 Link to comment Share on other sites More sharing options...
water Posted September 22, 2011 Share Posted September 22, 2011 You are sure you have a file C:\temp\test.xls with the content I posted? Then run this extended script (error checking) #include <excelcom_udf.au3> #include <array.au3> $ConcatVal = "aaa" $oExcel = _ExcelBookOpen("C:\temp\test.xls") if @error then exit msgbox(0,"","error opening excel file. @error = " & @error) $aFound = _ExcelFindInRange($oExcel, $ConcatVal, "A1:A200") if @error then exit msgbox(0,"","error finding string. @error = " & @error) _ArrayDisplay($aFound) _ExcelWriteCell($oExcel, "Yes", $aFound[1][3], $aFound[1][2] + 3) if @error then exit msgbox(0,"","error writing text. @error = " & @error) 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...
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