Excel automation merging cells dynamically

[yojayy]

all-

I have written some FoxPro code that will read a table of schedules and dynamically shade rows of cells (each column representing 15 minute buckets) depending on the length of each shift to visualize coverage. The problem is dynamically selecting each shift after it is painted to MERGE and label by position.

The following code will work:

OleApp.range("b4:b55&quot.select
OleApp.selection.mergecells =.t.

etc...

I can also use direct reference to select a single cell:

Oleapp.cells(4,2).select

but I need to be able to progammatically change the range through variables in a loop. Can anyone provide an example of code that will leverage the R1C1 formula model? I cannot get the syntax in FoxPro to accept a range of cells. I have tried the offset function as well, to no avail.

Thanks in advance for even considering this problem.

yojayy

 

[Mike Gagnon]

yojayy

1. Change the range through variables in a loop.
This works with variables:

LOCAL cell1,cell2
STORE "b1" TO cell1
STORE "b7" TO cell2
oExcel=CREATEOBJECT("Excel.application")
oWorkBook=oExcel.Workbooks.Open("c:\book1.xls")
oSheet=oWorkBook.Sheets("sheet1")
oSheet.Range(cell1,cell2).Merge
oExcel.visible =.t.

Can anyone provide an example of code that will leverage the R1C1 formula model?

oExcel=CREATEOBJECT("Excel.application")
oWorkBook=oExcel.Workbooks.Open("c:\book2.xls")
oSheet=oWorkBook.Sheets("sheet1")
MESSAGEBOX(oSheet.range("A9").Formula) && Or whatever you need to do with the formula

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.