how to get row & col of selected cell in EXCEL[un-SOLVED]

[theAutoitSpammer]

how to get row & col of selected cell in EXCEL in R1C1 format

for instance if cell B3 is selected, a row_func()=3 and col_func()=2

is there something like this?

thanks in advance

Al

Edited May 6, 2009 by alram

[John117]

While this is not what you are looking for . . . When I need help with Excel I just build it there as vba then convert it . . .

EX

With $oExcel.Selection.Interior

.PatternColorIndex = "xlAutomatic"

.Color = 255 ;Red - End

EndWith

[John117]

ok, here ya go. Use the vba recorder to learn a bit as you go.

#Include <Excel.au3>
$oExcel = _ExcelBookNew(1)
_ExcelSheetAddNew($oExcel, "Temp")
_ExcelSheetActivate($oExcel, "Temp")
_ExcelWriteCell($oExcel, 1, 1, 1)
_ExcelWriteCell($oExcel, 2, 1, 2)
_ExcelWriteCell($oExcel, "=RC[-2]+RC[-1]", 1, 3)
$oExcel.ActiveSheet.Range("C1" ).Activate
$Row = $oExcel.ActiveCell.Row
$Column = $oExcel.ActiveCell.Column
$Formula = $oExcel.ActiveCell.FormulaR1C1
Msgbox(0,"R1C1", $Row & ", " & $Column & ", " & $Formula)

Edited April 18, 2009 by Hatcheda

[theAutoitSpammer]

Hatcheda

thanks a lot I'll try it as soon as possible

Al

[John117]

no prob . . . the only important parts to get what you want are . . .

#Include <Excel.au3> ;has to be added at top of sctipt

$oExcel = _ExcelBookNew(1) ;if you are creating new sheet, new book - you could also just active an existing one -see help file.

_ExcelSheetAddNew($oExcel, "Temp") ;adds new sheet

_ExcelSheetActivate($oExcel, "Temp") ;activates sheet

$oExcel.ActiveSheet.Range("C1" ).Activate ;activates cell

$Row = $oExcel.ActiveCell.Row ;Gets row

$Column = $oExcel.ActiveCell.Column ; Gets Column

Msgbox(0,"R1C1", $Row & ", " & $Column) ;Displays both

The rest is just for demo so you can see how it works. -its ready to run first example.

[theAutoitSpammer]

John

Sorry to bother you again, I modify your script as follows, but it doesn't work

for me (it assumes a workbook is already open and a cell "A1" is selected with the mouse and with value="John").

Can you get it to run? What am I doing wrong? Thanks in advance

Al

;----------------------------

#include <Excel.au3>

;-----------------------------

$title = WinGetTitle('Microsoft Excel')

$oExcel = _ExcelBookAttach($title, "Title") ;with $s_mode = "Title" ==> Title of the Excel window

MsgBox(0, "", IsObj($oExcel))

Sleep(2000)

WinActivate($title)

$Row = $oExcel.ActiveCell.Row ;Gets row

$Column = $oExcel.ActiveCell.Column ; Gets Column

$val = _ExcelReadCell($oExcel, $Row, $Column)

MsgBox(0, "", $val)

;--------------------------------

[John117]

Do you have a personal.xls file? I believe you are attaching to it . . .

Test with this . . . after this . . .

WinActivate($title)

MsgBox(0, "", $oExcel.name)

[theAutoitSpammer]

yes I do, but how do you know that?????

follow this:

1- I create book1 from scratch after reboot

2- run the script

3- $title is book1.xlsx, isObj=1, $oExcel.name=book1.xlsx

4- then "$Row = $oExcel.selection.cells.row" returns an error (a=selection.cells.row works in excel)

then

1- open book2

2- title=book2

3- isObj=1

4- but $oExcel.name=book1.xlsx not book2 ????????????????

5- after that who cares

by the way i'm using excel 2007 which sucks!!!

help...script follows

;----------------------------

#include <Excel.au3>

;-----------------------------

$title = WinGetTitle('Microsoft Excel')

MsgBox(0, "", $title)

$oExcel = _ExcelBookAttach($title, "Title") ;with $s_mode = "Title" ==> Title of the Excel window

MsgBox(0, "", IsObj($oExcel))

_ExcelWriteCell($oExcel, "If you can read this, then Success!", 1, 1) ;Write to the Cell

Sleep(2000)

WinActivate($title)

MsgBox(0, "", $oExcel.name)

$Row = $oExcel.selection.cells.row ;ActiveCell.Row ;Gets row

$Column = $oExcel.selection.cells.column ;ActiveCell.Column ; Gets Column

$val = _ExcelReadCell($oExcel, $Row, $Column)

MsgBox(0, "", $val)

;--------------------------------

[John117]

yes I do, but how do you know that?????

-because I have one also, and I am using 07 also.

$title = WinGetTitle('Microsoft Excel')
MsgBox(0, "", $title) 
$oExcel = _ExcelBookAttach($title, "Title") ;with $s_mode = "Title" ==> Title of the Excel windowoÝ÷ ØÞ'¯zØb²+2¢êé®åzb²Gü¨ºj«²Úl~)^mçºÇÁ©íyÛh{.­ëajܨ¹Øm«ÞÞ¶§¢w±Çâê)zz+z[ʦk'(w¶Ú-ì+¢x(ëajج¶jÇ°Øiz»(©qÊ+¶¬yÊ'uº(*.¢§«^®«²ÚlÈbuק{ay¶
èºw[ºÛ-Ykºyâ*.h¥éÚ±«Þuº(X¤zØb±«­¢+Ù%¥±á¥ÍÑÌ¡MÉ¥ÁѥȵÀìÅÕ½ÐìÀäÈí¹á±ÌÅÕ½Ðì¤Q¡¸($ÀÌØí½á°ô}á±   ½½­=Á¸¡MÉ¥ÁѥȵÀìÅÕ½ÐìÀäÈí¹á±ÌÅÕ½Ðì°Ä¤)±Í($ÀÌØí½á°ô}á±  ½½­9Ü Ä¤(%}á±   ½½­MÙÌ ÀÌØí½á°°MÉ¥ÁѥȵÀìÅÕ½ÐìÀäÈíÅÕ½Ðì°ÅÕ½Ðíá±ÌÅÕ½Ðì¤(%%ÉɽÈQ¡¸5Í    ½à À°ÅÕ½ÐíÉɽÈÅÕ½Ðì°ÅÕ½ÐíU¹±Ñ¼ÍÙÌÌìÅÕ½Ðì°Ì¤)¹%oÝ÷ Ø    l¢'Ú¨»§¶+-ç[Úç­éÚºÚ"µÍÌÍÝ[H  ÌÍÛÑ^Ù[XÝ]PÙ[[Y

will get that for you.

[theAutoitSpammer]

Thanks John, but.....

your original script works fine when opening an existing book or creating a new book and then activate the cell from autoit.

The problem is that the workbook is already open then select a cell with the mouse and when I run the script it will get the cell row number and process it.

When you script a line in excel macro like [row1=selection.cells.row] works fine, why can't this work in autoit?---- $row=$oExcel.selection.cells.row

The second problem is when I open (from excel) another book and try to do the same, then I get the wrong (previous) book name.

I hope that you or anyone can solve this because I'm lost.

thanks again

Al

[Authenticity]

This works for me:

#include <Excel.au3>

$oExcel = _ExcelBookAttach('microsoft excel', 'title')
$oRange = $oExcel.Application.ActiveCell
ConsoleWrite($oRange.Column & @TAB & $oRange.Row & @LF)


$oExcel = 0
ExitoÝ÷ Øý½ç!jxtLzV®ßl(miÈ_ºw-÷öËb¶WjÇ­¡+kx+k²¢}ý¶¯zÚ,¥ç"+ayìZr×±qépwhÂØ­à¢ëmæ¶+Þ²¶§Z(ú+Ó~¨
i'¶*'Ëb½ì!z·

[theAutoitSpammer]

thanks Auth

I'll try as soon as I comeback from a 10 day business trip.

Al