Excel question: What cell am I in

[Rory]

Does anybody know how to identify what cell is the active cell in Excel?
I am asking this because I have an Excel file that I would like to add to using DDEPoke. I want to avoid having to go to the excel window and identifying the cell myself. It would seem that I should be able to automate the process.

Rory

[Marcus Tettmar]

Pretty sure you can do that if you use VBscript/COM to automate Excel.

[pgriffin]

Rory,


Explain a little about the process and we can probably help you out. I have written plenty of ddepoke and a little VBScript for Excel and I would be glad to share if I knew what you were trying to do.

[Rory]

It may be a little hard to explain. I am generating a spreadsheet from a report of off our Enterprise Management software. The report requires some human intervention as far as decisions. Once I have arrived at the decision, I want to send the data to Excel. Then start the process over for the next item in the list. With having MS stop, I am not sure how to keep the value of the increment for the cell. When I restart the MS script, I need to know which cell to send data to.

I could possibly write the cell increment data to a *.txt file and then read that line to pick it up, but that seems cumbersome.

If it was simply a list to read, it would be easy to use DDEPoke by reading the list on an increment basis and also increment the Cell (R1C1, R1C2, etc...).

Confused?

Rory

[JRL]

Stepping into territory that I have limited experience. From my understanding of your problem it seems you could simply put your data to the clipboard with MS then paste to the "current" cell, then use press up, down, left or right to move to the next appropriate cell.

Just a thought,
Dick

[pgriffin]

A couple of options (at least) that I see here.

1. you are right, you could write to some .txt or INI file the "last used" or "next " values for your cell location....doesn't seem all that cumbersome to me.

OR

2. Just in case some user might alter the spreadsheet, you could have dderequest read through the spreadsheet until it finds the first empty cell or some predetermined marker in the spreadsheet, then start working from that point....I've done this in previous apps and it works very well.

[Rory]

Thanks, much for the feedback. Those are good options. I didnt even think of using DDERequest to determine which cell is the one I need.
I can definitely make this work now.

Thanks much

Rory

[Marcus Tettmar]

Like I said. Use VBScript/COM. If you have first opened Excel with VBScript you can later reference any of it's objects. You have access to the properties and methods of the workbooks and sheets just as you would in VBA. Try this example:

Code: Select all

VBSTART
Dim xlApp
Dim xlBook

Sub OpenTheFile(xlFile)
  'Create Excel Object (Start Excel)
  Set xlApp = CreateObject("Excel.Application")
  xlApp.visible = True
  'Open the workbook
  Set xlBook = xlApp.Workbooks.Open(xlFile)
End Sub

Function GetCurrentCell
   GetCurrentCell = xlApp.ActiveCell.Row & "," & xlApp.ActiveCell.Column
End Function

VBEND

VBRun>OpenTheFile,c:\spreadsheets\mybook.xls
Wait>10
VBEval>GetCurrentCell,cname
MessageModal>cname

Modify the VBRun line to refer to a real excel file. Run the script, wait 10 second and a message box will pop up showing you what cell is currently selected. While waiting 10 seconds you can try changing the selected cell.

[Rory]

Marcus,
That is exactly what I am looking for.

Thanks,
Rory