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 [L1 ].
You can also record a macro and then examine the code.
To record a macro
To view a recorded macro
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: [L2 ]) 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
3.8Read the type library file.
% ::tcom::import $pathToOfficeDirectory/excel9.olb
ExcelThe ::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) = 1ALX 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 FunctionI'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 ...
Hence:
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.
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) ...]