>>>>>Hi Troops,
>>>>>
>>>>>I've been goofing around with Excel spreadsheets as a potential source for some data. The one thing I haven't been able to figure out is how to allow the user to select a range within a given sheet and retrieve that range. I've done with DDE with Excel and with versions of Lotus 1-2-3 up to and including Release 5. Unfortunately, Lotus, in its infinite wisdom (gag!), decided to drop DDE support after Release 5. I've played around with its (1-2-3's) OLE automation object, but it's so poorly documented that getting anything to work correctly almost takes an act of Congress.
>>>>>
>>>>>I'm aware of the InputBox method for retrieving a range in Excel, but that is less than satisfactory. I've also looked at CurrentRegion and UsedRange, but neither seem to give me what I want.
>>>>>
>>>>>Any ideas?
>>>>
>>>>
ox.Selection.Address(.f.,.f.,xlA1) ?
>>>Thanks, Cetin. That should do it. Then all I have to do is get a Range object from the active sheet, right?
>>
>>
George,
>>There is no one only selection in excel. There could be different selected areas per sheet at the same time. Selection applies to Application and Window. Test with both. Also you could create a toolbar&button in excel and via VFPCOM bind its click event to your VFP proc ;)
>
>Hi Cetin,
>
>I know, but I'm only interested in the ActiveSheet, so the following snippet works like a champ
>* oWorkbook was returned by the Workbooks.Open method
>#DEFINE XLA1 1
>lcrange = oExcel.Selection.Address(.F., .F., XLA1)
>lnpt = AT(":", lcrange)
>IF lnpt # 0
> lctop = LEFT(lcrange, lnpt - 1)
> lcbottom = SUBSTR(lcrange, lnpt + 1)
> oRange = oWorkbook.ActiveSheet.Range(lctop, lcbottom)
> FOR EACH oCell IN oRange.Cells
> * What I need to do here.
> NEXT
>ENDIF>In the above, lcrange, corresponds to the currently selected sheet. It's just what I want and need.
>
>Thanks a bunch, again!:-)
One last thing before I leave my friend.
Doesn't directly :
oRange = oWorkbook.ActiveSheet.Range(lcRange) work ?
And if that cell operation is one of the things that excel could do in a snap (like all value = "somevalue", fill series, fill months, set formula etc) these are valid too :
oRange.AutoFill(...)
oRange.Value = "somevalue"
oRange.Formula = "=$A$1*5"
Cetin
