Automationuser Posted July 25, 2013 Share Posted July 25, 2013 Hello All, Is there any way to fetch the Row and Column number of a string in excel? I am looking for a function something like _ExcelGetRowColumnNumber($String) Link to comment Share on other sites More sharing options...
Juvigy Posted July 25, 2013 Share Posted July 25, 2013 Hello All, Is there any way to fetch the Row and Column number of a string in excel? I am looking for a function something like _ExcelGetRowColumnNumber($String) Give me an example of what you mean by 'string' - 'C5' ? Link to comment Share on other sites More sharing options...
water Posted July 25, 2013 Share Posted July 25, 2013 Do you know the address cell where the string is stored? 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...
Automationuser Posted July 25, 2013 Author Share Posted July 25, 2013 I have a tescase ID "TC_01" in the 6th row and 1st column and this address might change anytime. So what I need is to search for Text "TC_01" and fetch the row and column number and write the result of the testcase in the 2nd column. TC_01 Pass Link to comment Share on other sites More sharing options...
Juvigy Posted July 25, 2013 Share Posted July 25, 2013 I dont remember if there was a FIND function in the excel UDF , but you can convert this VBA: Columns("B:B").Select Selection.Find(What:="Belguim", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Cells.Find(What:="Belgium", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Then you can do Activecell.address or something similar. Link to comment Share on other sites More sharing options...
water Posted July 25, 2013 Share Posted July 25, 2013 There is no search function in the Excel UDF that comes with AutoIt - at the moment. Please have a look at the search function in my rewrite of the Excel UDF (still an early alpha version!). Download link can be found in my signature. 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...
Automationuser Posted July 25, 2013 Author Share Posted July 25, 2013 (edited) @Juvigy, Where can I find info about VBA? I get ERROR: Columns(): undefined function. if I use tht code in my script Edited July 25, 2013 by Automationuser Link to comment Share on other sites More sharing options...
water Posted July 25, 2013 Share Posted July 25, 2013 Can you post the AutoIt code you have so far? You need to specify the worksheet you want to access. 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...
Automationuser Posted July 25, 2013 Author Share Posted July 25, 2013 Global $oExcel = _ExcelBookOpen(@ScriptDir&"ResultTestRunResults.xls") ; Open the Excel file _ExcelSheetActivate($oExcel, "ABC") Columns("ABC").Select Selection.Find(What:="TC_01", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Cells.Find(What:="TC_01", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Link to comment Share on other sites More sharing options...
water Posted July 25, 2013 Share Posted July 25, 2013 (edited) Columns("ABC").Select should become $oExcel.ActiveSheet.Columns("ABC").Select Edited July 25, 2013 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...
Juvigy Posted July 25, 2013 Share Posted July 25, 2013 This works for me: #include <Excel.au3> $oExcel = _ExcelBookAttach("C:\111.xlsx") $oExcel.Application.ActiveSheet.Columns("ABC").Select ConsoleWrite($oExcel.Application.Selection.Find("TC_01").Address&@CRLF) $oExcel = 0 Link to comment Share on other sites More sharing options...
Automationuser Posted July 25, 2013 Author Share Posted July 25, 2013 I am receiving the following : Variable must be of type "Object".: $oExcel.Application.ActiveSheet.Columns("ABC").Select $oExcel^ ERROR if I use isobj(), its returning 0. Link to comment Share on other sites More sharing options...
Juvigy Posted July 25, 2013 Share Posted July 25, 2013 I am receiving the following : Variable must be of type "Object".: $oExcel.Application.ActiveSheet.Columns("ABC").Select $oExcel^ ERROR if I use isobj(), its returning 0. You need to have C:111.xlsx already opened. You can change _ExcelBookAttach to _ExcelBookOpen or try it like this: $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 1 $oExcel.WorkBooks.Open($FilePath) $oExcel.Application.ActiveSheet.Columns("ABC").Select ConsoleWrite($oExcel.Application.Selection.Find("TC_01").Address&@CRLF) $oExcel = 0 Link to comment Share on other sites More sharing options...
Automationuser Posted July 25, 2013 Author Share Posted July 25, 2013 My bad...had incorrect file name in my script. Thanks...it worked 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