automating mail merge in Word with Excel data source
I have a Microsoft Word document that is a mail merge, with an Excel workbook as the data source. I'm trying to automate the opening of this document. This seems like such a basic request, and there ought to be a way to do it, but I've tried many things without success. Any help would be appreciated. I'm running Mac OS X 10.4.11, Word 2004 11.5.3 and Excel 2004 11.5.4.
When I open the document directly from Word, I get an "Open Workbook" dialog, asking for two things: the worksheet to be used as the data source, and the cell range. The AppleScript needs to be able to get through this dialog automatically, without the user having to do any clicking.
My first attempt was this:
*tell application "Microsoft Word" to open file name "MyMailMerge.doc"*
But when I do this, the "Open Workbook" dialog still appears, just as if I had opened it directly from Word. The AppleScript hangs on the above command until the user manually selects the worksheet and cell range, so there is no opportunity for the script to negotiate the dialog through GUI scripting.
So it seems I need to do the whole thing through GUI scripting. But with GUI scripting, I'm not even able to get through the "Open: Microsoft Word" dialog. Below is my attempt, with some extraneous detail removed:
*tell application "System Events"*
*tell process "Microsoft Word"*
*keystroke "o" using command down*
*repeat until exists window "Open: Microsoft Word"*
*end repeat*
*set value of text field "search" of group 1 of window "Open: Microsoft Word" to "MyMailMerge.doc"*
*repeat until exists radio group 1 of group 1 of window "Open: Microsoft Word"*
*end repeat*
try
*tell UI element 2 of radio group 1 of group 1 of window "Open: Microsoft Word"*
*set {xPosition, yPosition} to position*
*set {xSize, ySize} to size*
*end tell*
*-- modify offsets if hot spot is not centered:*
*click at {xPosition + (xSize div 2), yPosition + (ySize div 2)}*
*end try*
*end tell*
*end tell*
The goal of the "click at" command is to click the UI element marked "Computer", so that it searches the entire computer for the file "MyMailMerge.doc". I know I'm getting the right UI element, because if I display the variables xPosition, yPosition, xSize and ySize, they are the right values, as shown by the UI Browser in Screen Reader mode when I hover over that UI element.
But the "click at" command has no effect. Another choice (Folder "xxx") remains selected, so the script cannot proceed. If I manually click on "Computer", it works fine.
Any help would be appreciated. It really shouldn't be this hard to automate a mail merge.
When I open the document directly from Word, I get an "Open Workbook" dialog, asking for two things: the worksheet to be used as the data source, and the cell range. The AppleScript needs to be able to get through this dialog automatically, without the user having to do any clicking.
My first attempt was this:
*tell application "Microsoft Word" to open file name "MyMailMerge.doc"*
But when I do this, the "Open Workbook" dialog still appears, just as if I had opened it directly from Word. The AppleScript hangs on the above command until the user manually selects the worksheet and cell range, so there is no opportunity for the script to negotiate the dialog through GUI scripting.
So it seems I need to do the whole thing through GUI scripting. But with GUI scripting, I'm not even able to get through the "Open: Microsoft Word" dialog. Below is my attempt, with some extraneous detail removed:
*tell application "System Events"*
*tell process "Microsoft Word"*
*keystroke "o" using command down*
*repeat until exists window "Open: Microsoft Word"*
*end repeat*
*set value of text field "search" of group 1 of window "Open: Microsoft Word" to "MyMailMerge.doc"*
*repeat until exists radio group 1 of group 1 of window "Open: Microsoft Word"*
*end repeat*
try
*tell UI element 2 of radio group 1 of group 1 of window "Open: Microsoft Word"*
*set {xPosition, yPosition} to position*
*set {xSize, ySize} to size*
*end tell*
*-- modify offsets if hot spot is not centered:*
*click at {xPosition + (xSize div 2), yPosition + (ySize div 2)}*
*end try*
*end tell*
*end tell*
The goal of the "click at" command is to click the UI element marked "Computer", so that it searches the entire computer for the file "MyMailMerge.doc". I know I'm getting the right UI element, because if I display the variables xPosition, yPosition, xSize and ySize, they are the right values, as shown by the UI Browser in Screen Reader mode when I hover over that UI element.
But the "click at" command has no effect. Another choice (Folder "xxx") remains selected, so the script cannot proceed. If I manually click on "Computer", it works fine.
Any help would be appreciated. It really shouldn't be this hard to automate a mail merge.
PowerPC G4, Mac OS X (10.4.11)
