Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
Quote:
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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 |
#7
|
|||
|
|||
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:
|
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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 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
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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:
|
#12
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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:
|
#14
|
|||
|
|||
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:
|
#15
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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:
|
#18
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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:
|
#20
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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 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
|
|||
|
|||
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:
|
#22
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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 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
|
|||
|
|||
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:
|
#24
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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:
Last edited by Murray Muspratt-Rouse : August 29th 08 at 11:20 AM Reason: Completion of further test of code |
#26
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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:
|
#28
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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:
|
#30
|
|||
|
|||
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. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word Visual Basic | Microsoft Word Help | |||
Visual Basic through Word help | Microsoft Word Help | |||
Problem with Visual Basic A in Word | Microsoft Word Help | |||
MailMerge using Visual Basic ActiveX | Mailmerge | |||
How do I stop Visual Basic Macros from word? | Microsoft Word Help |