automating mail merge in Word with Excel data source: applescript, excel, word, macintosh, AppleScript

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:

SEE CODE BELOW

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.

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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

Select all Open in new window

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation

Collaborate with the world's top tech experts

Unlimited access to our exclusive solution database

Never be left without tech help again

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.

"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin

"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Question

automating mail merge in Word with Excel data source

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press