Mail Merge Wizard Named Excel Ranges

[Michael W]

I have multiple Named Ranges in Excel that I merge with a Word template.
After using the Step by Step Mail Merge Wizard in Word 2003 I was able to
step back to Step 3 to 'Select a different list" from the Excel file.

Now in Word 2007 when I try to open the Excel file I get the error message:
Select Data Source.
<filename.xlsx>
This file is in use.
Enter a new name or close the file that's open in another program."

To merge a new name range I have to close Word and start all over again.

Whats the go?

 

[Peter Jamieson]

Yes, it's a pain. What's more, as far as I can see, it only happens with the
new .xlsx format, not the old .xls format.

What works here is either
a. open the workbook in Excel and then connect to it (but I have a feeling
that you will see other problems if you do that)
b. click Start Mail Merge|Normal Word Document to "detach" the existing
source, then open the new source. But then if you are using something like
labels you may have to do extra to get back to where you started
c. make a small VBA macro like the following in your Normal.dotm template

Sub mmclose()
ActiveDocument.MailMerge.DataSource.Close
End Sub

Assign it to the QAT in Office Button->Word Options->Customize (select
Macros in the "Choose Commands from" dropdown) or click the Customize button
in there and assign it to a keystroke.

When you run that macro, you may or may not see anything happen. e.g. the
Edit Recipients button in the Mailings tab may not be greyed out as you
might expect. But if you click it, that button, you should see a message or
the button should grey.