Merging Word with a mutiple sheets Excel file

G

Guido Rossi

Is there anyone could help me doing this:

I have a word file and I want to merge it with a 4 sheets excel file; I'd
like word to ask me to which sheet connect while opening the mail merge
file.

Sheets are all in the same format.

Thank you
Guido
 
P

Peter Jamieson

If it's Word 2002/2003, you should get a choice of sheets by default when
you set up the connection to the workbook. That's obviously not quite the
same as "when you open the mail merge file", but all you have to do is go
through the connection process again. You will lose any sorts/filters you
set up.

To automate reconnection you would need to create a VBA module in your
document. For Word 2002/2003 it can be roughly as follows:

Sub AutoOpen()

Dim strDataSourcePathName

' Substitute the pathname you need here
strDataSourcePathName = "c:\mydata\myworkbook.xls"

' Make sure the previous data source is disconnected
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
' substitute the merge type you need here
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
' Open the data source - in this case, you should only need the path name of
the .xls
ActiveDocument.MailMerge.OpenDataSource _
Name:=strDataSourcePathName

End Sub

If it's Word 2000/97, things are more difficult because the default
connection method (DDE) does not let you choose a sheet, and the ODBC method
forces you to check various options in its dialog before you can choose a
sheet. Manually, it might be best to reconnect using the "Excel Converter".
Worse, using code you can probably only connect to ODBC data and I am fairly
sure that you have to specify the sheet name - you cnanot leave it up to the
user to choose.

Peter Jamieson
 
E

ElleEm

Peter,
I am having a similiar problem that changing the options and following the
directions you mention in your previous reply this thread doesn't seem to
help. My new computer came with Office Pro 2003.

It seems that when trying to set up connection between the word doc and the
xls file, it's dividng up my xls file into several html files. None of the
names match the sheets in the workbook. I can't make it let me choose the
sheet I need for anything. And it's ruining the original xls file in the
process. I open it up independently and all the data is completely gone,
apparently moved to html files in another folder?This is insane. I've had
to retrieve it from a back up several times now and it's getting old real
fast. Doesn't seem to matter how I try to connect them, through the wizard,
the tool bar, whatever.

I used mail merge with word and excel 2000 quite a bit and I never ran into
anything even close to this. I just want to have the merge fields show up in
a word doc from a particular worksheet within the file., no complicated
filtering or anything like that. It's very likely I am clueless and missing
some obvious change between the old and new, but that doesn't help me be
any less frustrated. Any ideas?

And you say we have to do some VBA coding to just keep the files connected
with each other now, which Word 97 and 2000 seemed to do on their own with
appararently no problem? That's not what I'd call very user friendly. Please
tell me your kidding.

Mail merging was the only reason I even got Office on this new computer, but
if I have to dig up my old office 2000 and install it just to have basic
mail merge, I'm sure you'll see the mushroom cloud from where ever your are
as my head implodes, hehehe.

-LM
 
D

Doug Robbins - Word MVP

You are obviously doing something different to what thousands of other
people are doing. Try using the original 3-Step Mail Merge Helper which you
can get access to by selecting Customize from the Tools menu and on the
Commands tab, select the All Commands category and then scroll down through
the list of Commands until you come to the Mail Merge Helper item and then
left click on it and drag it onto a toolbar or the expanded Tools menu.

There is also a Mail Merge Toolbar that can be activated by selecting
Toolbars from the View menu and then checking the Mail Merge item.

Forget what Peter said about VBA, that was for circumstances that are
different from yours.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
Top