Server Side Office Automation using ColdFusion

Tips after fighting to get robust Office Automation using ColdFusion 9.


Definitions
COM: Microsoft Component Object Model.
Automation: the process of launching another application and controlling it programmatically through a public interface.

Environment
ColdFusion 9
Office 2007 SP2

Tips

• Pay attention to Office version (down to minor rev numbers), I encountered several major differences  between Office 2007, and Office 2007 SP2 (no to mention the differences between 2003 and 2007)
• Server setup
• CF service account must be interactive
• Office installation need to select option to install all features locally
• After Office installation, need to login as service account, and start Office applications and click through license agreement, registration, ...
• Open Office applications with Visible=false may have negative impact on it's behavior (At least for Powerpoint, I found some layout options are not functioning correctly if application is started in invisible mode. Specifically, I was trying to set text box property: "Shrink text on overflow", by setting AutoSize=2 (msoAutoSizeTextToFitShape), it won't work unless Powerpoint application is created in visible mode.)
• Before even start ColdFusion code, I usually test my script using VBS, then translate to CFScript, however, CFScript have some minor difference from VBS:
• VBScript can use array notation or foreach on some collections, but CFScript  usually has to access the 'items' accessor
• Explicit type conversion may be necessary in some COM function calls, use "JavaCast". In my case, I got this error message:

An exception occurred when executing a COM method. 
The cause of this exception was that: AutomationException: 0x80048240 - Item 1 not found in the Designs collection. in 'Microsoft Office PowerPoint 2007'. 

Code causing the problem: objPresentation.Designs.Item(1)
Reason: ColdFusion is not type safe. COM definition for this input parameter is variant type. Seems like ColdFusion trying to send it over in string format "1" instead of numeric value 1.
Solution: objPresentation.Designs.Item(JavaCast("int", 1))

• VBScript can use enumerates just by name, CF does not have that luxury, you have to find out the numeric values for each of the enumerations, please see my other post for list of some numeric values: Some Enum Values for Office COM Object.
• To find out numeric values, I used Visual Studio to add reference to Office library, then I can easily navigate or search for enums
• One way to implementation robust automation:
• Main CFC
• Create a worker thread to do automation task
• Wait for the thread with timeout
• After wait is finished, if it's timeout, will kill automation application, kill thread, and cleanup, else we have successfully done!
• Automation thread
• Global lock on Office Application name (avoid concurrency issue)
• Create automation object
• <<do you thing>>
• quit automation object
• ReleaseCOMObject
• cleanup
• To cleanup, I use "pskill" to kill office processes, so that a single failed call won't block all future automation attempts
• Code segment to create automation object

Server Side Office Automation Problems

• Office applications are not designed for server environment
• Concurrency (Office applications are non-reentrant, STA based automation, use global shared resources)
• Scalability
• Security
• May lock up on launch or any point during execution if there are modal dialog popup (typically you will see license agreement, error messages, “install on first use”, … dialogs)
• License considerations: each client must have licensed copies of Office
• Officially discouraged by Microsoft

“Microsoft does not recommend or support server-side Automation of Office”

Alternatives

• CFSpreadsheet
• HTML/XML based format for word and excel
• OOXML (Open XML file format)
• Excel Services (Since Sharepoint Server 2007)
• Word Automation Services (Since Sharepoint Server 2010)
• Server libraries: Apach POI, Microsoft Open XML SDK

Reason to Stick with Automation Approach

• After the above "Problems" section, the biggest incentives to use automation are
• Need to invoke Excel macro or solution package on server side
• You already have some fancy VBS code that can do what you wanted through Office automation

References

Adobe. 2011. ColdFusion Developer’s Guide. Last Accessed April 19, 2011. http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Part_4_CF_DevGuide_1.html

Forta, Ben and et al. 2005. Advanced Macromedia ColdFusion MX 7 Application Development, Chapter 26 Extending ColdFusion with COM. Macromedia Press. http://www.forta.com/books/0321292693/0321292693_chapter26.pdf
Microsoft. 2011. KB 257757: Considerations for server-side Automation of Office. Last Accessed April 19, 2011. http://support.microsoft.com/kb/257757