Word 2002 cannot find its Excel data source for mail merge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

harryedwards - 22 Nov 2005 11:43 GMT

Having recently got my computer back after a crash - and with no change in
the software being used - my mail merge documents have problems.

Now, when opening the document, I get a box saying: '[Word doc name] is a
mail merge main document. Word cannot find its data source, [Excel file
name].' Both Word and Excel file are in exactly the same place as they were
before. I use the 'Find Data Source' option to try and connect to the Excel
file again, but the box I get offers me options like '+ New SQL Server
Connection' rather than simply the file itself as before. I can browse to the
file from here and select it, but then I have to select a sheet from it -
something that has never happened before - and once I've done that the data
source is connected. However, this now means that I have to use US dates
rather than UK as query options, any zero value is now 12.00AM, and the
merged document won't include things like £ signs which appeared in the Excel
sheet. In short, it messes everything up. Oh, and even if I now save the
document with the newly connected data source, it won't be connected next
time I open it and I'll have to go through it all again.

As you may have worked out, all I want is for everything to be like it was
before! Why has it changed and what can I do about it?

Thanks in advance.

Reply to this Message

Charles Kenyon - 22 Nov 2005 20:57 GMT

What I did about it was to reformat my documents as normal (rather than
merge) documents. I have a macro (attached to a button on my merge toolbar)
that converts it back to a merge document and attaches my database (at the
correct sheet). (It also calls up the query box so I can find the record I
want.)

Function WorkGroupPath() As String
' Written by Charles Kenyon
' February 28, 2003
'
' Used by templates menus to set location of templates.
' Returns workgroup tempates path with "\" at the end.
'
' This is needed because if the folder is a network drive rather
' than a folder, it will have the "\" already. If it is a folder,
' it will not have the backslash. This function gives a string
' with the backslash in either case.
'
WorkGroupPath =
Application.Options.DefaultFilePath(wdWorkgroupTemplatesPath)
If Right(WorkGroupPath, 1) <> "\" Then
WorkGroupPath = WorkGroupPath & "\"
End If
End Function

Sub AttachClients()
' Written by Charles Kenyon
' 19 April 2005
'
' Requires WorkGroupPath function
'
' Makes activedocument a mailmerge (letter) document and
' attaches Clients_Merge.xls from Parts folder of Workgroup Templates
folder.
'
' Then displays search dialog and goes to client, makes sure merge info is
' displayed instead of merge codes.
'
On Error Resume Next
'
' Name of file
Dim strFileName As String
Dim strProvider As String
strFileName = WorkGroupPath & "Parts\Merge Data\Clients_Merge.xls"
'
' Attach Merge list
ActiveDocument.MailMerge.OpenDataSource strFileName, , , False, _
True, False, "", "", False, "", "",
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=strFileName;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet
OLEDB:" _
, "SELECT * FROM `Clients$`", "", , wdMergeSubTypeAccess
'
' Show merge data
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
'
' Find client
Application.Dialogs(wdDialogMailMergeFindRecipient).Show
End Sub

Signature

Charles Kenyon

Word New User FAQ & Web Directory: http://addbalance.com/word

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide) http://addbalance.com/usersguide

See also the MVP FAQ: http://word.mvps.org/FAQs/ which is awesome!
--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

> Having recently got my computer back after a crash - and with no change in
> the software being used - my mail merge documents have problems.
[quoted text clipped - 23 lines]
>
> Thanks in advance.

Reply to this Message

harryedwards - 22 Nov 2005 22:50 GMT

Thanks a lot for that, but you're going to have to spell it out for me like
I'm a three-year-old, I'm afraid: I have no idea about macros. What exactly
do I have to do?

Sorry to be a nuisance.

> What I did about it was to reformat my documents as normal (rather than
> merge) documents. I have a macro (attached to a button on my merge toolbar)
[quoted text clipped - 83 lines]
> >
> > Thanks in advance.

Reply to this Message

Charles Kenyon - 23 Nov 2005 04:04 GMT

Sorry.

The first is a function. When called from another macro it comes back with
the text of the workgroup path with a "\" at the end of it. I store my data
file in a folder in the workgroup path. If I ever change my computer setup,
so long as my data file stays in the same relationship to the workgroup
path, I'll be able to use my macro without rewriting it.

The second macro is the one that is attached to my merge toolbar button. (It
is also called by an AutoNew macro whenever a new document is created from
one of my merge templates that uses this database.)

Assuming that you have your data file in a folder in your workgroup path,
you would modify the statement:
strFileName = WorkGroupPath & "Parts\Merge Data\Clients_Merge.xls"
to reflect the folder and file names.

It should work for you, then.

When I do merges, I usually do them for a single client/case. If you are
doing full merges, you would want to skip the following two lines:
' Find client
Application.Dialogs(wdDialogMailMergeFindRecipient).Show

Here are some links to help you get started on programming.
http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm
http://word.mvps.org/FAQs/MacrosVBA/VBABasicsIn15Mins.htm

Sorry. I don't have the time right now to explain more. Hope it helps.
Otherwise, you may want to wait for an answer to your original question. I
would guess one will be posted. There are some very knowledgeable people who
answer questions on this forum.

Signature

> Thanks a lot for that, but you're going to have to spell it out for me
> like
[quoted text clipped - 103 lines]
>> >
>> > Thanks in advance.

Reply to this Message

Graham Mayor - 23 Nov 2005 06:58 GMT

See the Excel data section of
http://www.gmayor.com/mail_merge_labels_with_word_xp.htm which will help
you get it back as before.

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> Having recently got my computer back after a crash - and with no
> change in the software being used - my mail merge documents have
[quoted text clipped - 21 lines]
>
> Thanks in advance.

Reply to this Message

harryedwards - 23 Nov 2005 11:09 GMT

Thank you both, Charles and Graham, very much - it's back how it was now.

> See the Excel data section of
> http://www.gmayor.com/mail_merge_labels_with_word_xp.htm which will help
[quoted text clipped - 25 lines]
> >
> > Thanks in advance.

Reply to this Message