Excel Automation - establishing properties of Excel object

[AndrewMozley]

I am working from Tamar's very useful book 'Office Automation with Visual Foxpro'

There are a few properties which I know exist, but I cannot find out what they are. I am rather optimistically using the VFP debugger after creating an Excel object 'Thisform.zexcel'

I try keying in .zExcel.ActiveSheet.range[A1] and hoped to be able to click on it to be given a list of available properties, but this is not so.

I suspect that I have been told how to do this before, but grateful for help. Andrew Mozley

 

[JRB-Bldr]

Perhaps what you are looking to find is not a direct property of the object you think it 'belongs' to or the property is found available in another manner.

What are you looking to find?

In addition to Tamar's excellent book, there are a number of examples in the FAQ's of this forum. You might want to look there as well.

Also remember that the best way to find out what needs to be done in VFP Automation of Excel is to do the task in Excel manually while recording the actions as a Macro. Then examine/edit the Macro and find out how Excel did it without any VFP involvement.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

It's always a good idea to use macro recording from excel.
Also, if you like to use intellisense, simply create a variable of the object you want to inspect, eg I'd say it should be:

o = .zExcel.ActiveSheet.range('A1')

Then you can inspect o to get deeper in the object and property hierarchy.

Bye, Olaf.

 

[Mike Lewis]

Andrew,

Another option is to go into Excel, open the Visual Basic Editor (either from the Tools menu or by pressing Alt+F11), then go into the Object Browser (by pressing F2).

In the Library drop-down (which displays <All Libraries>), choose Excel. Then either browse the list of classes, or search for an object or property name.

When you click on a class in the left-hand pane, it will show all its properties and methods in the right pane. You can then click on a link in the bottom pane to drill down into the hierarchy.

I'm not sure if I've explained this very well (it's getting late), but you should be able to figure it out.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[AndrewMozley]

Thank you jrb. The object has plenty of properties, (e.g. .font, .value &c). My concern was that such a property is not made visible in the debugger by putting it in the watch window and then clicking on its plus sign. With other objects (even the Excel object itself), their properties are visible.

Your suggestion of recording a macro and then examining it was however most helpful.

Thank you Olaf. You also suggest recording a macro and then examining it. Thanks also for the suggestion about Intellisense. This also works in the watch window of the debugger, so I see that I can key o. into the watch window and a list is then displayed showing the properties. That tells me the names of the properies, so I can set them. Great!

It seems that in some cases properties which are not visible by drilling down in the watch window are nonetheless visible via Intellisense in the top entry box of the watch window.

Thanks also Mike.

I have in fact written a short program to check out how one can expose the properties at run time :

zExcel = CREATEOBJECT("Excel.Application")
zWorkbook = zExcel.Workbooks.Add()
zSheet = zWorkbook.Worksheets[1]
zrange = zSheet.Range("A1")
zrange.font.bold = .T.
zrange.value = "ABC"
zExcel.visible = .T.
SET STEP ON
RETURN

I find that zexcel and zworkbook can be expanded in the watch window, whereas zsheet and zrange cannot – clicking on the + sign only shows properties (like zrange.font) which have already been referenced.

There is one other quirk. If I make the object variables properties of thisform (which I usually do), then you can put them into the watch window by specifying .zexcel
But if you start entering .zexcel. into the watch window, Intellisense does not kick in! In order to get Intellisense to work you must specify the full path : Thisform.zexcel.

 

[Olaf Doschke]

The last quirk is to be expected. A syntax of .zexcel only works within a block of code starting with WITH THISFORM or more general WITH PARENTOBJECT. That's not available in the watch window, as there every entry is a single line, no chance to get WITH ENDWTIH in it.

I really don't know if the aspect of intellisense not fully picking up every possible substructure from the beginning is a weakness of Foxpro or OLE/COM, I often do a mixture of inspecting objects via intellisense finding a reference, of which the object browser is one for COM classes or online in the MSDN and search samples by googling a property or method. Or of course use macro recording, where that is applicable, that's of course quite limited to office application automation.

Bye, Olaf.

 

[Mike Lewis]

Andrew,

I sometimes find that, in cases where the Watch window doesn't show all the properties of an object, you can go to the Command window (which should be available, given that the program will be suspended at this point) and store a reference to the object in a variable.

Then, when you go back to the Watch window, you can "watch" the variable, and you will now see all the properties.

I'm not sure if that covers the case that is giving you problems, but it might be worth trying.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy