What cell am I in?

[JRL]

I was asked in a PM how to acquire the current cell location when you have an excel spreadsheet open. The following code works for me with my Excel 2007 and Excel 2013. I suspect it work work with most others.

There is likely a better way. Suggestions are welcome.

I came up with this in about a half an hour. Using Google to discover how to do the processes manually then converting those processes to code. There was a slight difference between 2007 and 2013. When the "Name Manager" Window pops up, in 2007 sending "n" is all that's required to get to the "New Name" window. In 2013 the keys are Alt + n. Fortunately that also works in 2007.

Code: Select all

//Setfocus to your window.  As-is might or might not work for you
SetFocus>Excel*
Wait>0.3

//Ctrl + F3 brings up range name manager dialog
Press ctrl
Press F3
Release Ctrl
WaitWindowOpen>Name Manager
Wait>0.3

//Pressing "n" switches to "New Name" dialog
Press Alt
Send>n
Release Alt
WaitWindowOpen>New Name
Wait>0.3

//Press Tab three times to get to the cell location
Press Tab * 3

//Press Ctrl + c to copy cell location
Press Ctrl
Send>c
Release Ctrl
WaitClipBoard
Wait>0.3
GetClipBoard>CellLocation

//Close the two windows by pressing ESC twice
Press ESC
WaitWindowFocused>Name Manager
Wait>0.3
Press ESC

//Parse the cell data
MidStr>CellLocation,2,10000,CellLocation
Separate>CellLocation,$,Item

Let>SheetName=Item_1
Let>Column=Item_2
Let>Row=Item_3

MDL>Sheet = %SheetName%%crlf%Column = %Column%%crlf%Row = %Row%

[hagchr]

This gives the sheet name and cell through VBS:

VBSTART
Dim objExcel
Sub GetExcel
Set objExcel = GetObject( , "Excel.Application")
End Sub
VBEND

VBRun>GetExcel
VBEval>objExcel.ActiveWorkbook.Name,Sheet
VBEval>objExcel.ActiveCell.Address,Cell
MessageModal>Sheet:%Sheet%%CRLF%Cell: %Cell%
  View Snippet Page

[JRL]

Excellent! Way more reliable. Thank you hagchr.

[hagchr]

Sorry, I was too quick to submit, the previous one gives the workbook and cell. If you want the sheet name you just need to change ActiveWorkbook to ActiveSheet, ie.

VBSTART
Dim objExcel
Sub GetExcel
Set objExcel = GetObject( , "Excel.Application")
End Sub
VBEND

VBRun>GetExcel
VBEval>objExcel.ActiveSheet.Name,Sheet
VBEval>objExcel.ActiveCell.Address,Cell
MessageModal>Sheet: %Sheet%%CRLF%Cell: %Cell%
  View Snippet Page