Datasource

[Alex Hammerstein]

Hi

With Mailmerge documents in Word 2007 is it possible to establish the
datasource for the mail merge, without having to open up the Word document?

Many thanks

Alex

 

[Graham Mayor]

You can create a data source (e.g. with Excel or Access - or even from the
mailings tab (Select Recipients) of Word 2007 itself) without opening the
document with which it will be used, but in order to attach it to the
document the document needs to be opened.

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Alex Hammerstein]

Hi, Thanks for your response.

What I am trying to establish is that for a document that already has a data
source, is it possible to establish the name of the data source without
having to open the document. Its just that I have a few hundred to check
and I was hoping that I wouldn't have to go through each one opening them
up. I looked under properties but couldn't see anything

Thanks

Alex

 

[Graham Mayor]

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


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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Alex Hammerstein]

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

 

[Peter Jamieson]

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

 

[Graham Mayor]

The macro should go in the normal template or an add-in and not in a
documnent in the folder you are working on.

As for the SQL message - You receive the "Opening this will run the
following SQL command" message when you open a Word mail merge main document
that is linked to a data source - http://support.microsoft.com/?kbid=825765.
See also the Word macro to toggle the registry setting referred to in that
link at http://www.gmayor.com/word_vba_examples.htm#SQL.

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Alex Hammerstein]

Thanks Peter

The client had asked us to tidy up their database, so we went through
renaming objects properly and generally tidying up the object naming and
code.

What they forgot to tell us was that they had about 100 or so mail merge
Word documents, that of course now do not link properly, and we need to
establish what links they had so we can relink them.

I will try what you suggest

Alex