Word 2000 Merge with VBA

[C Marshall]

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?

 

[Word Heretic]

G'day "C Marshall" <[email protected]>,

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:

 

[C Marshall]

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" <[email protected]>,

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
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?