Word 2000 Merge with VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

C Marshall - 18 Aug 2005 20:19 GMT

I have a name badge document that I routinely merge with an Access data base
on a server. I create name badges for several offices. Each office is
generated with a make table and 2 append queries, with a query for the final
data.

The final data query for each office is used to create the name badges for
each office.

I recorded the merge process for one query, then copied for each office.
The code is:

Sub Merge_EventsDataBase_HOU()
'
' MailMerge Macro
' Macro recorded 8/17/2005 by cm9125
'
ActiveDocument.MailMerge.OpenDataSource Name:= _

"I:\Access\Registration_Lists_Pinnell\RegistrationLists_Original.mdb", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"QUERY Qry-N_B-Office-HOU-4-FinalData", SQLStatement:= _
"SELECT * FROM [Qry-N_B-Office-HOU-4-FinalData]", SQLStatement1:=""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
End Sub

Copied this code and changed the query name for each of the other offices.
This code ran fine, but when I tried the other offices got "Run-time error
'5922': Word was unable to open the data source."

Can someone help me with this? Also, on my computer it goes to the "I:"
drive. On someone else's computer it may not be "I:". Is there a way to let
the other user enter their drive number?

Reply to this Message

Word Heretic - 21 Aug 2005 16:27 GMT

G'day "C Marshall" <CMarshall@discussions.microsoft.com>,

As the first line of code in the sub put

Dim Drive as String

Drive = "I:/"

On Error Goto ErrHandler

Then replace the bit that says "I:/ with Drive & "

Then at the end do something like

ErrHandler:
If Err.Number<>0 then
Drive = InputBox("Enter drive name where
RegistrationLists_Original.mdb can be found","I:/")
Resume
End if

Steve Hudson - Word Heretic

steve from wordheretic.com (Email replies require payment)
Without prejudice

C Marshall reckoned:

>I have a name badge document that I routinely merge with an Access data base
>on a server. I create name badges for several offices. Each office is
[quoted text clipped - 42 lines]
>drive. On someone else's computer it may not be "I:". Is there a way to let
>the other user enter their drive number?

Reply to this Message

C Marshall - 24 Aug 2005 17:15 GMT

Thanks for your response. I tried this but didn't work. In the meantime I
changed the code so that it doesn't matter the computer, it will follow the
server path.

However, still a glitch. I need to close the datasource after each merge.
If I run merge 1, then run merge 2 - won't work. If I run merge 1, close
document, reopen and run merge 2 it works. I found some code about this and
added, but it doesn't seem to be working. Added in both the code and
document close of this document.

Here's the new code.

Sub Merge_EventsDataBase_HOU()
'
' MailMerge Macro
' Macro recorded 8/17/2005 by cm9125
'
' ID Software Document Type Project Type Start DateTime Stop
DateTime EMPLOYEE_ID Comments ClientPrefix ClientNumber
MatterNumber JobsOfIntForEval

' \\Server-tmp\BSDEV\BDGROUP\Access\Registration_Lists_Pinnell

ActiveDocument.MailMerge.OpenDataSource Name:= _

"\\Server-tmp\BSDEV\BDGROUP\Access\Registration_Lists_Pinnell\RegistrationLists_Original.mdb", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"QUERY Qry-N_B-Office-HOU-4-FinalData", SQLStatement:= _
"SELECT * FROM [Qry-N_B-Office-HOU-4-FinalData]", SQLStatement1:=""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

' Tried this code to close the datasource but didn't work.
' Also put it in This Document/Close
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

End Sub

> G'day "C Marshall" <CMarshall@discussions.microsoft.com>,
>
[quoted text clipped - 70 lines]
> >drive. On someone else's computer it may not be "I:". Is there a way to let
> >the other user enter their drive number?

Reply to this Message

Rate this thread: