Updated 2013-01-20 19:33:26 by RLE

One place to start is by Googling "$app_name object model". The (full) help for most Microsoft applications includes this information, often disguised as instructions on use of Visual Basic for Applications or VBScript. These reference guides usually are not installed by default during the typical installation. They can be added later by rerunning the setup program with the custom install option. Also, one can often start the application in question, and select Tools/Macros/Visual Basic Editor, then View/$app Explorer, and the API should be within a click or two more. This process is documented in Microsoft's knowledgebase [1].

You can also record a macro and then examine the code.

To record a macro

  • On the Tools menu, point to Macro and click Record New Macro.
  • Type a name for the new macro in the Macro name box and click OK.
  • Perform the actions you want to learn more about.
  • When you have completed the actions you want to record, on the Stop Recording toolbar click Stop Recording.

To view a recorded macro

  • On the Tools menu, point to Macro and click Macros .
  • Find and click to select your recorded macro in the list of available macros.
  • Click Edit.

Also, Chin Huang notes, "you can consult the MSDN library at http://msdn.microsoft.com/library/default.aspx In the left tree, open Office Solutions Development -> Microsoft Office -> Microsoft Office 2000 -> Microsoft Office 2000 Language Reference -> Microsoft Excel 2000 Reference."

How to Obtain Built-In Constant Values for an Office Application

The article http://support.microsoft.com/support/kb/articles/Q239/9/30.ASP (Link broken, it might be this one: [2]) describes how to obtain the constant values for Office applications from type library files. Here's an example of how to do it in Tcl:

Load the tcom extension.
    % package require tcom

Read the type library file.
    % ::tcom::import $pathToOfficeDirectory/excel9.olb

The ::tcom::import command put the constant values into some arrays. It returns the namespace where it created the arrays.
    % info vars ::Excel::*
    (many array names listed)
    % parray ::Excel::XlRowCol
    ::Excel::XlRowCol(xlColumns) = 2
    ::Excel::XlRowCol(xlRows)    = 1

ALX on 22 Sep 2007: See also Howto find com object enums for tcom using registry.

How to Generate VBA Code Handling The Constant Values for an Office Application

Brian Passingham on 20 Feb 2007 : The 'tcom::import' mechanism shown above can also be used to fill in on a gap in Visual Basic for Applications, which apparently does not support any mechanism for retrieving the literals of enumeration values, even though the Office applications themselves often display these names to the end user. So people often develop code like the following:
  Function EnumNameOfXlPivotFieldOrientation(ByVal x As XlPivotFieldOrientation)
    Dim res As String
    Select Case x
    Case 1
         res = "xlRowField"
    Case 0
         res = "xlHidden"
    Case 3
         res = "xlPageField"
    Case 4
         res = "xlDataField"
    Case 2
         res = "xlColumnField"
    Case Else
         res = "Not found " & x
    End Select

    EnumNameOfXlPivotFieldOrientation = res
  End Function

I'm trying to analyze the structure of a lot of spreadsheets at the moment, but didn't want to spend a few days writing several of these ...

  package require tcom

  # Revise the paths below to do the same for other applications
  tcom::import {C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE}

  set vb [open excelenums_vb.txt w]
  puts $vb {' This module contains a large number of functions for converting
  ' Excel enumeration types into their string literals.
  ' If so desired, a similar set of conversion routines could be produced very rapidly.
  ' The code derives its data from the .COM model for version 11 of Excel, as found
  ' in Office 2003 Professional.
  ' Author: Brian Passingham}
  puts $vb "' Date [clock format [clock seconds] -format "%D"]\n\n\n"

  foreach tname [info vars ::Excel::*] {
    # Ignore the __ id array and the mixed 'Constants' - generate a Sub for every other enumeration
    if {![regexp {__} $tname] && [regexp {::(\w+)$} $tname void typeName] && ![string equal $typeName Constants]} {
        puts $vb "Function EnumNameOf${typeName} (ByVal x As ${typeName})"
        puts $vb {    Dim res As String

    Select Case x}

        foreach {key value} [array get $tname] {
            puts $vb "    Case $value"
            puts $vb "         res = \"$key\""

        puts  $vb {    Case Else
         res = "Not found " & x
    End Select
        puts $vb "    EnumNameOf${typeName} = res\nEnd Function\n\n"
  puts $vb "' End of file"

  close $vb

I'm glad I wrote that and tested one generated Sub, rather than hand-producing the 4900 lines of VBA it produced. Meta-programming/code generation can be your friend in a lot of cases ...

By using optcl you can browse the interfaces of loaded com objects with:
 package require optcl
 tlview::refview .ref
 tlview::loadedlibs .libs

LES on April 28, 2004: optcl tells me that tlview::refview is an invalid command. ???

CL on 6 August 2004: optcl, and [refview] in particular, are doing great things for me, although I still have a lot to learn.

On the 13th, CL observes that it ought to be a nice project to rewrite these in tcom, mainly in recognition that the latter is more widely deployed.

In 2005, CL observes that he gets all the information he needs from ::tcom::info.

JMM, in 2006, optcl 3.0 also tells me that tlview::refview is an invalid command. However, tlview::libs works, e.g.
 tlview::libs .ref

Separate question: how does one locate type libraries? CL doesn't know, and does much trial-and-error. TP seems to find some answers in the registry.

http://www.mvps.org/skp/vba.htm has examples of [VBA] controlling PowerPoint.

phk Sometimes an object browser can help.

  • ObjectScope [3]
  • OleView (OLE Object Viewer): included in both MS Visual Studio and the Platform SDK
  • Visual Basic Object Browser: part of the Visual Basic environment.
  • application specific e.g. Word VisualBasic-Editor > Object Catalog

COMet is a COM Explorer for Tcl. It's a tcom based application to discover and interactively program COM objects.

[... Open MS Word and press ALT + F11 (for the VBA environment), then F2 (for the object browser) ...]