MS Office Automation

I've worked with a lot of people who have wanted to automate MS Office apps. Sometimes they're trying to just write results data out to a Word doc, other times, their company builds a plug-in to an Office app and they're trying to create automated tests for that plug-in. And they all start out by pointing a commercial record & playback tool at the app and clicking that red "record" button. At best, this results in tests that click on obscurely named objects, or at worst, tests that are driven exclusively by X/Y coordinates.

You see, MS Office apps (Word, Powerpoint Excel, Outlook) are not record & playback friendly. I learned this the hard way a long time ago, and I'm hoping to spare others the same pain. You will not be able to create a reliable, robust set of MS Office tests via a record and playback tool. The only way I've found to effectively work with Office apps is via scripting. The Office object model lets you programmatically access any bit of text, cell or slide. For example, this code launches Word, opens a new document, and writes Hello:

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Add()
objDoc.Selection.TypeText("Hello")


If you try to do those same actions via record & replay, your results will be spotty at best. So when you need to work with Office apps, automate them via their object model. A little known fact is that MS ships help files with office that describe each app's object model, along with coding examples. The location of these files is [OfficeInstallDirectory]\[OfficeVersion]\1033
The app's help files are:

• Word - VBAWD10.chm
• Excel -VBAXL10.chm
• Powerpoint - VBAPP10.chm

There's also an online version here: http://msdn.microsoft.com/en-us/library/y1xatbkd%28VS.80%29.aspx

Keep these handy next time you're doing MS Office automation, and your automation efforts will be much more successful.