Interact with External Programs

by Peter on February 28, 2013

in User Reference

With version 5 of XpressDox it is possible to instantiate COM (Common Object Model) and .NET objects and address their methods and properties.  There are a huge number of applications which are exposed via the COM mechanism, including all the Microsoft Office modules.

The functions which implement these features are CreateObject, GetObjectValue, GetProperty, InvokeMethod and SetProperty.

These can best be explained by way of examples (the wizards in the Command Editor have more instruction on the syntax of the functions).

COM – Create a spreadsheet

This example demonstrates creating a spreadsheet with two rows and one column. It assumes that the template author is aware of the Microsoft Office COM interface – this is what is available to VBA programmers. One way to see what the method and property names are is to record a macro in Excel, and see what VBA code is produced.

Here is what the XpressDox functions to create a small spreadsheet would look like:

«CreateObject(‘ExcelApp’, ‘Excel.Application:’)»
«CreateObject(‘Sheet’,‘ExcelApp:WorkBooks.Add.WorkSheets’,’Item’,1)»
«InvokeMethod(‘Sheet:Range(A1).Select’)»
«SetProperty(‘ExcelApp:ActiveCell.FormulaR1C1’,’Date’)»
«InvokeMethod(‘Sheet:Range(A2).Select’)»
«SetProperty(‘ExcelApp:ActiveCell.FormulaR1C1’,’2013-01-21’)»
«InvokeMethod(‘Excel.Application:ActiveWorkbook.SaveAs’,ExcelFileName)»
«InvokeMethod(‘Excel.Application:Quit’)»

The first CreateObject function creates an object reference in XpressDox called ‘ExcelApp’.  The COM object which it instantiates is an object with ProgID of  ‘Excel.Application’ – this is the ProgID of Excel (as defined by Microsoft).  After this CreateObject has executed, reference can be made to ‘ExcelApp’ instead of ‘Excel.Application’, so it is really something like a shortcut reference to ‘Excel.Application’.

The second CreateObject creates an object reference called ‘Sheet’.  The object that this refers to is the first worksheet (‘Item’,1) in a new WorkBook.  (The string of object members Workbooks.Add is what creates a new workbook, and the WorkSheets property is a collection of the worksheets in that new workbook).

The first two InvokeMethod functions cause the relevant cell in the worksheet to be referenced as the “active” cell.  Notice how the object reference (in this case ‘Sheet’) comes first, and is followed by a colon (:).  This is purely a syntactic device to help XpressDox differentiate between an actual ProgID and one of its own internal object references.

The SetProperty functions set the values of the first column in the first and second rows to ‘Date’ (i.e. a heading) and ‘2013-01-21’ respectively.

Then, the last two InvokeMethod calls result in this little spreadsheet being saved and the Excel application is exited.  The reason for using  ‘Excel.Application’ instead of ‘ExcelApp’ in these two examples is purely illustrative.  The effect is exactly the same.

.NET – Accounting Example

Suppose you have an accounting system that is written in .NET and exposes a number of classes that are used to implement its features.  For example, you might like to write a letter requesting payment, and post a fee to an account when you do that.  Here’s a snippet of a template which would achieve that:

«CreateObject(‘Account’,’o2Smart.AccountingSystem.Account, o2Smart.Accounting’,’ctor’,AccountNumber)»
«SetVr(‘Contact’,GetProperty(‘Account:PrimaryContact’))»
«GetV(‘Contact’)»
«InsertFormattedText(GetProperty('Account:BillingAddress'),Lines)»

Dear  «GetV(‘Contact’)»

The balance on your account is «CurrencySymbol»«FormatNumber(GetObjectValue(‘Account:Balance’))»

Yours faithfully

«InvokeMethod(‘Account:PostFee’,GetObjectValue(‘Account:FeeForLetter’))»

CreateObject creates an instance of an Account object by using the constructor which takes the value of data element AccountNumber as a parameter.  (Note that with .NET it is possible to create objects directly in this way, using the ‘ctor’ member name, whereas with COM it is not possible to instantiate a COM object from its ProgID while passing parameters to a constructor). The second parameter is an instance of an Assembly Qualified Name – the part before the comma is the namespace and name of the class, the part after the comma is the assembly (DLL) name.

Notice how an XpressDox variable can be set to the value of a property (PrimaryContact).

The last function will read the value of FeeForLetter (which can be either a property or a method), and send that to the PostFee method of the Account object.

Formatting and Layout

The functions CreateObject, InvokeMethod and SetProperty all return a value which will cause XpressDox to remove their containing paragraph from the merged document (this is similar to the functioning of SetVr and related functions).  This was done to help the authoring of templates which use these features and not have to pepper the templates with «RemoveParagraph()» commands.

The above is true even if the method invoked with InvokeMethod is a function that returns a value.  If that value of that function is required in the document, then GetObjectValue should be used rather than InvokeMethod.