Mail Merge relative reference to data source

[Chris Slowe]

Hi

I've been trying to find out over the last few days how exactly create/
fake a relative reference (as opposed to an absolute eg C:\blah\blah
\blah) to an Excel spreadsheet I'm using as a data source. It sounds
possible from some things I've read online, and I think I understand
the theory but unfortunately I'm not proficient in VBA to do it, and
nobody has posted the code anywhere (and as this is the only vb I'll
be touching it's not worth learning the whole caboodle).

It sounds like you have to create an AutoOpen macro (that kicks in on
opening the doc) that changes the path of the MailMerge datasource to
a combination of the file path of the word document and the file name
chosen for the spreadsheet. I cobbled together some code that looked
ok, and it seemed to work... but only once. on moving the duplicate
folder it reverted to referring to the original again.

I'm sure the code must be simple but I'm not having any luck

would appreciate some help. typically the project somewhat relies on
me finding a fix for this and will be seriously hampered without one!

thanks in advance

Chris

 

[Peter Jamieson]

You relaly have to do at least two thing:
a. make sure that the Mail Merge Main Document is never saved with any data
source information - otherwise, when you re-open it, the first thing that
Word does is to try to locate the existing data source. You can't write a
macro that intercepts WOrd before this point and you can't really guarantee
to avoid the dialog boxes that will appear if the data source is not found.
b. use an AutoOpen macro to connect to the new data source, as you mention.

To disconnect a datasource from its datasource before saving it, you can use

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

You will lose the source name, connection infromation and any sort/filter
selections, but not any of the fields in the document.

IN Word 2003 you can also do ActiveDocument.MailMerge.DataSource.Close, but
I know little about the consequences of that approach.

Here's a sample AutoOpen for you:

Sub AutoOpen()
Dim strDataSource As String
Dim strConnection As String
Dim strQuery As String


' this is a simple example for a data source which is a Word document
' set this to be the file name of your data source
strDataSource = "kt.doc"

' set this to be the connection string for your data source
'strConnection = ""

' set this to be the query for your data source
' strQuery = ""

With ActiveDocument
strDataSource = .Path & "\" & strDataSource
' for a Word document as data source, we just need the path name
With .MailMerge
.OpenDataSource _
Name:=strDataSource
' use the type you need
.MainDocumentType = wdFormLetters
' use the destination you need
.Destination = wdSendToNewDocument

' NB the above code does not execute the merge.
End With
End With
End Sub

Peter Jamieson

 

[Chris Slowe]

(have tried to post this with Google groups but it's not having it-
last try!)

That's great stuff Peter- and it almost works!

When I run the script it opens the correct excel worksheet and
completes the script without complaint. But when I switch back from
the VBA editor to word the sheet hasn't been attached. Checking in the
Mail Merge Helper the data source is still blank.

I can't really understand why this would be the case as everything
seems set up right. I'm sure there's a proper way of doing this but I
made a little test thing to print variables:

MsgBox ( ActiveDocument.MailMerge.DataSource.Name)

when within the 'with .MailMerge' it outputs the correct path. When
placed outside that statement it returns a blank. Any ideas?

A couple more questions...
When you say 'for a Word document as data source, we just need the
path name' does this mean that we need something more for an Excel
sheet?
Also can i just check that I'm putting the scripts in the correct
place- separate modules for the disconnect script and connect script.
rather than using the ThisDocument section?

thanks for your help

Chris

 

[Peter Jamieson]

Answering the bits that I can for now...

When you say 'for a Word document as data source, we just need the
path name' does this mean that we need something more for an Excel
sheet?

A Word .doc can only contain one data source as far as I know, so the only
way you can modify it is to provide some SQL that does the simple sorts and
filters supported by Word's very simple internal SQL dialect.

An Excel .xls workbook is much more like a "database" that can have several
"tables" (e.g. worksheets and named ranges). There are several different
possible ways to connect to a workbook, and if you connect using ODBC or OLE
DB (the default in Word 2003) you get to use the Jet dialect of SQL which is
in a different league than the simple built-in Word dialect. However, if you
just specify the workbook name in the OpenDataSource Name parameter, you
will probably see the data from the first sheet in the workbook, and you
will probably get the data via OLE DB. However, there is another problem
when connecting to OLE DB data sources which is that one way or another Word
uses a connection string that, sooner or later, will be truncated to 255
characters. If the full path name of the Excel file is long, you may find
that the connection string is truncated and the open fails.

I can't really understand why this would be the case as everything
seems set up right. I'm sure there's a proper way of doing this but I
made a little test thing to print variables:

MsgBox ( ActiveDocument.MailMerge.DataSource.Name)

Two possibilities spring to mind, neither of them particlarly convincing:
a. Do you need to implement the registry change described in

http://support.microsoft.com/kb/825765
b. does it make any difference if you set a variable to the active
document, .e.g.

Dim objMMMD As Word.Document
' or Dim objMMMD As Object ' if you're scripting
Set objMMMD = ActiveDocument

Then /only/ reference the Mail Merge Main Document using objMMMD. Certainly,
after a MailMerge.Execute, the ActiveDocument will change to be the output
document if you have specified output to a new document.

Peter Jamieson

 

[Chris Slowe]

Thanks for getting back to me.

I was sure that I mentioned I'm working in Office 2000 but looking
back...I didn't (unless it was when Google Groups lost my reply). I
imagine that slightly changes things- the registry changes seem only
to be relevant for Word 2002 onwards. Also while I'm clarifying things
I'm on Windows 2000 Professional.

I changed substituting a variable for ActiveDocument, but it had no
effect, though I can see your logic.

I have however sorted it it seems! I went through the code and
commented out various bits to see if that would make a difference and
found the culprit was
..MailMerge.MainDocumentType = wdFormLetters
once commented out everything works

for prosperities sake here's the code that I've ended up with:

----------Module1----------
Sub AutoOpen()

Dim strDataSource As String
strDataSource = "Contacts.xls"

With ActiveDocument
strDataSource = .Path & "\" & strDataSource
.MailMerge.OpenDataSource _
Name:=strDataSource, _
Connection:="!Entire Spreadsheet"
.MailMerge.Destination = wdSendToNewDocument
End With

End Sub
----------

----------Module2----------
Sub AutoClose()
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
End Sub