Fellow MVP, Peter Jamieson, posted this to another forum. I am not sure if
any of it is applicable to your situation, but as Peter says at the end of
the post - "Worth a try."
Quote
What happens is that when you connect via OLE DB in the first instance, and
save the document, the OLE DB connect string is set up correctly and saved.
If you re-open the document when that data source is no longer there (e.g.
has been moved) you should see:
a. the SQL Warning
b. the OLE DB dtaalink dialog box, which will probably display the existing
path.
So you change the path, and the document works OK. You are now at point (A).
So you save the mail merge main document to save the modified path.
Unfortunately, for reasons I can only guess at, when you get to point (A),
the Mailmerge data structures still reference the /old/ path. You may be
able to see this if you try that
print ActiveDocument.MailMerge.DataSource.ConnectString
statement in VBA's immediate window again. Also
print ActiveDocument.MailMerge.DataSource.Name
and
print ActiveDocument.MailMerge.DataSource.Tablename
have SQL that just references the worksheet, not the workbook pathname.
So when you save the document, the new pathname is not saved.
My guess is that this is because when Word connects via OLE DB, it uses a
thing called the Office Data Source Object (ODSO) and that in this
situation, the prompts are handled entirely by ODSO and are never "handed
back" to Word (for wahatever reason. Because Word VBA has no mechanism to
get a reference to the ODSO object, it's not possible to discover what's
going on inside it. (Well, OK, I guess someone who knows how to get the ODSO
object's address some other way could do it).
So I think what you probably need to do is...
a. open the document, and jump through the usual hoops to connect to your
data source (doesn't matter how).
b. detach the data source altogether - e.g. select "Normal Word Document"
using the first button on the MailMerge toolbar
c. select the Mailmerge document type you want again
d. re-attach the data source in the usual way
e. save the mail merge main document
f. close & re-open - you should "just" get the SQL Warning dialog.
The main snag with this sequence is that for certain types of mail merge
main document, step (c) will trigger additional dialogs that you could
probably do without, e.g. if it's Labels or Envelopes merge. The only way I
can see to avoid this is to use VBA, e.g.
Sub reconnect()
With ActiveDocument.MailMerge
' as far as I know, .Close is new in Word 2003. It seems to have the
desired effect of removing the existing data source
' without changing the type of mail merge main document, whereas the only
way to disconnect UI (new or old) is to change
' the Mail megre main document type to "Normal Word Document".
.DataSource.Close
' open the source you want
.OpenDataSource "C:\Documents and Settings\Suzanne\My Documents\Excel
Files\Rotary\Attendance.xls "
End With
End Sub
Then you need to save the mail merge main document.
Worth a try, anyway...
Unquote
--
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
Chaplain Doug said:
No long path name. The file resides in:
C:\Documents and Settings\All Users\Documents\Thank You Letters\File.csv
and similar locations on other systems tested.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org
Doug Robbins - Word MVP said:
Where is the datasource located? Is it on a network with a long path
filename?
--
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
Chaplain Doug said:
Actually Doug the dialog:
Opening this document will run the following SQL command:
SELECT * FROM [Drive:\Path\FileName].csv
does appear, but when I click "OK" it them tells me it cannot find the
file.
If I browse out to the CSV file and select it, it gives an error and
will
not connect. I tried this on another system that is running Word 2007
with
Windows XP. I got the same problem. Hence it is reproducible and
appears
to
be inhernet with Word 2007. What now???
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org
:
I am unable to replicate that problem when using Word 2007 on Vista
Business. I believe that it is unlikely that Vista Ultimate would
make
a
difference.
Are you getting the message
Opening this document will run the following SQL command:
SELECT * FROM [Drive:\Path\FileName].csv
Data from your database will be placed in the document. Do you want
to
continue?
Where is the datasource located? Is it on a network with a long path
filename?
--
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
message
Windows Vista Ultimate. Office 2007 Professional. Word 2007.
In Word 2003 I can connect my document to a .CSV file as my data
source
and
it remembers and maintains the data connection when I save the file
and
open
it later. However, in Word 2007 when I connect to a .CSV file as
the
data
source and save the file, the next time I open the file it is no
longer
connected to the .CSV file. I have to go through all the steps to
connect
to
a data source again. If I use a regular Excel file (.xls) I do not
have
this
problem. I need to be able to have this work for a .CSV file.
Why is this simple function not working in Word 2007? Is it a known
bug?
How Can I fix this issue???
Thanks for the help.
