Reply
 
Thread Tools Display Modes
  #1   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Wink Word mailmerge - Visual Basic experts please help!

I do not speak VBA! I have managed to stop Word from displaying Save As but have finished up with the Word document displayed, but not the result of the merge. I have tried inserting the code for "MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As Document)" but that fails. What I would like is to have the merge result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying it as text into Word came out too large.

Many thanks in advance for your help

Murray
Attached Files
File Type: zip modMIBFunctions.zip (1.5 KB, 426 views)
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Word mailmerge - Visual Basic experts please help!

What exactly are you trying to do.

Copy and paste the code from the module into the body of the message that
you post to the newsgroup.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...

I do not speak VBA! I have managed to stop Word from displaying Save As
but have finished up with the Word document displayed, but not the
result of the merge. I have tried inserting the code for
"MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As
Document)" but that fails. What I would like is to have the merge
result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying it
as text into Word came out too large.

Many thanks in advance for your help

Murray


+-------------------------------------------------------------------+
|Filename: modMIBFunctions.zip |
|Download: http://www.wordbanter.com/attachment...ttachmentid=71 |
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse



  #3   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

Quote:
Originally Posted by Murray Muspratt-Rouse View Post
I do not speak VBA! I have managed to stop Word from displaying Save As but have finished up with the Word document displayed, but not the result of the merge. I have tried inserting the code for "MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As Document)" but that fails. What I would like is to have the merge result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying it as text into Word came out too large.

Many thanks in advance for your help

Murray
To explain: - I have recently been asked by a UK charity to help update their system. I have 'inherited' what has been constructed for them in Access, with use of Word Mail Merge, controlled by a Visyal Basic module. I was asked to upgrade to MS Office 2007. As I got it the mail merge process ended with a Save As display, the original document and the resuilt of the merge. What I want to achieve is the display of the merge result alone. By adding SaveChanges:=wdDoNotSaveChanges I have got rid of the Save As display, but now only the original document appears, which allows preview of the result of the merge. I did not include the VBA code in my original post, because I thought that would break the rules. I append it below.

Murray


Option Compare Database
Public lngMyEmpID As Long
Public datDate1 As Date
Public datDate2 As Date
Public datDate3 As Date



Sub OpenWordDoc(strDocName As String, strFormName As String)
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"

'Load data to MergeTable with one of two queries

If strFormName = "Volunteers" Then
DoCmd.OpenQuery ("qryVolunteer")
Else
DoCmd.OpenQuery ("qryClient")
End If

Dim objApp As Object
Dim strCurrentFileName As String
strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Visible = False

' objApp.ChangeFileOpenDirectory "C:\Temp\"
objApp.Documents.Open FileName:=strDocName, ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", XMLTransform:=""

'Format:=wdOpenFormatAuto
' "C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb", _
'Data Source=C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb
objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With objApp.ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = .ActiveRecord
.LastRecord = .ActiveRecord
End With
.Execute Pause:=False
End With

Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges




'objApp.Documents.Open strDocName


objApp.Visible = True

End Sub



'[Forms]![Volunteers]![VolunteerID]



Public Function CheckMerge() As Integer
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
CheckMerge = MsgBox(" Please Confirm", vbYesNo, "Merge Letter")
End Function


Public Sub OpenLetters(strLetterName As String)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stArgs As String

stDocName = "Letters"
stArgs = strLetterName

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormPropertySettings, acWindowNormal, stArgs


End Sub

Public Function GetNow() As Date
GetNow = FormatDateTime(Now, vbShortDate)
End Function

Public Sub ShowMatchDetails()
Forms!volunteers.cmbFindsurname.SetFocus
With Forms!volunteers![sbfBefriendingMatchVolunteer].Form
.Visible = (.RecordsetClone.RecordCount 0)
End With
If Forms!volunteers![sbfBefriendingMatchVolunteer].Form.Visible = False Then
Forms!volunteers.InsideHeight = 5300
Forms!volunteers.cmdMatchClient.Visible = True
Else
Forms!volunteers.InsideHeight = 8200
Forms!volunteers.cmdMatchClient.Visible = False

End If

End Sub
  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Word mailmerge - Visual Basic experts please help!

If you remove the

objApp.Visible = False

and perhaps replace it with

obj.App.Activate

though that may not be necessary, I think that the result of the merge will
be visible

I do not understand the following part of your code:

Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges

If you want to close the mail merge main document without saving it, just
use:

strDocName.Close wdDoNotSaveChanges

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...

Murray Muspratt-Rouse;358691 Wrote:
I do not speak VBA! I have managed to stop Word from displaying Save As
but have finished up with the Word document displayed, but not the
result of the merge. I have tried inserting the code for
"MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As
Document)" but that fails. What I would like is to have the merge
result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying it
as text into Word came out too large.

Many thanks in advance for your help

Murray


To explain: - I have recently been asked by a UK charity to help update
their system. I have 'inherited' what has been constructed for them in
Access, with use of Word Mail Merge, controlled by a Visyal Basic
module. I was asked to upgrade to MS Office 2007. As I got it the mail
merge process ended with a Save As display, the original document and
the resuilt of the merge. What I want to achieve is the display of the
merge result alone. By adding SaveChanges:=wdDoNotSaveChanges I have
got rid of the Save As display, but now only the original document
appears, which allows preview of the result of the merge. I did not
include the VBA code in my original post, because I thought that would
break the rules. I append it below.

Murray


Option Compare Database
Public lngMyEmpID As Long
Public datDate1 As Date
Public datDate2 As Date
Public datDate3 As Date



Sub OpenWordDoc(strDocName As String, strFormName As String)
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"

'Load data to MergeTable with one of two queries

If strFormName = "Volunteers" Then
DoCmd.OpenQuery ("qryVolunteer")
Else
DoCmd.OpenQuery ("qryClient")
End If

Dim objApp As Object
Dim strCurrentFileName As String
strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Visible = False

' objApp.ChangeFileOpenDirectory "C:\Temp\"
objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

'Format:=wdOpenFormatAuto
' "C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb", _
'Data Source=C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb
objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With objApp.ActiveDocument.MailMerge
Destination = wdSendToNewDocument
SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
Execute Pause:=False
End With

Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges




'objApp.Documents.Open strDocName


objApp.Visible = True

End Sub



'[Forms]![Volunteers]![VolunteerID]



Public Function CheckMerge() As Integer
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
CheckMerge = MsgBox(" Please Confirm", vbYesNo, "Merge
Letter")
End Function


Public Sub OpenLetters(strLetterName As String)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stArgs As String

stDocName = "Letters"
stArgs = strLetterName

DoCmd.OpenForm stDocName, , , stLinkCriteria,
acFormPropertySettings, acWindowNormal, stArgs


End Sub

Public Function GetNow() As Date
GetNow = FormatDateTime(Now, vbShortDate)
End Function

Public Sub ShowMatchDetails()
Forms!volunteers.cmbFindsurname.SetFocus
With Forms!volunteers![sbfBefriendingMatchVolunteer].Form
Visible = (.RecordsetClone.RecordCount 0)
End With
If Forms!volunteers![sbfBefriendingMatchVolunteer].Form.Visible =
False Then
Forms!volunteers.InsideHeight = 5300
Forms!volunteers.cmdMatchClient.Visible = True
Else
Forms!volunteers.InsideHeight = 8200
Forms!volunteers.cmdMatchClient.Visible = False

End If

End Sub


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse



  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Word mailmerge - Visual Basic experts please help!

Broadly speaking you will be better off making a reference to the mail
merge main document when you open it, e.g.

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


Unless an error document has been created, when you merge to a new document,
the new document becomes the ActiveDocument, so you should be able to do
something like

objApp.Visible = True
objApp.Activate

at the end of your code. However, I cannot say I have actually tested that
with your code here.


--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse" wrote
in message ...

Murray Muspratt-Rouse;358691 Wrote:
I do not speak VBA! I have managed to stop Word from displaying Save As
but have finished up with the Word document displayed, but not the
result of the merge. I have tried inserting the code for
"MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As
Document)" but that fails. What I would like is to have the merge
result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying it
as text into Word came out too large.

Many thanks in advance for your help

Murray


To explain: - I have recently been asked by a UK charity to help update
their system. I have 'inherited' what has been constructed for them in
Access, with use of Word Mail Merge, controlled by a Visyal Basic
module. I was asked to upgrade to MS Office 2007. As I got it the mail
merge process ended with a Save As display, the original document and
the resuilt of the merge. What I want to achieve is the display of the
merge result alone. By adding SaveChanges:=wdDoNotSaveChanges I have
got rid of the Save As display, but now only the original document
appears, which allows preview of the result of the merge. I did not
include the VBA code in my original post, because I thought that would
break the rules. I append it below.

Murray


Option Compare Database
Public lngMyEmpID As Long
Public datDate1 As Date
Public datDate2 As Date
Public datDate3 As Date



Sub OpenWordDoc(strDocName As String, strFormName As String)
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"

'Load data to MergeTable with one of two queries

If strFormName = "Volunteers" Then
DoCmd.OpenQuery ("qryVolunteer")
Else
DoCmd.OpenQuery ("qryClient")
End If

Dim objApp As Object
Dim strCurrentFileName As String
strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Visible = False

' objApp.ChangeFileOpenDirectory "C:\Temp\"
objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

'Format:=wdOpenFormatAuto
' "C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb", _
'Data Source=C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb
objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With objApp.ActiveDocument.MailMerge
Destination = wdSendToNewDocument
SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
Execute Pause:=False
End With

Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges




'objApp.Documents.Open strDocName


objApp.Visible = True

End Sub



'[Forms]![Volunteers]![VolunteerID]



Public Function CheckMerge() As Integer
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
CheckMerge = MsgBox(" Please Confirm", vbYesNo, "Merge
Letter")
End Function


Public Sub OpenLetters(strLetterName As String)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stArgs As String

stDocName = "Letters"
stArgs = strLetterName

DoCmd.OpenForm stDocName, , , stLinkCriteria,
acFormPropertySettings, acWindowNormal, stArgs


End Sub

Public Function GetNow() As Date
GetNow = FormatDateTime(Now, vbShortDate)
End Function

Public Sub ShowMatchDetails()
Forms!volunteers.cmbFindsurname.SetFocus
With Forms!volunteers![sbfBefriendingMatchVolunteer].Form
Visible = (.RecordsetClone.RecordCount 0)
End With
If Forms!volunteers![sbfBefriendingMatchVolunteer].Form.Visible =
False Then
Forms!volunteers.InsideHeight = 5300
Forms!volunteers.cmdMatchClient.Visible = True
Else
Forms!volunteers.InsideHeight = 8200
Forms!volunteers.cmdMatchClient.Visible = False

End If

End Sub


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse




  #6   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

I agree with Doug about the code he does not understand - since I am not the original author of this code and do not speak VBA any way I have been struggling to find my way to add new function to the application.

The merge document is selected by the user from an Access table in which the full path to the file is stored, so what the code that we cannot understand does is to strip off the path to the document from strDocName: -
Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
So perhaps what the last line does is to shut the new document, since the original merge document is displayed. I have tried commenting out this code but that only results in an error. I will try changing ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray

Quote:
Originally Posted by Doug Robbins - Word MVP View Post
If you remove the

objApp.Visible = False

and perhaps replace it with

obj.App.Activate

though that may not be necessary, I think that the result of the merge will
be visible

I do not understand the following part of your code:

Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges

If you want to close the mail merge main document without saving it, just
use:

strDocName.Close wdDoNotSaveChanges

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...

Murray Muspratt-Rouse;358691 Wrote:
I do not speak VBA! I have managed to stop Word from displaying Save As
but have finished up with the Word document displayed, but not the
result of the merge. I have tried inserting the code for
"MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As
Document)" but that fails. What I would like is to have the merge
result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying it
as text into Word came out too large.

Many thanks in advance for your help

Murray


To explain: - I have recently been asked by a UK charity to help update
their system. I have 'inherited' what has been constructed for them in
Access, with use of Word Mail Merge, controlled by a Visyal Basic
module. I was asked to upgrade to MS Office 2007. As I got it the mail
merge process ended with a Save As display, the original document and
the resuilt of the merge. What I want to achieve is the display of the
merge result alone. By adding SaveChanges:=wdDoNotSaveChanges I have
got rid of the Save As display, but now only the original document
appears, which allows preview of the result of the merge. I did not
include the VBA code in my original post, because I thought that would
break the rules. I append it below.

Murray


Option Compare Database
Public lngMyEmpID As Long
Public datDate1 As Date
Public datDate2 As Date
Public datDate3 As Date



Sub OpenWordDoc(strDocName As String, strFormName As String)
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"

'Load data to MergeTable with one of two queries

If strFormName = "Volunteers" Then
DoCmd.OpenQuery ("qryVolunteer")
Else
DoCmd.OpenQuery ("qryClient")
End If

Dim objApp As Object
Dim strCurrentFileName As String
strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Visible = False

' objApp.ChangeFileOpenDirectory "C:\Temp\"
objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

'Format:=wdOpenFormatAuto
' "C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb", _
'Data Source=C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb
objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With objApp.ActiveDocument.MailMerge
Destination = wdSendToNewDocument
SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
Execute Pause:=False
End With

Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges




'objApp.Documents.Open strDocName


objApp.Visible = True

End Sub



'[Forms]![Volunteers]![VolunteerID]



Public Function CheckMerge() As Integer
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
CheckMerge = MsgBox(" Please Confirm", vbYesNo, "Merge
Letter")
End Function


Public Sub OpenLetters(strLetterName As String)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stArgs As String

stDocName = "Letters"
stArgs = strLetterName

DoCmd.OpenForm stDocName, , , stLinkCriteria,
acFormPropertySettings, acWindowNormal, stArgs


End Sub

Public Function GetNow() As Date
GetNow = FormatDateTime(Now, vbShortDate)
End Function

Public Sub ShowMatchDetails()
Forms!volunteers.cmbFindsurname.SetFocus
With Forms!volunteers![sbfBefriendingMatchVolunteer].Form
Visible = (.RecordsetClone.RecordCount 0)
End With
If Forms!volunteers![sbfBefriendingMatchVolunteer].Form.Visible =
False Then
Forms!volunteers.InsideHeight = 5300
Forms!volunteers.cmdMatchClient.Visible = True
Else
Forms!volunteers.InsideHeight = 8200
Forms!volunteers.cmdMatchClient.Visible = False

End If

End Sub


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse
  #7   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

I have made some changes -
Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


'objApp.Windows(strDocName).Activate
objApp.strDocName.Close SaveChanges:=wdDoNotSaveChanges




'objApp.Documents.Open strDocName


objApp.Visible = True
objApp.Activate

The result has been, with the example I chose to test, that the first of 3 merged letters was displayed. When I closed it I then found myself looking at the original document again, from which I could run the merge and see all 3 letters!

The plot thickens!

Murray


Quote:
Originally Posted by Murray Muspratt-Rouse View Post
I agree with Doug about the code he does not understand - since I am not the original author of this code and do not speak VBA any way I have been struggling to find my way to add new function to the application.

The merge document is selected by the user from an Access table in which the full path to the file is stored, so what the code that we cannot understand does is to strip off the path to the document from strDocName: -
Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
So perhaps what the last line does is to shut the new document, since the original merge document is displayed. I have tried commenting out this code but that only results in an error. I will try changing ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray
  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Word mailmerge - Visual Basic experts please help!

Actually, I did know what the code was doing, but did not understand why.

However, I suggest that you follow the advice given by Peter Jamieson and
use

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...

I agree with Doug about the code he does not understand - since I am not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray

Doug Robbins - Word MVP;359242 Wrote:
If you remove the

objApp.Visible = False

and perhaps replace it with

obj.App.Activate

though that may not be necessary, I think that the result of the merge
will
be visible

I do not understand the following part of your code:

Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges

If you want to close the mail merge main document without saving it,
just
use:

strDocName.Close wdDoNotSaveChanges

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"

wrote
in message ...-

Murray Muspratt-Rouse;358691 Wrote:-
I do not speak VBA! I have managed to stop Word from displaying Save
As
but have finished up with the Word document displayed, but not the
result of the merge. I have tried inserting the code for
"MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As
Document)" but that fails. What I would like is to have the merge
result alone displayed.

I attach the VBA module exported as .bas in a zip file since copying
it
as text into Word came out too large.

Many thanks in advance for your help

Murray-

To explain: - I have recently been asked by a UK charity to help
update
their system. I have 'inherited' what has been constructed for them
in
Access, with use of Word Mail Merge, controlled by a Visyal Basic
module. I was asked to upgrade to MS Office 2007. As I got it the
mail
merge process ended with a Save As display, the original document and
the resuilt of the merge. What I want to achieve is the display of
the
merge result alone. By adding SaveChanges:=wdDoNotSaveChanges I have
got rid of the Save As display, but now only the original document
appears, which allows preview of the result of the merge. I did not
include the VBA code in my original post, because I thought that
would
break the rules. I append it below.

Murray


Option Compare Database
Public lngMyEmpID As Long
Public datDate1 As Date
Public datDate2 As Date
Public datDate3 As Date



Sub OpenWordDoc(strDocName As String, strFormName As String)
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"

'Load data to MergeTable with one of two queries

If strFormName = "Volunteers" Then
DoCmd.OpenQuery ("qryVolunteer")
Else
DoCmd.OpenQuery ("qryClient")
End If

Dim objApp As Object
Dim strCurrentFileName As String
strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Visible = False

' objApp.ChangeFileOpenDirectory "C:\Temp\"
objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

'Format:=wdOpenFormatAuto
' "C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb", _
'Data Source=C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb
objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With objApp.ActiveDocument.MailMerge
Destination = wdSendToNewDocument
SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
Execute Pause:=False
End With

Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges




'objApp.Documents.Open strDocName


objApp.Visible = True

End Sub



'[Forms]![Volunteers]![VolunteerID]



Public Function CheckMerge() As Integer
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
CheckMerge = MsgBox(" Please Confirm", vbYesNo, "Merge
Letter")
End Function


Public Sub OpenLetters(strLetterName As String)

Dim stDocName As String
Dim stLinkCriteria As String
Dim stArgs As String

stDocName = "Letters"
stArgs = strLetterName

DoCmd.OpenForm stDocName, , , stLinkCriteria,
acFormPropertySettings, acWindowNormal, stArgs


End Sub

Public Function GetNow() As Date
GetNow = FormatDateTime(Now, vbShortDate)
End Function

Public Sub ShowMatchDetails()
Forms!volunteers.cmbFindsurname.SetFocus
With Forms!volunteers![sbfBefriendingMatchVolunteer].Form
Visible = (.RecordsetClone.RecordCount 0)
End With
If Forms!volunteers![sbfBefriendingMatchVolunteer].Form.Visible =
False Then
Forms!volunteers.InsideHeight = 5300
Forms!volunteers.cmdMatchClient.Visible = True
Else
Forms!volunteers.InsideHeight = 8200
Forms!volunteers.cmdMatchClient.Visible = False

End If

End Sub


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse -



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse



  #9   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

Thank you Doug for referring me to Peter Jamieson's advice. I have immediately run in to a problem - it does not like 'objMMMD As Word.Document' telling me 'User-defined type not defined' and suggesting that it might be in a properly registered object or type library. Did I misunderstand Peter's advice? Was I meant to change objMMMD to something else?

Murray

[quote=Doug Robbins - Word MVP;359552]Actually, I did know what the code was doing, but did not understand why.

However, I suggest that you follow the advice given by Peter Jamieson and
use

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

I agree with Doug about the code he does not understand - since I am not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray
  #10   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Word mailmerge - Visual Basic experts please help!

That is probably because the Word Object library has not been referenced via
the Tools|References option in Access VBA. However, the original author may
have avoided that deliberately because when you reference a library, the
reference is to a specific version of Word.

You can typically work around that using the following instead (you just
don't see the autocomplete Help in the VBA editor that you would otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray

Doug Robbins - Word MVP;359552 Wrote:
Actually, I did know what the code was doing, but did not understand
why.

However, I suggest that you follow the advice given by Peter Jamieson
and
use

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"
wrote
in message
...

I agree with Doug about the code he does not understand - since I am
not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the
application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse




  #11   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

I am very grateful to Peter for his advice - this morning I realised that I should have googled objMMMD. When I did that I found some of his postings on the web and started looking for Microsoft Office 12.0 Object Library, Trying to reference it showed it to be missing, so I tried to find its name and location. What I saw was cut off at c:\Program Files\Microsoft Office\Office 12\M - and I could not make the pop up any wider!

I have tried Dim objMMMD As Object - the result was a compile error with a syntax error on Set objMMMD = objApp.Documents.Open FileName:=strDocName etc. I must locate the Object Library and regioster it, I think.

Murray

Quote:
Originally Posted by Peter Jamieson View Post
That is probably because the Word Object library has not been referenced via
the Tools|References option in Access VBA. However, the original author may
have avoided that deliberately because when you reference a library, the
reference is to a specific version of Word.

You can typically work around that using the following instead (you just
don't see the autocomplete Help in the VBA editor that you would otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray

Doug Robbins - Word MVP;359552 Wrote:
Actually, I did know what the code was doing, but did not understand
why.

However, I suggest that you follow the advice given by Peter Jamieson
and
use

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"

wrote
in message
...

I agree with Doug about the code he does not understand - since I am
not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the
application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse
  #12   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Word mailmerge - Visual Basic experts please help!

My apologies- the syntax for the Open has to change to the "function" style
where the parameter list is surrounded by braces, e.g.

Set objMMMD = objApp.Documents.Open(FileName:=strDocName)


--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

I am very grateful to Peter for his advice - this morning I realised
that I should have googled objMMMD. When I did that I found some of his
postings on the web and started looking for Microsoft Office 12.0 Object
Library, Trying to reference it showed it to be missing, so I tried to
find its name and location. What I saw was cut off at c:\Program
Files\Microsoft Office\Office 12\M - and I could not make the pop up
any wider!

I have tried Dim objMMMD As Object - the result was a compile error
with a syntax error on Set objMMMD = objApp.Documents.Open
FileName:=strDocName etc. I must locate the Object Library and
regioster it, I think.

Murray

Peter Jamieson;359803 Wrote:
That is probably because the Word Object library has not been referenced
via
the Tools|References option in Access VBA. However, the original author
may
have avoided that deliberately because when you reference a library,
the
reference is to a specific version of Word.

You can typically work around that using the following instead (you
just
don't see the autocomplete Help in the VBA editor that you would
otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse"
wrote
in message ...-

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray

Doug Robbins - Word MVP;359552 Wrote:-
Actually, I did know what the code was doing, but did not understand
why.

However, I suggest that you follow the advice given by Peter Jamieson
and
use

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead
of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"

wrote
in message
...

I agree with Doug about the code he does not understand - since I am
not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the
application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray
-


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse -



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse


  #13   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

I think I have overcome the Tools/References problem, but now I have another one: - I get an compile error message telling me that End of Statement was expected, highlighting FileName in the statement to which I have added 'Set objMMMD ='

Set objMMMD = objApp.Documents.Open FileName:=strDocName, ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", XMLTransform:=""

Murray

Quote:
Originally Posted by Peter Jamieson View Post
That is probably because the Word Object library has not been referenced via
the Tools|References option in Access VBA. However, the original author may
have avoided that deliberately because when you reference a library, the
reference is to a specific version of Word.

You can typically work around that using the following instead (you just
don't see the autocomplete Help in the VBA editor that you would otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray

Doug Robbins - Word MVP;359552 Wrote:
Actually, I did know what the code was doing, but did not understand
why.

However, I suggest that you follow the advice given by Peter Jamieson
and
use

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"

wrote
in message
...

I agree with Doug about the code he does not understand - since I am
not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the
application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse
  #14   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

This time at execution the error message on "Dim objMMMD As Word.Document" was 'User-defined type not defined'. Does this mean I am missing something else? A 'type' library?

Murray
Quote:
Originally Posted by Peter Jamieson View Post
My apologies- the syntax for the Open has to change to the "function" style
where the parameter list is surrounded by braces, e.g.

Set objMMMD = objApp.Documents.Open(FileName:=strDocName)


--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

I am very grateful to Peter for his advice - this morning I realised
that I should have googled objMMMD. When I did that I found some of his
postings on the web and started looking for Microsoft Office 12.0 Object
Library, Trying to reference it showed it to be missing, so I tried to
find its name and location. What I saw was cut off at c:\Program
Files\Microsoft Office\Office 12\M - and I could not make the pop up
any wider!

I have tried Dim objMMMD As Object - the result was a compile error
with a syntax error on Set objMMMD = objApp.Documents.Open
FileName:=strDocName etc. I must locate the Object Library and
regioster it, I think.

Murray

Peter Jamieson;359803 Wrote:
That is probably because the Word Object library has not been referenced
via
the Tools|References option in Access VBA. However, the original author
may
have avoided that deliberately because when you reference a library,
the
reference is to a specific version of Word.

You can typically work around that using the following instead (you
just
don't see the autocomplete Help in the VBA editor that you would
otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse"
wrote
in message ...-

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray

Doug Robbins - Word MVP;359552 Wrote:-
Actually, I did know what the code was doing, but did not understand
why.

However, I suggest that you follow the advice given by Peter Jamieson
and
use

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead
of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"

wrote
in message
...

I agree with Doug about the code he does not understand - since I am
not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the
application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray
-


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse -



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse
  #15   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Word mailmerge - Visual Basic experts please help!

See Peter's previous message in which he says to use

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

Probably all that you really need of that command is:

Set objMMMD = objApp.Documents.Open (FileName: = strDocName,
AddToRecentFiles:=False )

And that is on the assumption that you do not want the document to be added
to the list of most recently used files.

If that is not an issue, you can simply use

Set objMMMD = obj.App.Documents.Open(strDocName)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

I think I have overcome the Tools/References problem, but now I have
another one: - I get an compile error message telling me that End of
Statement was expected, highlighting FileName in the statement to which
I have added 'Set objMMMD ='

Set objMMMD = objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

Murray

Peter Jamieson;359803 Wrote:
That is probably because the Word Object library has not been referenced
via
the Tools|References option in Access VBA. However, the original author
may
have avoided that deliberately because when you reference a library,
the
reference is to a specific version of Word.

You can typically work around that using the following instead (you
just
don't see the autocomplete Help in the VBA editor that you would
otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse"
wrote
in message ...-

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray

Doug Robbins - Word MVP;359552 Wrote:-
Actually, I did know what the code was doing, but did not understand
why.

However, I suggest that you follow the advice given by Peter Jamieson
and
use

Dim objMMMD As Word.Document
'
'
'
Set objMMMD = objApp.Documents.Open FileName:=strDocName, 'etc.
'
'
'
' then when you want to close the mail merge main document, instead
of
having to
' work out which window it is in, you can do

objMMMD.Close SaveChanges:=False
'
' then

Set objMMMD = Nothing


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"

wrote
in message
...

I agree with Doug about the code he does not understand - since I am
not
the original author of this code and do not speak VBA any way I have
been struggling to find my way to add new function to the
application.

The merge document is selected by the user from an Access table in
which the full path to the file is stored, so what the code that we
cannot understand does is to strip off the path to the document from
strDocName: -
Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
So perhaps what the last line does is to shut the new document, since
the original merge document is displayed. I have tried commenting out
this code but that only results in an error. I will try changing
ActiveWindow in the last line to strDocName!

Many thanks for your time and trouble

Murray
-


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse -



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse





  #16   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

While thanking Peter and Doug for their advice I have to say that I have a major problem - I am not sure what objMMMD is. Does it stand for Mail Merge Main Document or something like that? If so is it meant to be used to identify the document set up with merge fields, so as to make it possible to close it without closing the result of the merge or whatever? If I am right then I think I understand what I must do to use it in the VBA code.

Also, because the References pop-up only shows me the first character of the module name (C:\Program Files\Common Files\Microsoft Office\OFFICE12\M) I do not whether one of the 12 dlls i can see is the right one!

Murray

[quote=Doug Robbins - Word MVP;360024]See Peter's previous message in which he says to use

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

Probably all that you really need of that command is:

Set objMMMD = objApp.Documents.Open (FileName: = strDocName,
AddToRecentFiles:=False )

And that is on the assumption that you do not want the document to be added
to the list of most recently used files.

If that is not an issue, you can simply use

Set objMMMD = obj.App.Documents.Open(strDocName)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

I think I have overcome the Tools/References problem, but now I have
another one: - I get an compile error message telling me that End of
Statement was expected, highlighting FileName in the statement to which
I have added 'Set objMMMD ='

Set objMMMD = objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

Murray

Peter Jamieson;359803 Wrote:[color=green][i]
That is probably because the Word Object library has not been referenced
via
the Tools|References option in Access VBA. However, the original author
may
have avoided that deliberately because when you reference a library,
the
reference is to a specific version of Word.

You can typically work around that using the following instead (you
just
don't see the autocomplete Help in the VBA editor that you would
otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse"
wrote
in message ...-

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray
  #17   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

I tried 'Dim objMMMD As Word.Document' again and got the error message "User-defined type not defined'. This is what makes me think I do not have the correct objMMMD module registered. I tried 'Dim objMMMD As Object' and the result was that the Mail Merge document was closed, but the display of the merge result only showed one document, instead of the 3 that I expected.

Murray

[quote=Murray Muspratt-Rouse;360272]While thanking Peter and Doug for their advice I have to say that I have a major problem - I am not sure what objMMMD is. Does it stand for Mail Merge Main Document or something like that? If so is it meant to be used to identify the document set up with merge fields, so as to make it possible to close it without closing the result of the merge or whatever? If I am right then I think I understand what I must do to use it in the VBA code.

Also, because the References pop-up only shows me the first character of the module name (C:\Program Files\Common Files\Microsoft Office\OFFICE12\M) I do not whether one of the 12 dlls i can see is the right one!

Murray

Quote:
Originally Posted by Doug Robbins - Word MVP View Post
See Peter's previous message in which he says to use

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

Probably all that you really need of that command is:

Set objMMMD = objApp.Documents.Open (FileName: = strDocName,
AddToRecentFiles:=False )

And that is on the assumption that you do not want the document to be added
to the list of most recently used files.

If that is not an issue, you can simply use

Set objMMMD = obj.App.Documents.Open(strDocName)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

I think I have overcome the Tools/References problem, but now I have
another one: - I get an compile error message telling me that End of
Statement was expected, highlighting FileName in the statement to which
I have added 'Set objMMMD ='

Set objMMMD = objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

Murray

Peter Jamieson;359803 Wrote:[color=green][i]
That is probably because the Word Object library has not been referenced
via
the Tools|References option in Access VBA. However, the original author
may
have avoided that deliberately because when you reference a library,
the
reference is to a specific version of Word.

You can typically work around that using the following instead (you
just
don't see the autocomplete Help in the VBA editor that you would
otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse"
wrote
in message ...-

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray
  #18   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Word mailmerge - Visual Basic experts please help!

objMMMD is just a "label" assigned to a document object which by the command

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

is being set to the document with the file name of strDocName, which I
believe is your mailmerge main document.

having done that, you can refer to that document by the label that is
assigned to the object.

There is nothing special about the MMMD other than it conveys some meaning
as you have deduced to the use of a person reading the code

You could just as well have used:

Dim doc as Object
Set doc = objApp.Documents.Open (FileName: = strDocName, etc )

but then doc does not convey as much intelligence to the user, other than it
probably refers to a document, but which one.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

While thanking Peter and Doug for their advice I have to say that I have
a major problem - I am not sure what objMMMD is. Does it stand for Mail
Merge Main Document or something like that? If so is it meant to be
used to identify the document set up with merge fields, so as to make
it possible to close it without closing the result of the merge or
whatever? If I am right then I think I understand what I must do to use
it in the VBA code.

Also, because the References pop-up only shows me the first character
of the module name (C:\Program Files\Common Files\Microsoft
Office\OFFICE12\M) I do not whether one of the 12 dlls i can see is the
right one!

Murray

Doug Robbins - Word MVP;360024 Wrote:[color=green][i]
See Peter's previous message in which he says to use

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

Probably all that you really need of that command is:

Set objMMMD = objApp.Documents.Open (FileName: = strDocName,
AddToRecentFiles:=False )

And that is on the assumption that you do not want the document to be
added
to the list of most recently used files.

If that is not an issue, you can simply use

Set objMMMD = obj.App.Documents.Open(strDocName)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"
wrote
in message
...

I think I have overcome the Tools/References problem, but now I have
another one: - I get an compile error message telling me that End of
Statement was expected, highlighting FileName in the statement to
which
I have added 'Set objMMMD ='

Set objMMMD = objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

Murray

Peter Jamieson;359803 Wrote:
That is probably because the Word Object library has not been
referenced
via
the Tools|References option in Access VBA. However, the original
author
may
have avoided that deliberately because when you reference a library,
the
reference is to a specific version of Word.

You can typically work around that using the following instead (you
just
don't see the autocomplete Help in the VBA editor that you would
otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse"
wrote
in message ...-

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse



  #19   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

Doug, thank you for confirming my guess! I have in fact used 'Dim objMMD As Object' as Peter recommended - the result of a merge that should have produced 3 letters was that I got only one. For the moment I intend to leave things as they are (without the objMMD code), so that users will finish the merge with the function provided by Word 2007, unless, of course, there is a solution for the problem I have described!

Murray

Quote:
Originally Posted by Doug Robbins - Word MVP View Post
objMMMD is just a "label" assigned to a document object which by the command

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

is being set to the document with the file name of strDocName, which I
believe is your mailmerge main document.

having done that, you can refer to that document by the label that is
assigned to the object.

There is nothing special about the MMMD other than it conveys some meaning
as you have deduced to the use of a person reading the code

You could just as well have used:

Dim doc as Object
Set doc = objApp.Documents.Open (FileName: = strDocName, etc )

but then doc does not convey as much intelligence to the user, other than it
probably refers to a document, but which one.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

While thanking Peter and Doug for their advice I have to say that I have
a major problem - I am not sure what objMMMD is. Does it stand for Mail
Merge Main Document or something like that? If so is it meant to be
used to identify the document set up with merge fields, so as to make
it possible to close it without closing the result of the merge or
whatever? If I am right then I think I understand what I must do to use
it in the VBA code.

Also, because the References pop-up only shows me the first character
of the module name (C:\Program Files\Common Files\Microsoft
Office\OFFICE12\M) I do not whether one of the 12 dlls i can see is the
right one!

Murray

Doug Robbins - Word MVP;360024 Wrote:[color=green][i]
See Peter's previous message in which he says to use

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

Probably all that you really need of that command is:

Set objMMMD = objApp.Documents.Open (FileName: = strDocName,
AddToRecentFiles:=False )

And that is on the assumption that you do not want the document to be
added
to the list of most recently used files.

If that is not an issue, you can simply use

Set objMMMD = obj.App.Documents.Open(strDocName)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"

wrote
in message
...

I think I have overcome the Tools/References problem, but now I have
another one: - I get an compile error message telling me that End of
Statement was expected, highlighting FileName in the statement to
which
I have added 'Set objMMMD ='

Set objMMMD = objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

Murray

Peter Jamieson;359803 Wrote:
That is probably because the Word Object library has not been
referenced
via
the Tools|References option in Access VBA. However, the original
author
may
have avoided that deliberately because when you reference a library,
the
reference is to a specific version of Word.

You can typically work around that using the following instead (you
just
don't see the autocomplete Help in the VBA editor that you would
otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse"
wrote
in message ...-

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse
  #20   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Word mailmerge - Visual Basic experts please help!

Murray,

Can you post back with the complete code as you now have it.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

Doug, thank you for confirming my guess! I have in fact used 'Dim objMMD
As Object' as Peter recommended - the result of a merge that should have
produced 3 letters was that I got only one. For the moment I intend to
leave things as they are (without the objMMD code), so that users will
finish the merge with the function provided by Word 2007, unless, of
course, there is a solution for the problem I have described!

Murray

Doug Robbins - Word MVP;360424 Wrote:[color=green][i]
objMMMD is just a "label" assigned to a document object which by the
command

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

is being set to the document with the file name of strDocName, which I

believe is your mailmerge main document.

having done that, you can refer to that document by the label that is
assigned to the object.

There is nothing special about the MMMD other than it conveys some
meaning
as you have deduced to the use of a person reading the code

You could just as well have used:

Dim doc as Object
Set doc = objApp.Documents.Open (FileName: = strDocName, etc )

but then doc does not convey as much intelligence to the user, other
than it
probably refers to a document, but which one.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"
wrote
in message ...-

While thanking Peter and Doug for their advice I have to say that I
have
a major problem - I am not sure what objMMMD is. Does it stand for
Mail
Merge Main Document or something like that? If so is it meant to be
used to identify the document set up with merge fields, so as to make
it possible to close it without closing the result of the merge or
whatever? If I am right then I think I understand what I must do to
use
it in the VBA code.

Also, because the References pop-up only shows me the first character
of the module name (C:\Program Files\Common Files\Microsoft
Office\OFFICE12\M) I do not whether one of the 12 dlls i can see is
the
right one!

Murray

Doug Robbins - Word MVP;360024 Wrote:-
See Peter's previous message in which he says to use

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

Probably all that you really need of that command is:

Set objMMMD = objApp.Documents.Open (FileName: = strDocName,
AddToRecentFiles:=False )

And that is on the assumption that you do not want the document to be
added
to the list of most recently used files.

If that is not an issue, you can simply use

Set objMMMD = obj.App.Documents.Open(strDocName)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"

wrote
in message
...

I think I have overcome the Tools/References problem, but now I have
another one: - I get an compile error message telling me that End of
Statement was expected, highlighting FileName in the statement to
which
I have added 'Set objMMMD ='

Set objMMMD = objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

Murray

Peter Jamieson;359803 Wrote:
That is probably because the Word Object library has not been
referenced
via
the Tools|References option in Access VBA. However, the original
author
may
have avoided that deliberately because when you reference a library,
the
reference is to a specific version of Word.

You can typically work around that using the following instead (you
just
don't see the autocomplete Help in the VBA editor that you would
otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse"
wrote
in message ...-

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray-


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse -



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse





  #21   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Smile

Here is the code as it is now. The merge document is displayed and the user can then finish the merge.

Option Compare Database
Public lngMyEmpID As Long
Public datDate1 As Date
Public datDate2 As Date
Public datDate3 As Date



Sub OpenWordDoc(strDocName As String, strLetterDescription As String, strFormName As String)
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

Dim objApp As Object
Dim strCurrentFileName As String
strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Visible = False
'Dim objMMMD As Object
'Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
' objApp.ChangeFileOpenDirectory "C:\Temp\"
objApp.Documents.Open FileName:=strDocName, ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", XMLTransform:=""

'Format:=wdOpenFormatAuto
' "C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb", _
'Data Source=C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb
objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With objApp.ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = .ActiveRecord
.LastRecord = .ActiveRecord
End With
.Execute Pause:=False
End With


Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
'objMMMD.Close SaveChanges:=False
'Set objMMMD = Nothing


'objApp.Documents.Open strDocName


objApp.Visible = True
objApp.Activate

End Sub

I really do appreciate the help you give!

Murray

Quote:
Originally Posted by Doug Robbins - Word MVP View Post
Murray,

Can you post back with the complete code as you now have it.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

Doug, thank you for confirming my guess! I have in fact used 'Dim objMMD
As Object' as Peter recommended - the result of a merge that should have
produced 3 letters was that I got only one. For the moment I intend to
leave things as they are (without the objMMD code), so that users will
finish the merge with the function provided by Word 2007, unless, of
course, there is a solution for the problem I have described!

Murray

Doug Robbins - Word MVP;360424 Wrote:[color=green][i]
objMMMD is just a "label" assigned to a document object which by the
command

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

is being set to the document with the file name of strDocName, which I

believe is your mailmerge main document.

having done that, you can refer to that document by the label that is
assigned to the object.

There is nothing special about the MMMD other than it conveys some
meaning
as you have deduced to the use of a person reading the code

You could just as well have used:

Dim doc as Object
Set doc = objApp.Documents.Open (FileName: = strDocName, etc )

but then doc does not convey as much intelligence to the user, other
than it
probably refers to a document, but which one.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"

wrote
in message ...-

While thanking Peter and Doug for their advice I have to say that I
have
a major problem - I am not sure what objMMMD is. Does it stand for
Mail
Merge Main Document or something like that? If so is it meant to be
used to identify the document set up with merge fields, so as to make
it possible to close it without closing the result of the merge or
whatever? If I am right then I think I understand what I must do to
use
it in the VBA code.

Also, because the References pop-up only shows me the first character
of the module name (C:\Program Files\Common Files\Microsoft
Office\OFFICE12\M) I do not whether one of the 12 dlls i can see is
the
right one!

Murray

Doug Robbins - Word MVP;360024 Wrote:-
See Peter's previous message in which he says to use

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

Probably all that you really need of that command is:

Set objMMMD = objApp.Documents.Open (FileName: = strDocName,
AddToRecentFiles:=False )

And that is on the assumption that you do not want the document to be
added
to the list of most recently used files.

If that is not an issue, you can simply use

Set objMMMD = obj.App.Documents.Open(strDocName)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"

wrote
in message
...

I think I have overcome the Tools/References problem, but now I have
another one: - I get an compile error message telling me that End of
Statement was expected, highlighting FileName in the statement to
which
I have added 'Set objMMMD ='

Set objMMMD = objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

Murray

Peter Jamieson;359803 Wrote:
That is probably because the Word Object library has not been
referenced
via
the Tools|References option in Access VBA. However, the original
author
may
have avoided that deliberately because when you reference a library,
the
reference is to a specific version of Word.

You can typically work around that using the following instead (you
just
don't see the autocomplete Help in the VBA editor that you would
otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse"
wrote
in message ...-

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray-


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse -



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse
  #22   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Word mailmerge - Visual Basic experts please help!

Try the following (watch out for line breaks that may occur in the wrong
place.) I haven't really looked at the If... Else...End If manipulations of
the Access data at the beginning to see if there is anything wrong with it,
only at the Word part of the code.

Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String

On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Activate
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
With ObjMMMD
.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er _
ID=Admin;Date Source=strCurrentFileName;Mode=Read; _
Extended Properties="""";Jet OLEDB:System database=""""; _
Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLE",
_
SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
.Execute Pause:=False
.Close wdDoNotSaveChanges
End With

End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...[color=blue][i]

Here is the code as it is now. The merge document is displayed and the
user can then finish the merge.

Option Compare Database
Public lngMyEmpID As Long
Public datDate1 As Date
Public datDate2 As Date
Public datDate3 As Date



Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

Dim objApp As Object
Dim strCurrentFileName As String
strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Visible = False
'Dim objMMMD As Object
'Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
' objApp.ChangeFileOpenDirectory "C:\Temp\"
objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

'Format:=wdOpenFormatAuto
' "C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb", _
'Data Source=C:\Documents and Settings\mike\My Documents\Mind\MIB.mdb
objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With objApp.ActiveDocument.MailMerge
Destination = wdSendToNewDocument
SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
Execute Pause:=False
End With


Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
'objMMMD.Close SaveChanges:=False
'Set objMMMD = Nothing


'objApp.Documents.Open strDocName


objApp.Visible = True
objApp.Activate

End Sub

I really do appreciate the help you give!

Murray

Doug Robbins - Word MVP;360676 Wrote:[color=green][i]
Murray,

Can you post back with the complete code as you now have it.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"
wrote
in message ...-

Doug, thank you for confirming my guess! I have in fact used 'Dim
objMMD
As Object' as Peter recommended - the result of a merge that should
have
produced 3 letters was that I got only one. For the moment I intend
to
leave things as they are (without the objMMD code), so that users
will
finish the merge with the function provided by Word 2007, unless, of
course, there is a solution for the problem I have described!

Murray

Doug Robbins - Word MVP;360424 Wrote:-
objMMMD is just a "label" assigned to a document object which by the
command

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

is being set to the document with the file name of strDocName, which
I

believe is your mailmerge main document.

having done that, you can refer to that document by the label that is
assigned to the object.

There is nothing special about the MMMD other than it conveys some
meaning
as you have deduced to the use of a person reading the code

You could just as well have used:

Dim doc as Object
Set doc = objApp.Documents.Open (FileName: = strDocName, etc )

but then doc does not convey as much intelligence to the user, other
than it
probably refers to a document, but which one.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"

wrote
in message ...-

While thanking Peter and Doug for their advice I have to say that I
have
a major problem - I am not sure what objMMMD is. Does it stand for
Mail
Merge Main Document or something like that? If so is it meant to be
used to identify the document set up with merge fields, so as to make
it possible to close it without closing the result of the merge or
whatever? If I am right then I think I understand what I must do to
use
it in the VBA code.

Also, because the References pop-up only shows me the first character
of the module name (C:\Program Files\Common Files\Microsoft
Office\OFFICE12\M) I do not whether one of the 12 dlls i can see is
the
right one!

Murray

Doug Robbins - Word MVP;360024 Wrote:-
See Peter's previous message in which he says to use

Set objMMMD = objApp.Documents.Open (FileName: = strDocName, etc )

Probably all that you really need of that command is:

Set objMMMD = objApp.Documents.Open (FileName: = strDocName,
AddToRecentFiles:=False )

And that is on the assumption that you do not want the document to be
added
to the list of most recently used files.

If that is not an issue, you can simply use

Set objMMMD = obj.App.Documents.Open(strDocName)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse"

wrote
in message
...

I think I have overcome the Tools/References problem, but now I have
another one: - I get an compile error message telling me that End of
Statement was expected, highlighting FileName in the statement to
which
I have added 'Set objMMMD ='

Set objMMMD = objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
_
WritePasswordTemplate:="", XMLTransform:=""

Murray

Peter Jamieson;359803 Wrote:
That is probably because the Word Object library has not been
referenced
via
the Tools|References option in Access VBA. However, the original
author
may
have avoided that deliberately because when you reference a library,
the
reference is to a specific version of Word.

You can typically work around that using the following instead (you
just
don't see the autocomplete Help in the VBA editor that you would
otherwise
get)

Dim objMMMD As Object

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Murray Muspratt-Rouse"
wrote
in message ...-

Thank you Doug for referring me to Peter Jamieson's advice. I have
immediately run in to a problem - it does not like 'objMMMD As
Word.Document' telling me 'User-defined type not defined' and
suggesting that it might be in a properly registered object or type
library. Did I misunderstand Peter's advice? Was I meant to change
objMMMD to something else?

Murray-


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse --


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse -



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse



  #23   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

I have implemented Doug's suggestions, but, while the code ran without error, nothing was displayed - the application returned to the form in MS Access from which the merge was initiated. The code that ran is below: -

Sub OpenWordDoc(strDocName As String, strLetterDescription As String, strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name

Set objApp = CreateObject("Word.Application")
'objApp.Visible = False
objApp.Activate
'Dim objMMMD As Object
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

'objApp.Documents.Open FileName:=strDocName, ConfirmConversions:=False, _
'ReadOnly:=False, AddToRecentFiles:=False _
', PasswordDocument:="", _
'PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
'WritePasswordTemplate:="", XMLTransform:=""

'objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _

With objMMMD
.MailMerge.OpenDataSource Name:=strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = .ActiveRecord
.LastRecord = .ActiveRecord
End With
.Execute Pause:=False
.Close wdDoNotSaveChanges
End With


'Dim intSplitName As Integer
'Dim intLength As Integer
'intLength = Len(strDocName)
'intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
'strDocName = Right(strDocName, intLength - intSplitName)


'objApp.Windows(strDocName).Activate
'objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
'objMMMD.Close SaveChanges:=False
'Set objMMMD = Nothing


'objApp.Documents.Open strDocName


'objApp.Visible = True
'objApp.Activate

End Sub

Murray

Quote:
Originally Posted by Doug Robbins - Word MVP View Post
Try the following (watch out for line breaks that may occur in the wrong
place.) I haven't really looked at the If... Else...End If manipulations of
the Access data at the beginning to see if there is anything wrong with it,
only at the Word part of the code.

Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String

On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Activate
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
With ObjMMMD
.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er _
ID=Admin;Date Source=strCurrentFileName;Mode=Read; _
Extended Properties="""";Jet OLEDB:System database=""""; _
Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLE",
_
SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
.Execute Pause:=False
.Close wdDoNotSaveChanges
End With

End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
--
  #24   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Word mailmerge - Visual Basic experts please help!

I can see why you would comment out

'objApp.Visible = False

But why are you commenting out all of the other lines of code?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...

I have implemented Doug's suggestions, but, while the code ran without
error, nothing was displayed - the application returned to the form in
MS Access from which the merge was initiated. The code that ran is
below: -

Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name

Set objApp = CreateObject("Word.Application")
'objApp.Visible = False
objApp.Activate
'Dim objMMMD As Object
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

'objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
'ReadOnly:=False, AddToRecentFiles:=False _
', PasswordDocument:="", _
'PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
'WritePasswordTemplate:="", XMLTransform:=""

'objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _

With objMMMD
MailMerge.OpenDataSource Name:=strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
Destination = wdSendToNewDocument
SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
Execute Pause:=False
Close wdDoNotSaveChanges
End With


'Dim intSplitName As Integer
'Dim intLength As Integer
'intLength = Len(strDocName)
'intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
'strDocName = Right(strDocName, intLength - intSplitName)


'objApp.Windows(strDocName).Activate
'objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
'objMMMD.Close SaveChanges:=False
'Set objMMMD = Nothing


'objApp.Documents.Open strDocName


'objApp.Visible = True
'objApp.Activate

End Sub

Murray

Doug Robbins - Word MVP;360910 Wrote:
Try the following (watch out for line breaks that may occur in the wrong

place.) I haven't really looked at the If... Else...End If
manipulations of
the Access data at the beginning to see if there is anything wrong with
it,
only at the Word part of the code.

Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String

On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Activate
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
With ObjMMMD
.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er _
ID=Admin;Date Source=strCurrentFileName;Mode=Read; _
Extended Properties="""";Jet OLEDB:System database=""""; _
Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet
OLE",
_
SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
.Execute Pause:=False
.Close wdDoNotSaveChanges
End With

End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
--



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse



  #25   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

The reason I commented out all the other lines of code was so as to make it easier to get back to where I was if the recommendations did not succeed in solving the problem. I will back up the database and do it again, removing all the code not included in your recommendations. Then, if it fails again, I will be able to restore the module from the backup.

I have done as I said I would above, with the same result. Here is the code that ran: -

Sub OpenWordDoc(strDocName As String, strLetterDescription As String, strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name

Set objApp = CreateObject("Word.Application")
objApp.Activate
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

With objMMMD
.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = .ActiveRecord
.LastRecord = .ActiveRecord
End With
.Execute Pause:=False
.Close wdDoNotSaveChanges
End With

End Sub


Murray

Quote:
Originally Posted by Doug Robbins - Word MVP View Post
I can see why you would comment out

'objApp.Visible = False

But why are you commenting out all of the other lines of code?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Last edited by Murray Muspratt-Rouse : August 29th 08 at 11:20 AM Reason: Completion of further test of code


  #26   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Word mailmerge - Visual Basic experts please help!

There are some significant differences in this section of the code in your
message from that which I provided, that would cause it not to compile
correctly:

Set objApp = CreateObject("Word.Application")
'objApp.Visible = False
objApp.Activate
'Dim objMMMD As Object
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

'objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
'ReadOnly:=False, AddToRecentFiles:=False _
', PasswordDocument:="", _
'PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
'WritePasswordTemplate:="", XMLTransform:=""

'objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _

With objMMMD
MailMerge.OpenDataSource Name:=strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
Destination = wdSendToNewDocument
SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
Execute Pause:=False
Close wdDoNotSaveChanges
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...

I have implemented Doug's suggestions, but, while the code ran without
error, nothing was displayed - the application returned to the form in
MS Access from which the merge was initiated. The code that ran is
below: -

Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name

Set objApp = CreateObject("Word.Application")
'objApp.Visible = False
objApp.Activate
'Dim objMMMD As Object
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

'objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
'ReadOnly:=False, AddToRecentFiles:=False _
', PasswordDocument:="", _
'PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
'WritePasswordTemplate:="", XMLTransform:=""

'objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _

With objMMMD
MailMerge.OpenDataSource Name:=strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
Destination = wdSendToNewDocument
SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
Execute Pause:=False
Close wdDoNotSaveChanges
End With


'Dim intSplitName As Integer
'Dim intLength As Integer
'intLength = Len(strDocName)
'intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
'strDocName = Right(strDocName, intLength - intSplitName)


'objApp.Windows(strDocName).Activate
'objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
'objMMMD.Close SaveChanges:=False
'Set objMMMD = Nothing


'objApp.Documents.Open strDocName


'objApp.Visible = True
'objApp.Activate

End Sub

Murray

Doug Robbins - Word MVP;360910 Wrote:
Try the following (watch out for line breaks that may occur in the wrong

place.) I haven't really looked at the If... Else...End If
manipulations of
the Access data at the beginning to see if there is anything wrong with
it,
only at the Word part of the code.

Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String

On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Activate
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
With ObjMMMD
.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er _
ID=Admin;Date Source=strCurrentFileName;Mode=Read; _
Extended Properties="""";Jet OLEDB:System database=""""; _
Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet
OLE",
_
SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
.Execute Pause:=False
.Close wdDoNotSaveChanges
End With

End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
--



+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse



  #27   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

Doug, below you will find what I wrote in post no 25. After you had queried why I had commented out some code I cleaned up what I ran and tried again. I have copied in the code I used as well. In your latest post you have omitted the 'End Sub' - is that significant? I have checked my code against what you recommended and can find no difference - I am sure you must be correct!

"The reason I commented out all the other lines of code was so as to make it easier to get back to where I was if the recommendations did not succeed in solving the problem. I will back up the database and do it again, removing all the code not included in your recommendations. Then, if it fails again, I will be able to restore the module from the backup.

I have done as I said I would above, with the same result. Here is the code that ran."

Sub OpenWordDoc(strDocName As String, strLetterDescription As String, strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name

Set objApp = CreateObject("Word.Application")
objApp.Activate
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

With objMMMD
.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password=""" ";Us er ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = .ActiveRecord
.LastRecord = .ActiveRecord
End With
.Execute Pause:=False
.Close wdDoNotSaveChanges
End With

End Sub


Quote:
Originally Posted by Doug Robbins - Word MVP View Post
There are some significant differences in this section of the code in your
message from that which I provided, that would cause it not to compile
correctly:

Set objApp = CreateObject("Word.Application")
'objApp.Visible = False
objApp.Activate
'Dim objMMMD As Object
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

'objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
'ReadOnly:=False, AddToRecentFiles:=False _
', PasswordDocument:="", _
'PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
'WritePasswordTemplate:="", XMLTransform:=""

'objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _

With objMMMD
MailMerge.OpenDataSource Name:=strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
Destination = wdSendToNewDocument
SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
Execute Pause:=False
Close wdDoNotSaveChanges
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
  #28   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Word mailmerge - Visual Basic experts please help!

I would suggest that you take a look at the information provided in the
following post by fellow MVP Albert Kallal:

Actually, use my merge sample. it don't give that warning, and you don't
have to change registiry stuff etc.

The sample I have can be found he
http://www.members.shaw.ca/AlbertKal.../msaccess.html

What is nice/interesting about my sample is that is specially designed to
enable ANY form with ONE LINE of code....

Thus, each time you build a new form, you can word merge enable it with
great ease.

Make sure you read the instructions from above, and you should eventually
get to the follwoing page
http://www.members.shaw.ca/AlbertKal...rge/page2.html


Note that the merge can also use a query, and thus you don't have to merge
just "one" record..

After the merge occurs, you get a plain document WITHOUT any merge fields,
and this allows the end user to save, edit, or even email the document
(since the merge fields are gone after the merge occurs).

Give the above a try.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Murray Muspratt-Rouse" wrote
in message ...

Doug, below you will find what I wrote in post no 25. After you had
queried why I had commented out some code I cleaned up what I ran and
tried again. I have copied in the code I used as well. In your latest
post you have omitted the 'End Sub' - is that significant? I have
checked my code against what you recommended and can find no difference
- I am sure you must be correct!

"The reason I commented out all the other lines of code was so as to
make it easier to get back to where I was if the recommendations did
not succeed in solving the problem. I will back up the database and do
it again, removing all the code not included in your recommendations.
Then, if it fails again, I will be able to restore the module from the
backup.

I have done as I said I would above, with the same result. Here is the
code that ran."

Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name

Set objApp = CreateObject("Word.Application")
objApp.Activate
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

With objMMMD
MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password=""" ";Us er ID=Admin;Date
Source=strCurrentFileName;Mode=Read;Extended Properties="""";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
Destination = wdSendToNewDocument
SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
Execute Pause:=False
Close wdDoNotSaveChanges
End With

End Sub


Doug Robbins - Word MVP;361443 Wrote:
There are some significant differences in this section of the code in
your
message from that which I provided, that would cause it not to compile

correctly:

Set objApp = CreateObject("Word.Application")
'objApp.Visible = False
objApp.Activate
'Dim objMMMD As Object
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

'objApp.Documents.Open FileName:=strDocName,
ConfirmConversions:=False, _
'ReadOnly:=False, AddToRecentFiles:=False _
', PasswordDocument:="", _
'PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
'WritePasswordTemplate:="", XMLTransform:=""

'objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _

With objMMMD
MailMerge.OpenDataSource Name:=strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
Destination = wdSendToNewDocument
SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
Execute Pause:=False
Close wdDoNotSaveChanges
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP




+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
Murray Muspratt-Rouse



  #29   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

Thank you, Doug, for referring me to Albert Kallal's posts. I have decided to 'leave well alone', and persuade the users that being able to make changes to the merge document, and to the results of the merge, is exactly what they need!

Murray

Quote:
Originally Posted by Doug Robbins - Word MVP View Post
I would suggest that you take a look at the information provided in the
following post by fellow MVP Albert Kallal:

Actually, use my merge sample. it don't give that warning, and you don't
have to change registiry stuff etc.

The sample I have can be found he
http://www.members.shaw.ca/AlbertKal.../msaccess.html

What is nice/interesting about my sample is that is specially designed to
enable ANY form with ONE LINE of code....

Thus, each time you build a new form, you can word merge enable it with
great ease.

Make sure you read the instructions from above, and you should eventually
get to the follwoing page
http://www.members.shaw.ca/AlbertKal...rge/page2.html


Note that the merge can also use a query, and thus you don't have to merge
just "one" record..

After the merge occurs, you get a plain document WITHOUT any merge fields,
and this allows the end user to save, edit, or even email the document
(since the merge fields are gone after the merge occurs).

Give the above a try.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
  #30   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

Yesterday I installed my version of the Access application containing the mailmerge function on the users' PC, which is on Office 2003. When we tried a mailmerge the result was the merged document, using the VBA code as you last saw it that resulted with Word 2007 in the display of the main document ready for merge preview or the completion of the merge. It seems to me, therefore, that there must be some essential difference between the ways in which Word 2007 and Word 2003 work in this environment

Murray.
Quote:
Originally Posted by Murray Muspratt-Rouse View Post
Thank you, Doug, for referring me to Albert Kallal's posts. I have decided to 'leave well alone', and persuade the users that being able to make changes to the merge document, and to the results of the merge, is exactly what they need!

Murray
Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Word Visual Basic ksawb Microsoft Word Help 1 November 5th 07 11:33 AM
Visual Basic through Word help 4a4i4163211 Microsoft Word Help 0 October 2nd 07 05:22 PM
Problem with Visual Basic A in Word PJay Microsoft Word Help 1 April 9th 07 11:47 AM
MailMerge using Visual Basic ActiveX ref Mailmerge 2 February 1st 07 07:11 PM
How do I stop Visual Basic Macros from word? RobSlattery Microsoft Word Help 4 July 9th 05 07:26 AM


All times are GMT +1. The time now is 03:58 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"