Fetch the Row and Column number of a string in excel

[Automationuser]

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)

 

[Juvigy]

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' ?

[water]

Do you know the address cell where the string is stored?

[Automationuser]

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

[Juvigy]

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.

[water]

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.

[Automationuser]

@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

[water]

Can you post the AutoIt code you have so far?

You need to specify the worksheet you want to access.

[Automationuser]

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

[water]

Columns("ABC").Select

should become

$oExcel.ActiveSheet.Columns("ABC").Select

Edited July 25, 2013 by water

[Juvigy]

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

[Automationuser]

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.

[Juvigy]

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

[Automationuser]

My bad...had incorrect file name in my script.

Thanks...it worked