?
Solved

How to get data from multiple sheets an in .xlw into mail merge

Posted on 2011-09-14
5
Medium Priority
?
204 Views
Last Modified: 2012-05-12
I have office 2003 and am trying to create a mail merge in publisher from excel.

The excel sheet is in .xlw opposed to .xls - there is no difference that my eye can see but I haven't really dug too deep.

When I try to mail merge into publisher, it's asking for a specific database type and won't accept the .xlw or any other format I am able to "Save as" from excel.

The excel workbook has multiple pages and I'd like to be able to pull from them independently, as online instructions say I can.

I was getting a verbose error message that after research, had me install a Microsoft database tool update, now I get a "cannot open data source" error

It seems like the mail merge tool only wants to open .dbx or odbx file types.

How can I get the data from this excel sheet into publisher?
0
Comment
Question by:downsouf912
  • 2
4 Comments
 
LVL 23

Expert Comment

by:Danny Child
ID: 36539448
I've done merges from Excel 2003 into Word successfully in the past, but it's quite fiddly.  I'd suggest that you do a Save As to XLS in the first place.  I also recall that it was best to actually have the Excel sheet CLOSED when the merge runs.  

I've got some further notes at work which I can get tomorrow on the details of the procedure....

Is it essential that you use Publisher as well?
0
 

Author Comment

by:downsouf912
ID: 36542605
I'm not sure I can get out of publisher, the document is a newsletter that was designed in publisher and when I try to save as another file format, it changes the formatting of the document.

I had tried saving as a .xls but got the same error when trying to create the mail-merge.
0
 
LVL 23

Accepted Solution

by:
Danny Child earned 1000 total points
ID: 36915911
Finally got back to this - here's my crib sheet on the process for Word and Excel 2003.  Hope it's not too abbreviated....

get the addresses all sorted, in Excel - only one line per cell.
Save the changes, and CLOSE Excel.
Word, new blank doc - Tools.. Letters And Mailings.. Mail Merge..
choose Labels from the menu on the right, and click Next - Starting Document.
leave it on Change Document Layout, but click Label Options below
choose the label, for instance -set Label Products to be Avery A4 and A5 Sizes, then set the product number to be L7163, and OK - then, Next - Select Recipients.  
leave it on Use An Existing List, and click on Browse.. and find the main Excel document.  (ignore the scary My Data Sources, and NEW SQL Server Connection options - just browse for the Excel file above).
Once opened, it may offer a Select Table window - just leave this on the defaults and OK.  Leave the list of recipients alone, and click OK.  Click on Next: Arrange Your Labels.  
click on Address Block to ensure that the layout is good.  
Address Block.. Match Fields button.. <<<this bit is important!
Change any Not Matched to have the right entry.

FINALLY, click on Update All Labels - *important* - then Next - Preview Your Labels
and choose the Edit Individual Labels option to get the output.
Note - you'll get 2 documents out of this - a one page master showing the layout, and also one with the results - multiple pages.  
0
 
LVL 50
ID: 37412242
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question