How do I find 'R1C1' location from a selected cell?

[marknrox]

How do I get the R1C1 style cell location from the currently selected cell in Excel?

I can perform a request if I know the location...

Thanks,
marknrox

 

[Volkmaniac]

There is a good example on

http://www.aspectscripting.com

called DDEEXAM4.WAS that should be able to help you. That shows you how to place something in an Excel cell and you should be able to modify it to locate a particular cell.

 

[hankm3]

Hello,

The Easy way to understand the relationship is in the Excel "Grid"..

Excel works with Rows and Columns. The Rows are Numbered 1 thru the end of the Spreadsheet. Example 1 thru 3276.
The Columns are Lettered A -> Z then AA - ZZ, etc.

R1C1 is Row 1 Column A
R1C2 is Row 1 Column B

R2C1 is Row 2 Column A
R2C26 is Row 2 Column Z
R2C27 is Row 2 Column AA

Knob's Website has Several examples that I wrote using DDE to Excel that will show how to increment the Rows and Columns when using DDE.

http://www.aspectscripting.com

Hank

 

[marknrox]

I believe I need to rephrase...

When I use dde to do a 'FIND.CELL' operation, it finds the data and Excel visually shows it as the currently selected cell on the spreadsheet. What I need to know is how will the cell's location information get back to Procomm so that I can do a DDEREQUEST for the contents in that cell?

 

[hankm3]

Hello,

One of the scripts I wrote that is on Knobs website deals with extracting the Data from Excel and sending it to a Text file. This might give you some ideas.

You may even have to write an Excel Macro that will get the Cell Data and have Excel set up a Link to Procomm to Send the Data. I had some problems with extracting the Data via a Procomm DDEREQUEST Command.

I'll look back and see what I can find in some test Scripts.

Hank

 

[knob]

Hank, one thing I had been working on via email (haven't had time to get back to it yet) was issuing the Selection() or Active.Cell() commands, but wasn't able to get a value returned back to Procomm. You may want to try working your Excel magic with those commands and see if you are able to come up with a solution?


aspect@aspectscripting.com

http://www.aspectscripting.com

 

[hankm3]

Knob,

According to the information I've come up with via the Excel DDE Help File, None of the functions return a value.
I did create an Excel Macro that stored the Size of a Cell and put the value into a Cell I selected. I could then retrieve this info.

Hank

 

[hankm3]

Hello,

This is from one ofmy scripts for DDE.

While Ro < 6 ;* Last Row in Excel

strfmt sOne &quot;%s%d%s%d&quot; sStrR Ro sStrC C1
strfmt sTwo &quot;%s%d%s%d&quot; sStrR Ro sStrC C2
strfmt sThr &quot;%s%d%s%d&quot; sStrR Ro sStrC C3
strfmt sFou &quot;%s%d%s%d&quot; sStrR Ro sStrC C4
strfmt sFiv &quot;%s%d%s%d&quot; sStrR Ro sStrC C5

;* GET THE DATA FROM THE ROWS
;* AND COLUMNS IN EXCEL 97

Dderequest LinkVar sOne sTok1
fputs 1 sTok1
Dderequest Linkvar sTwo sTok2
fputs 1 sTok2
Dderequest Linkvar sThr sTok3
fputs 1 sTok3
Dderequest Linkvar sFou sTok4
fputs 1 sTok4
Dderequest Linkvar sFiv sTok5
fputs 1 sTok5

pause 1

Ro++ ;* Increment Row Number
Endwhile

As you can see, you have to know which Cell Reference you want data from. I suppose if an Excel Sheet kept a standard format, you could do a Strcmp with a Cell Contents and based on what is there, change the Row and Column for another DDEREQUEST for another Cell.

Hank

 

[hankm3]

Knob,

This is part of my DMS Monitor Script, I mentioned last week. I changed the code to retrive the Value from the Header Cell. [R1C10]


Proc Formatting
ddepoke LinkVar &quot;R1C10&quot; &quot;1&quot;
ddepoke LinkVar &quot;R2C10&quot; &quot;5&quot;
ddepoke LinkVar &quot;R3C10&quot; &quot;6&quot;
ddepoke LinkVar &quot;R4C10&quot; &quot;7&quot;
ddepoke LinkVar &quot;R5C10&quot; &quot;8&quot;
ddeexecute LinkVar &quot;[SELECT(`&quot;C10`&quot]&quot;
ddeexecute LinkVar &quot;[COLUMN.WIDTH(7.3)]&quot;
ddeexecute LinkVar &quot;[SELECT(`&quot;R1`&quot]&quot;
ddeexecute LinkVar &quot;[INSERT(2)]&quot;
;* pause 3
ddeexecute LinkVar &quot;[SELECT(`&quot;R1C10`&quot]&quot;
ddeexecute LinkVar &quot;[FORMULA(`&quot;=SUM(R2C10:R3264C10)`&quot]&quot;
ddeexecute LinkVar &quot;[ZOOM(75)]&quot;
ddeexecute LinkVar &quot;[OPTIONS.VIEW(,,,,,1,,1)]&quot;
ddeexecute LinkVar &quot;[PAGE.SETUP(,,,,,,,,,,2,1,65)]&quot;
ddeexecute LinkVar &quot;[SELECT(`&quot;R1C10`&quot]&quot;
dderequest LinkVar &quot;R1C10&quot; AA
pause 10
UserMsg &quot;AA: %d&quot; AA
pause 3
Endproc

Hank

 

[knob]

I think you would have better luck asking in one of the Tek-Tips forums that covers Office and see if someone there knows of the necessary command for Procomm to send. I did a lot more digging on this and couldn't find a built-in Excel command that will return the selected cell in RnCn format. The two commands I mentioned earlier don't return the current cell in that format and I'm having no luck getting that data back to Procomm, although the same command works OK in Excel.


aspect@aspectscripting.com

http://www.aspectscripting.com

 

[hankm3]

Hello,

I've been work on this for a few days now, and I'm close to a solution. Once the Active Cell is Located, Excel sets a DDE connection back to Procomm, and sends back the Location of the Cell to Procomm. If the Cell Needs to be in the Form of R1C1, that should not be a problem.

Hope to have this in a Few Days.

Hank

 

[hankm3]

hello,

Here is a Simple Macro that Can be inserted into a Spreadsheet. You'll need to work on the Code for Checking when the Next FIND.CELL is Called.

Sub DDE_Act_Cell()

Dim ActCell As String
Dim Chan As Long
Dim Data As String
ActCell = Application.ActiveCell.Address(, , xlR1C1)
Chan = DDEInitiate(&quot;PW5&quot;, &quot;system&quot
Chan = DDEInitiate(&quot;PW5&quot;, &quot;Hello.wax&quot
Range(&quot;AJ8&quot.Value = ActCell
DDETerminate Chan

End Sub

Note that My Script was Called &quot;Hello.wax&quot;. Also I used
Cell AJ8 to hold the Active Cell Location. You'll have to have Procomm go to that Location to Extract your Data.

Hank