Viewing Excel Properties within VFP 1

[AndrewMozley]

I am working with Tamar's very helpful book, "Microsoft Office Automation in Visual Foxpro" to read and write Excel files.

I would like to see all the properties that exist for an workbook object and for a worksheet object. Specifically I need to know how many sheets there are in a workbook so that I can open those that exist, but not get a run-time error if I try to read a non-existent work-sheet. I have an idea that this is accessible via VBA but I do not use VBA very often and have forgotten how to work with VBA in Excel.

I have tried using the VFP debugger, and can watch my object .zexcel and even a workbook which I have opened by saying :

.zWorkbook = .zExcel.Workbooks.Open(Myfilename)

But the debugger tends to run out of steam. It will tell you that something is an object but will not let you see the properties of that object unless you already know the names of them

Thanks. Andrew M.

 

[GriffMG]

Hi Andrew

I think you can use something like

oexcel.sheets.count

To find the number of sheets, and then use the index (not the name) to access each sheet:

for i = 1 to oexcel.sheet.count
  ? oexcel.sheets(i).name
next

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Andrew,

You're right. The debugger only knows about properties that have been referenced in the current session.

One workaround is to suspend the program while the object is instantiated (I assume you are already doing that, otherwise you wouldn't see it in the debugger). Then go to the command window and store an object reference to the object in question in a variable. For example:

oxl = someform.zExcel.Workbooks

Then, still in the command window, use Intellisense to explore the properties. Type oxl and a dot, and Intellisense will show all the properties (and methods), regardless of whether the debugger knows about them.

Be sure to release the variable when you have finished this exercise.

But if you simply want to know how many sheets there are in the workbook, Griff has given you the answer.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[brigmar]

You're looking to determine the property names and values for an unknown COM object ?

AMEMBERS() function.

n = AMEMBERS( laProps, object, 3 )

(the 3 is required for COM objects, but cannot be used in .app or .exe)

http://msdn.microsoft.com/en-us/library/x45a49w6(v=VS.71).aspx

 

[cbasoz]

Check these:

http://www.foxite.com/archives/macros-in-word-0000146626.htm

and same tip for excel posted on UT as is:

>hi everybody,
>
>
>i want intellisence to have properties , method in my programm but since succes.
>
>
>PUBLIC oExcel AS OBJECT OF "Excel.APPLICATION"
>
> oExcel. && ctrl + J does not work
>
>
>
>how can i do it ?
>
>
>bernhart

Do not use public with Excel (you will soon experience why not if you do):



local oExcel as 'Excel.Application', ;
oWorkBook as 'Excel.WorkBook', ;
oSheet as 'Excel.WorkSheet'

oExcel = CREATEOBJECT('Excel.Application')
WITH oExcel as 'Excel.Application'
* oWorkBook = .Workbooks.Add()
WITH .Workbooks.Add() as 'Excel.WorkBook'
* oSheet = .ActiveSheet
with .ActiveSheet as 'Excel.WorkSheet'
* .
endwith
ENDWITH
endwith

But though working with intellisense this way helps, I think you can't get so much deeper easily with it is object model (saying easily I mean it is cumbersome to specify all those as blahblah series when Excel have tons of objects). One thing I find easy is, instead of using 'as ...' I :
-suspend the code at a point with set step on
-Size the VFP and Excel windows so I can see both
-Go to command window
-Assign some object to a variable
-Type in command window (even if I had no "as ..." and no matter how deep it is in object hiearchy) and I get intellisense there
-Execute the code there and either get an error or if it is right see the result in Excel interactively
-When satisified, copy the code from command window and put in production code

That is the best working code writing for me when it is Excel, Word or any other non-VFP COM object.
Cetin


Cetin Basoz
MS Foxpro MVP, MCP

 

[AndrewMozley]

Thank you all. I have defined a variable in the command window :

 oxl = .zworkbook

And then I find that Intellisense works both in the command window and in the debugger window.

I am still rather surprised that variable oxl has more status (as far as Intellisense is concerened) than the original property (.zworkbook) from which it received its value, but I know now how to do this.

What a lot of properties there are . . .

 

[cbasoz]

And BTW you can drill down all the PEM and constants using Object Explorer (not very helpful though other than writing code for you if you drag&drop constants node or an event on to a code window).

Cetin Basoz
MS Foxpro MVP, MCP

 

[AndrewMozley]

Forgive my ignorance. What is "Object Explorer"?

 

[Mike Lewis]

Andrew,

There's an Object Browser in VFP, on the Tools menu. Click the Open Type Library button, then go to the COM Libraries page. You can pick a COM object, and then view its properties, methods, interfaces, enumerations, etc.

Personally, I don't find it all that useful. If you're working with Office Automation classes such as Excel or Word, the VBA Object Browser is much more useful:

1. Open the relevant Office applicatoin.

2. Go to Tools / Macros / Visual Basic Editor.

3. From within the editor, go to View / Object Browser.

4. In the <All Libraries> combo (top-left corner), choose the library for your application (such as Excel).

5. You'll now see all the classes in a pane on the left. Click on one of them, and you'll see its members to the right. For example, if you click on Workbook, you'll see that it has a property called Sheets.

6. Click on the member of interest (Sheets, in this example). At the bottom of the screen, click on the hyperlink for the selected member. This will go to the relevant member. So, in this example, click on Sheets at the bottom of the screen, and you will see that is has a Count property.

7. Now click the Help button. (Be careful; there are two Help buttons; don't click the one on the main toolbar). This should take you to the Help page for the Count property.

Note that the above menu names, etc. might vary with different versions of Office, but it should all be fairly obvious.

Give it a try, and let us know what you think.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[AndrewMozley]

Thanks very much Mike. Yes, the VBA Object browser seemed more useful. I can see that there is a huge amount of information there. It will be a matter of becoming familiar with the method of navigating through all the stuff. I will keep your notes.

I have not written VBA code in Excel but feel that I ought to become familiar with macros. I see there is material in Excel Help. is there any guide containing a worked example?

 

[GriffMG]

Recording macros in Excel is a very good way to learn how they work - particularly if you want to automate from VFP

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Recording macros in Excel is a very good way to learn how they work

Yes, that's definitely true. If there's something I can't figure out, I usually find that recording a macro helps, even though I don't know VBA.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

It would be Object Browser as Mike said. It is very useful if you need to write code say an eventhandler code. You simply drag&drop an event on to a code window. As far as I know you can't do the same with VBA object browser. VFP's browser do what VBA one does plus more. Be carefull if you expand the one that has many members it takes some time to load all.

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Cetin,

Yes, that's a good point about dragging the event into an editing window. It's not something I usually do, but I can see that it could be useful.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[AndrewMozley]

Cetin

Was interested in your comment about dragging and dropping an event onto a code window. How do I do that? For example if I have a button cmdclose, with an event (I believe) cmdclose.click(), what do I need to do to simply drag it onto a code window? and what will it show me?

Thanks. Andrew M.

 

[TamarGranor]

The Object Browser is useful for one particular type of event handling. When you want to write VFP code to respond to events from an Automation server (like Excel), you can drag the appropriate interface from the Object Browser into a VFP code to give you a skeleton for the code you need.

Check the section "Responding to Office Events" in this paper on my website (

http://www.tomorrowssolutionsllc.com/Materials/Advanced_Automation.htm)

for an explanation of what that's all about and how to do it.

Tamar

 

[cbasoz]

Below is one of the many samples I have posted on UT.

(ExcelHandler code was written by VFP by drag & drop appevents node and later some of the code was filled - VFP originally writes as:

x=NEWOBJECT("myclass")

DEFINE CLASS myclass AS session OLEPUBLIC)
...

Steps to create this code:

1) Go to command window
Modify command [enter]

2) Leave code window open and go to tools\object browser

3) Load excel (actual name is different between versions, it was MS Excel, now something like Officew.Interop.Excel off the top of my head)

4) Expand Excel node, then Interfaces node

5) LOcate "AppEvents" node

6) Drag and drop on to the code window you left open.

wasn't that easy Open another code window and drag&drop contants node. Save as xlcontants.h (you would need this often if you would do excel automation). You do the same with other office applications.

Note: This is an "interface". You cannot remove any of the procedures from the code but may leave the body empty to use default implementation. This sample intercepts Select All, workbook save,print, close.
You can add command bar and buttons to excel with your own click code. However that is better handled with Excel addin done using VB with office SDK (I once wrote using VFP too but the VFP one was ill behaving).

Clear
Local ox As 'excel.application'
ox = Newobject('excel.application')
oxEvents = Newobject("ExcelHandler",'','',ox)
Eventhandler(ox,oxEvents)
With ox
  .Workbooks.Add
  With .ActiveWorkBook.ActiveSheet
    For ix = 1 To 10 && normally you should never use such data filling
      For jx = 1 To 10
        .Cells(m.ix,m.jx).Value = m.ix * 1000 + m.jx
      Endfor
    Endfor
  Endwith
  .Visible = .T.
Endwith

Define Class ExcelHandler As Session OlePublic

  Implements AppEvents In "c:\program files\microsoft office\office11\excel.exe"

  oHook = Null
  Procedure Init(loHook)
    This.oHook  = loHook
  Endproc

  Procedure AppEvents_NewWorkbook(Wb As VARIANT) As VOID
  Endproc

  Procedure AppEvents_SheetSelectionChange(Sh As VARIANT, Target As VARIANT) As VOID
    If Target.Address = Sh.usedrange.Address
      ? 'Attempted to select all',Target.Address,'in sheet',Sh.Name,'Target changed'
      Sh.Cells(1,1).Select
    Endif
  Endproc

  Procedure AppEvents_SheetBeforeDoubleClick(Sh As VARIANT, Target As VARIANT, Cancel As LOGICAL @) As VOID
  Endproc

  Procedure AppEvents_SheetBeforeRightClick(Sh As VARIANT, Target As VARIANT, Cancel As LOGICAL @) As VOID
  Endproc

  Procedure AppEvents_SheetActivate(Sh As VARIANT) As VOID
  Endproc

  Procedure AppEvents_SheetDeactivate(Sh As VARIANT) As VOID
  Endproc

  Procedure AppEvents_SheetCalculate(Sh As VARIANT) As VOID
  Endproc

  Procedure AppEvents_SheetChange(Sh As VARIANT, Target As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WorkbookOpen(Wb As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WorkbookActivate(Wb As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WorkbookDeactivate(Wb As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WorkbookBeforeClose(Wb As VARIANT, Cancel As LOGICAL @) As VOID
    ? 'WorkBook closing',Datetime()
    Eventhandler(This.oHook,This,.T.)
  Endproc

  Procedure AppEvents_WorkbookBeforeSave(Wb As VARIANT, SaveAsUI As LOGICAL, Cancel As LOGICAL @) As VOID
    ? 'Attempted to save', Datetime(), 'denied silently'
    Cancel = .T.
  Endproc

  Procedure AppEvents_WorkbookBeforePrint(Wb As VARIANT, Cancel As LOGICAL @) As VOID
    ? 'Attempted to print', Datetime(), 'denied silently'
    Cancel = .T.
  Endproc

  Procedure AppEvents_WorkbookNewSheet(Wb As VARIANT, Sh As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WorkbookAddinInstall(Wb As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WorkbookAddinUninstall(Wb As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WindowResize(Wb As VARIANT, Wn As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WindowActivate(Wb As VARIANT, Wn As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WindowDeactivate(Wb As VARIANT, Wn As VARIANT) As VOID
  Endproc

  Procedure AppEvents_SheetFollowHyperlink(Sh As VARIANT, Target As VARIANT) As VOID
  Endproc

  Procedure AppEvents_SheetPivotTableUpdate(Sh As VARIANT, Target As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WorkbookPivotTableCloseConnection(Wb As VARIANT, Target As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WorkbookPivotTableOpenConnection(Wb As VARIANT, Target As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WorkbookSync(Wb As VARIANT, SyncEventType As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WorkbookBeforeXmlImport(Wb As VARIANT, Map As VARIANT, Url As String, IsRefresh As LOGICAL, Cancel As LOGICAL @) As VOID
  Endproc

  Procedure AppEvents_WorkbookAfterXmlImport(Wb As VARIANT, Map As VARIANT, IsRefresh As LOGICAL, Result As VARIANT) As VOID
  Endproc

  Procedure AppEvents_WorkbookBeforeXmlExport(Wb As VARIANT, Map As VARIANT, Url As String, Cancel As LOGICAL @) As VOID
  Endproc

  Procedure AppEvents_WorkbookAfterXmlExport(Wb As VARIANT, Map As VARIANT, Url As String, Result As VARIANT) As VOID
  Endproc
Enddefine

Cetin Basoz
MS Foxpro MVP, MCP

 

[AndrewMozley]

Thanks for your contribution Cetin. I have tried to follow your instructions and your steps 1 and 2 work fine. These displays a blank code window and the object browser window.

What do I need to do for your step 3 ("Load Excel")? I could not see a place to key in that instruction or maybe use the mouse.

What will this exercise do? Is this a way of inspecting some of the code in Excel?

Thanks. Andrew M.

 

[cbasoz]

Andrew,
Seeing that question I would say forget it.

Cetin Basoz
MS Foxpro MVP, MCP

 

[Olaf Doschke]

Andrew: With "Load excel" Cetin means to load the excel type library.

The left uppermost icon of the object browser toolbar is the "open type library" button.

There's a tab COM libraries and there's all COM type libraries listed that could be OLE automated. There's Microsfot Office 11.0 Type Library or 10, 12 or 13 depending on your office version. Check that.

Besides cecking items from the list you can also browse for a DLL, OCX or EXE file of some file containing OLE classes, you could eg click browse and then navigate to Excel.EXE and choose that.

The left upper window will then have a treeview with a root node of Excel or Office or both depening on what you choose.

Beneath that there are some nodes. Interesting onses are constants. If you drag and drop that to the empty code window you get a list of defines for Office/Excel constants.

Within the interface nodes you can drag some interface, eg _Application to a code window and get a class definition that implements (and therefore can bind to) excel application events. That's a bit advanced, an instanciation of such a class can be bound to an excel object via EVENTHANDLER() function.

Maybe that's why Cetin said "Seeing that question I would say forget it." It's advanced, but enables you to react to events happening in a Excel instance. At least the constant list is very easy to create. Save to Excel.h and use via #INCLUDE, you then for example don't need to translate excel constant names of excel code (recorded macros) into numbers.

Bye, Olaf.