In order to run Graham's macro successfully, you will probably need to
apply the registry change described in
http://support.microsoft.com/kb/825765/en-us
For each document, if the data source no longer exists, you will not be
able to get any information about it by opening the .doc, because Word
will force you to discard the data source or provide another one. After
that, VBA will not let you see the details of the old data source.
For documents with other formats such as .docx/.docm (Word 2007), and in
some cases, .rtf, .xml, .html, you may be able to find the data source
details by opening the files outside Word - e.g. you can open .rtf, .xml
and .htm in Notepad. .docx and .docm files can be renamed to .zip and
the internal .xml files examined.
If all these documents connected to a .mdb which has now gone, or which
does not have the same query/table names in it, your task will be made a
lot easier if you can restore the old .mdb temporarily. If you do that,
I strongly recommend that you extract the following pieces of
information for each data source:
oDoc.MailMerge.DataSource.Name
oDoc.MailMerge.DataSource.Connectstring
oDoc.MailMerge.DataSource.Querystring
It wouldn't do any harm to extract
oDoc.MailMerge.DataSource.Tablename
either.
The chances are that all the mail merge main documents that connect to
your Access database have the same or similar COnnectstring, but if for
some reason, some connections needed to use the DDE method (e.g. because
they reference parameter queries), each connection string will be different.
The querystring will typically be something like
SELECT * FROM `tablename`
but if the user (or programmer) has set up any sort/filter criteria, the
Querystring embodies those criteria and will help you set up the mail
merge main document with the new table name and the same criteria.
Peter Jamieson
http://tips.pjmsn.me.uk
Alex Hammerstein wrote:
Hello Graham
Thank you so much for the macro and for your help - it is much appreciated.
I am having a slight problem.
I have created a folder and placed all the Word files into this folder along
with a word file called test, and which contains the macro.
I open this file and run the macro.
I then get a message that Word is trying to run the SQL command to select
the data. If I select yes, I am getting and error:
Microsoft Visual Basic
Run-time error 5398
The operation is cancelled
(W:\checklistinvoice * IT e-mail.doc)
It started debugging and highlighted the following line in yellow:
Set oDoc = Documents.Open(strPath & strFile)
I subsequently stopped the debugging.
If I select No, The word file being checked is listed on the test document
and shown as:
F:\mail merge letters\College Results\failed 1st attempt - 3pl.doc
Not a merge document
Have I done something wrong?
Alex
On 23/01/2009 14:15, in article ,
"Graham Mayor" wrote:
If you put the documents in a separate folder and run the following macro,
then provided the documents are not password protected, the macro should
list the documents and their associated data sources in a new document. If
no data source is attached, the macro reports that also. Any automacros in
the documents are temporarily disabled and the documents are not altered..
Sub ListDataSources()
Dim strFile As String
Dim strPath As String
Dim oDoc As Document
Dim oTarget As Document
Dim iFld As Integer
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
With fDialog
.Title = "Select Folder containing the documents and click OK"
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
If .Show -1 Then
MsgBox "Cancelled By User"
Exit Sub
End If
strPath = fDialog.SelectedItems.Item(1)
If Right(strPath, 1) "\" Then strPath = strPath + "\"
End With
WordBasic.DisableAutoMacros 1
If Documents.Count 0 Then
Documents.Close SaveChanges:=wdPromptToSaveChanges
End If
Set oTarget = Documents.Add
strFile = Dir$(strPath & "*.do?")
While strFile ""
Set oDoc = Documents.Open(strPath & strFile)
oTarget.Activate
Selection.TypeText oDoc.FullName & Chr(11)
If oDoc.MailMerge.MainDocumentType wdNotAMergeDocument Then
Selection.TypeText "Datasource: - " & oDoc.MailMerge.DataSource.name
Else
Selection.TypeText "Not a merge document"
End If
Selection.TypeParagraph
oDoc.Close SaveChanges:=wdDoNotSaveChanges
strFile = Dir$()
Wend
WordBasic.DisableAutoMacros 0
End Sub
http://www.gmayor.com/installing_macro.htm