Excel 2007 Sheet as a Data Source For Word 2007 Mail Merge

[HoosierMama]

Hi All,
I have data on two sheets of a workbook which are joined by a VLOOKUP. I have the data I want to use in a mail merge on one of the two sheets in this workbook, but, when I create a new data source for my Word Merge using ODBC data sources, the workbook only contains one sheet, and it happens to be the wrong one. It never shows me multiple sheets to choose from, and I have tried copying and pasting the data but since it is based on formulas that reference another worksheet, I don't end up with what I want. I have tried saving the sheet as a CSV file and then reopening it and saving it as an Excel workbook, but then I see no data in the word merge at all. Again, to summarize, I want to use EXCEL1 containing sheet1 and sheet2 for a mail merge. When I create the data source, I only have one sheet available in the merge - sheet1 - and darnit what I need is on sheet2. The last thing I want to do is manually enter all the data, but, it is seeming more and more that is my only option.
Thanks for any help you may be. Excel 7, Windows 7, Word 7.
Wendy

 

[DrNicket]

I have a similar situation.


I'm creating multiple separate Mail merge templates.

My goal would be to use:
a single macro-enabled Excel workbook (.xlsm),

have a different sheet for each template data source,

and

a different macro-enabled Word document (.docm) for each e-mail template
(since Word doesn't appear to have separate sheets within a single file like Excel does).

However Word does not appear to support sheets beyond the first as a data source.


Any solution to this?


EDIT: To clarify, Each .docm would only ever need to reference data on one particular sheet, never across 2 or more. I just want to be able to reference one workbook, and select which sheet to source my data from for that particular .docm.

 

[DrNicket]

Update: The issue was when selecting a Sheet, it only showed the first sheet (named) and the second sheet as 'Sheet1$' Despite being named also, saving and closing all documents and reopening only word. Later, it just miraculously showed properly.

Why didn't it update in a timely manner? What happened?