Home |
Search |
Today's Posts |
|
#1
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 |
#2
|
|||
|
|||
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:
|
#4
|
|||
|
|||
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:
|
#5
|
|||
|
|||
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 |