Maniplulating the data source location of a merge, MS Word merge

[MCubitt]

I have created a merge in MS Word which uses a text file as its data source.

When I start the document, and review the merge details, it shows the Data
Source as Data: C:\folder1\data.txt

However, I want to transport the Word and data.txt file which could end up
on another drive/folder location (but always with the Word and data.txt in
the same place, not separated).

What I really want is Data: ..\data.txt so it looks inside the current
folder.

Is this possible please?

 

[Peter Jamieson]

You can use VBA to get the path name of the .doc and use it to construct the
path name of the .txt, then issue an OpenDataSource call in an AutoOpen
macro to open that data source when the document opens. However, you should
also make sure that the document is saved without a connection to the data
source, and that means that you will lose any sorting or filtering and you
will need to specify the appropriate bit of SQL in the OpenDataSource call.

e.g., your macro might be along the following lines (I haven't tested this
one and you will probably need to modify the OpenDataSource):

Sub autoopen()
Dim sDSFN As String
sDFSN = "data.txt"
With ActiveDocument.Mailmerge
' Change the document type to the one you want
.MainDocumentType = wdFormLetters
.OpenDataSource _
ActiveDocument.Path & "\" & sDFSN, _
SQLStatement:="SELECT * FROM " & sDFSN
' Change the destination to the one you want
.Destination = wdSendToNewDocument
' Uncomment the next line if you want to do the merge
.Execute
End With
End Sub

Peter Jamieson

 

[MCubitt]

Thanks Peter. In fact I already use VBA to obtain current docpath and then
dictate the source, but Word errors before VBA is reached, since the merge
seems to be validated upon opening.

You say save without a connection to the data source, how do I do that? If
I remove the data source won't I lose the merge definition, what goes where?

Thanks

 

[Peter Jamieson]

You say save without a connection to the data source, how do I do that?

In VBA, use

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

If
I remove the data source won't I lose the merge definition, what goes
where?

You will lose the information about the source file or database, and any
sort or filter information, and propably the "current preview record" which
Word seems to save when it is connected to a data source.

To apply sorts and filters programmatically, you have to work out what SQL
is needed, e.g.

SELECT * FROM something WHERE x = 'abc' ORDER BY 1 ASC

or whatever. The only thing this misses is that there is another mechanism
for filtering records in Word 2002/2003 which is to select/reject individual
records in the Merge Recipients dialog box. That kind of filtering is not
implemented using SQL - Word just tries to remember enough info. about each
record to be able to apply the criteria. I recommend that you stay away from
that stuff.

Peter Jamieson

 

[MCubitt]

Thanks again Peter.

Now I could just be not too bright, but would you mind clarifying a few
things please?

First of all, the mail merge is very very simple. It uses an ASCII text
(csv) file as its data source. There is no filtering or sorting, that's been
done in the text file already. So all I do is map a few of the fields to
places on the Word document.

The data source is fixed at c:\folder1\data.txt but is actually generated by
a job which runs in Excel and then open this (merge) document. So the point
is that these files could be anywhere so the fixed data source is an issue.
(if the files end up on i:\temp\ then of course opening the word file fails)

There is a VBA script using document_open() which does the following:

' On opening this word doc, merge then close
Sub Document_Open()
' Filename
rightnow = Now()
newdocfilename = "AddressBook " & Year(rightnow) & "-" &
Format(Month(rightnow), "00") & "-" & Format(Day(rightnow), "00") & " " &
Format(Hour(rightnow), "00") & "." & Format(Minute(rightnow), "00") & "." &
Format(Second(rightnow), "00") & ".doc"
newdocfilename = ActiveDocument.Path & "\" & newdocfilename

datasourcefilename = "data.txt"
datasourcefilename = ActiveDocument.Path & "\" & datasourcefilename

ActiveDocument.MailMerge.OpenDataSource Name:=datasourcefilename

ActiveDocument.MailMerge.Execute
ActiveDocument.SaveAs filename:=newdocfilename
ActiveDocument.Close SaveChanges:=False
ActiveDocument.Close SaveChanges:=False
End Sub

But the problem is that when Word opens the document, before reaching the
VBA is tries to connect to the data source.


Your suggestion is to use

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

in VBA.

But where & when?

If I run the document up and enable macros it will run my VBA script and
auto close. if I disable macros I cannot run the command you rpvided (tried
in the Immediate window).

So I am not sure if I misunderstood or my execution of the method was wrong!

Thanks again

 

[Peter Jamieson]

Your suggestion is to use

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

in VBA.

But where & when?

What you need to do once and once only, before you deploy your solution, is
to open your mail merge main document and run that line of code, either in
the Immediate window in the VBA Editor, or as, e.g.

Sub x()
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
End Sub

then save the document. (In fact you can just use the Mailmerge
toolbar/wizard/mailmergehelper to switch the document back to "Normal" but
for people who are already using VBA it is simpler to give the one-liner.)

After you have made the document a normal document and saved it, when Word
opens the document you will not see the message about not being able to find
the data source. When your macro executes

ActiveDocument.MailMerge.OpenDataSource Name:=datasourcefilename

the document will become a mail merge main document again - I think it
defaults to type Form Letters, but you can set

ActiveDocument.MailMerge.MainDocumentType

to the value you want. The other thing you may have to consider if you
distribute your solution to users of recent versions and SPs of Word is that
a security issue may prevent Word from opening the data source from code
unless you apply the information given in

http://support.microsoft.com/default.aspx?scid=kb;en-us;825765

Peter Jamieson

Thanks again Peter.

Now I could just be not too bright, but would you mind clarifying a few
things please?

First of all, the mail merge is very very simple. It uses an ASCII text
(csv) file as its data source. There is no filtering or sorting, that's
been
done in the text file already. So all I do is map a few of the fields to
places on the Word document.

The data source is fixed at c:\folder1\data.txt but is actually generated
by
a job which runs in Excel and then open this (merge) document. So the
point
is that these files could be anywhere so the fixed data source is an
issue.
(if the files end up on i:\temp\ then of course opening the word file
fails)

There is a VBA script using document_open() which does the following:

' On opening this word doc, merge then close
Sub Document_Open()
' Filename
rightnow = Now()
newdocfilename = "AddressBook " & Year(rightnow) & "-" &
Format(Month(rightnow), "00") & "-" & Format(Day(rightnow), "00") & " " &
Format(Hour(rightnow), "00") & "." & Format(Minute(rightnow), "00") & "."
&
Format(Second(rightnow), "00") & ".doc"
newdocfilename = ActiveDocument.Path & "\" & newdocfilename

datasourcefilename = "data.txt"
datasourcefilename = ActiveDocument.Path & "\" & datasourcefilename

ActiveDocument.MailMerge.OpenDataSource Name:=datasourcefilename

ActiveDocument.MailMerge.Execute
ActiveDocument.SaveAs filename:=newdocfilename
ActiveDocument.Close SaveChanges:=False
ActiveDocument.Close SaveChanges:=False
End Sub

But the problem is that when Word opens the document, before reaching the
VBA is tries to connect to the data source.


Your suggestion is to use

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

in VBA.

But where & when?

If I run the document up and enable macros it will run my VBA script and
auto close. if I disable macros I cannot run the command you rpvided
(tried
in the Immediate window).

So I am not sure if I misunderstood or my execution of the method was
wrong!

Thanks again

 

[MCubitt]

Thank you very much Peter. In the end I manually removed the merge factor,
returning it back to a "normal" document. Of course the field references
remained <<...>> which was initially my worry they'd vanish!

Anyway, it worked and I can finally relase the document to a user base.

Thanks again