Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
harryedwards
 
Posts: n/a
Default Word 2002 cannot find its Excel data source for mail merge

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.
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Charles Kenyon
 
Posts: n/a
Default Word 2002 cannot find its Excel data source for mail merge

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(wdWorkgroupTem platesPath)
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="""";Us er 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

--
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.

"harryedwards" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
harryedwards
 
Posts: n/a
Default Word 2002 cannot find its Excel data source for mail merge

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.

"Charles Kenyon" wrote:

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(wdWorkgroupTem platesPath)
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="""";Us er 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

--
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.

"harryedwards" wrote in message
...
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.




  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Charles Kenyon
 
Posts: n/a
Default Word 2002 cannot find its Excel data source for mail merge

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/...csIn15Mins.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.
--
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.


"harryedwards" wrote in message
...
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.

"Charles Kenyon" wrote:

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(wdWorkgroupTem platesPath)
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="""";Us er 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

--
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.

"harryedwards" wrote in message
...
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.






  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor
 
Posts: n/a
Default Word 2002 cannot find its Excel data source for mail merge

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

--

Graham Mayor - Word MVP

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


harryedwards wrote:
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.





  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
harryedwards
 
Posts: n/a
Default [SOLVED] Word 2002 cannot find its Excel data source for mail merg

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

"Graham Mayor" wrote:

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

--

Graham Mayor - Word MVP

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


harryedwards wrote:
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
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to open excel data source for word mail merge Trisha Microsoft Word Help 0 October 18th 05 10:59 AM
insert Excel data into Word Mail Merge document RRemy Mailmerge 2 June 25th 05 04:52 PM
Underscore (_) will not always display in RTF files (Word 2002). David A Edge Microsoft Word Help 6 June 14th 05 10:39 AM
Mail Merge Issue With Office 97 - Excel Data Source Matt Thorley Mailmerge 1 February 15th 05 11:38 PM
Word Field Codes in Excel data file mranz Mailmerge 7 December 11th 04 09:02 AM


All times are GMT +1. The time now is 08:48 PM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"