Jump to content
Sign in to follow this  
theAutoitSpammer

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

Recommended Posts

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 by alram

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by Hatcheda
  • Like 1

Share this post


Link to post
Share on other sites
theAutoitSpammer

Hatcheda

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

Al

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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)

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

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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)

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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·

Share this post


Link to post
Share on other sites
theAutoitSpammer

thanks Auth

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

Al

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×