|
|
Having installed Office 2003 on another PC I would like to revisit a
problem I have reported before. The following code processes all letter
merge requests, starting with the deletion of the contents of
MergeTable, which provides the merge data, and the running of Access
2003 queries to load what is required: -
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 ("qryUpdateVolunteerRefereeLetterDate")
DoCmd.OpenQuery ("qryAppendVolunteerRefereedata")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateVolunteerRefereeChaserDate")
DoCmd.OpenQuery ("qryVolunteerRefereechaser")
Else
If strLetterDescription = "TRAINING DATES" Then
DoCmd.RunMacro ("Set up training dates data for
merge")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
End If
End If
If strFormName = "Clients" Then
If strLetterDescription = "REFERENCE CLIENT" Then
DoCmd.OpenQuery ("qryUpdateClientRefereeLetterDate")
DoCmd.OpenQuery ("qryAppendClientRefereedata")
Else
If strLetterDescription = "Client Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateClientRefereeChaserDate")
DoCmd.OpenQuery ("qryClientRefereechaser")
Else
DoCmd.OpenQuery ("qryClient")
End If
End If
End If
The code which follows does the merge - I think!
strCurrentFileName = CurrentDb.Name
Set objApp = CreateObject("Word.Application")
objApp.Visible = False
'objApp.Activate
'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:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDB:Database 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
The code which follows must be what closes the merge document and
leaves the result of the merge. I'm afraid I do not understand it at
all - I suppose I must get a good VBA programming guide (suggestions
welcome!)
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
If the result is meant to be more than one letter only one appears. The
only way to get all the letters out is to open the marge document in
Word, identify the Access table - again! - and complete the merge.
All suggestions gratefully received!
Murray
--
Murray Muspratt-Rouse
|
|
I would think that your problem is with the following lines of code
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
Which set the first and the last record to be merged to the active record -
i.e. one record.
--
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" <Murray.Muspratt-Rouse.3482c50[ at ]wordbanter.com> wrote
in message news:Murray.Muspratt-Rouse.3482c50[ at ]wordbanter.com...
[Quoted Text] >
> Having installed Office 2003 on another PC I would like to revisit a
> problem I have reported before. The following code processes all letter
> merge requests, starting with the deletion of the contents of
> MergeTable, which provides the merge data, and the running of Access
> 2003 queries to load what is required: -
>
> 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 ("qryUpdateVolunteerRefereeLetterDate")
> DoCmd.OpenQuery ("qryAppendVolunteerRefereedata")
> Else
> If strLetterDescription = "Referee chaser" Then
> DoCmd.OpenQuery ("qryUpdateVolunteerRefereeChaserDate")
> DoCmd.OpenQuery ("qryVolunteerRefereechaser")
> Else
> If strLetterDescription = "TRAINING DATES" Then
> DoCmd.RunMacro ("Set up training dates data for
> merge")
> Else
> DoCmd.OpenQuery ("qryVolunteer")
> End If
> End If
> End If
> End If
> If strFormName = "Clients" Then
> If strLetterDescription = "REFERENCE CLIENT" Then
> DoCmd.OpenQuery ("qryUpdateClientRefereeLetterDate")
> DoCmd.OpenQuery ("qryAppendClientRefereedata")
> Else
> If strLetterDescription = "Client Referee chaser" Then
> DoCmd.OpenQuery ("qryUpdateClientRefereeChaserDate")
> DoCmd.OpenQuery ("qryClientRefereechaser")
> Else
> DoCmd.OpenQuery ("qryClient")
> End If
> End If
> End If
>
> The code which follows does the merge - I think!
>
> strCurrentFileName = CurrentDb.Name
>
> Set objApp = CreateObject("Word.Application")
> objApp.Visible = False
> 'objApp.Activate
> '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:= _
> strCurrentFileName, _
> ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
> _
> AddToRecentFiles:=False, PasswordDocument:="",
> PasswordTemplate:="", _
> WritePasswordDocument:="", WritePasswordTemplate:="",
> Revert:=False, _
> Connection:= _
> "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
> ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
> Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
> Path="""";Jet OLEDB:Database 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
>
> The code which follows must be what closes the merge document and
> leaves the result of the merge. I'm afraid I do not understand it at
> all - I suppose I must get a good VBA programming guide (suggestions
> welcome!)
>
> 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
>
> If the result is meant to be more than one letter only one appears. The
> only way to get all the letters out is to open the marge document in
> Word, identify the Access table - again! - and complete the merge.
>
> All suggestions gratefully received!
>
> Murray
>
>
>
>
> --
> Murray Muspratt-Rouse
|
|
Doug Robbins - Word MVP;373990 Wrote:
> I would think that your problem is with the following lines of code
[Quoted Text] >
> FirstRecord = .ActiveRecord
> LastRecord = .ActiveRecord
>
> Which set the first and the last record to be merged to the active
> record -
> i.e. one record.
>
> --
> 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
>
> Thank you for that, Doug. That's what I thought! Can I just use quotes
> to remove
> "With .DataSource
> FirstRecord = .ActiveRecord
> LastRecord = .ActiveRecord
> End With"?
>
> Not a good idea! It locked the database...
>
> The odd thing is that running the merge under Office 2007 on my PC
> (which the client insisted that I install, despite the fact that the
> database is installed under Office 2003) I am able to display the
> recipients and print the letters - all of them! Perhaps the best
> solution would be to leave the main document open, so that the users
> can complete the merge themselves? How would that change the code?
>
> Murray
--
Murray Muspratt-Rouse
|
|
Use an apostrophe at the beginning of each line to convert it into a
comment.
--
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" <Murray.Muspratt-Rouse.348d50f[ at ]wordbanter.com> wrote
in message news:Murray.Muspratt-Rouse.348d50f[ at ]wordbanter.com...
[Quoted Text] >
> Doug Robbins - Word MVP;373990 Wrote:
>> I would think that your problem is with the following lines of code
>>
>> FirstRecord = .ActiveRecord
>> LastRecord = .ActiveRecord
>>
>> Which set the first and the last record to be merged to the active
>> record -
>> i.e. one record.
>>
>> --
>> 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
>>
>> Thank you for that, Doug. That's what I thought! Can I just use quotes
>> to remove
>> "With .DataSource
>> FirstRecord = .ActiveRecord
>> LastRecord = .ActiveRecord
>> End With"?
>>
>> Not a good idea! It locked the database...
>>
>> The odd thing is that running the merge under Office 2007 on my PC
>> (which the client insisted that I install, despite the fact that the
>> database is installed under Office 2003) I am able to display the
>> recipients and print the letters - all of them! Perhaps the best
>> solution would be to leave the main document open, so that the users
>> can complete the merge themselves? How would that change the code?
>>
>> Murray
>
>
>
>
> --
> Murray Muspratt-Rouse
|
|
That's just what I did! Please note what I said about the same code
producing all the letters when run with Word 2007.
I have ordered a copy of Access 2003 Vba Programmer's Reference (Wrox
Press) (Programmer to Programmer) in the hopes of learning a bit more
about VBA. Learn something new every day - even when you are as old as
me - a few days short of 79!
Murray
Doug Robbins - Word MVP;374051 Wrote:
> Use an apostrophe at the beginning of each line to convert it into a
[Quoted Text] > comment.
>
> --
> 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" Murray.Muspratt-Rouse.348d50f[ at ]wordbanter.com
> wrote
> in message news:Murray.Muspratt-Rouse.348d50f[ at ]wordbanter.com...-
>
> Doug Robbins - Word MVP;373990 Wrote:-
> I would think that your problem is with the following lines of code
>
> FirstRecord = .ActiveRecord
> LastRecord = .ActiveRecord
>
> Which set the first and the last record to be merged to the active
> record -
> i.e. one record.
>
> --
> 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
>
> Thank you for that, Doug. That's what I thought! Can I just use
> quotes
> to remove
> "With .DataSource
> FirstRecord = .ActiveRecord
> LastRecord = .ActiveRecord
> End With"?
>
> Not a good idea! It locked the database...
>
> The odd thing is that running the merge under Office 2007 on my PC
> (which the client insisted that I install, despite the fact that the
> database is installed under Office 2003) I am able to display the
> recipients and print the letters - all of them! Perhaps the best
> solution would be to leave the main document open, so that the users
> can complete the merge themselves? How would that change the code?
>
> Murray-
>
>
>
>
> --
> Murray Muspratt-Rouse -
--
Murray Muspratt-Rouse
|
|
Try
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
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" <Murray.Muspratt-Rouse.34a34a0[ at ]wordbanter.com> wrote
in message news:Murray.Muspratt-Rouse.34a34a0[ at ]wordbanter.com...
[Quoted Text] >
> That's just what I did! Please note what I said about the same code
> producing all the letters when run with Word 2007.
>
> I have ordered a copy of Access 2003 Vba Programmer's Reference (Wrox
> Press) (Programmer to Programmer) in the hopes of learning a bit more
> about VBA. Learn something new every day - even when you are as old as
> me - a few days short of 79!
>
> Murray
> Doug Robbins - Word MVP;374051 Wrote:
>> Use an apostrophe at the beginning of each line to convert it into a
>> comment.
>>
>> --
>> 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" Murray.Muspratt-Rouse.348d50f[ at ]wordbanter.com
>> wrote
>> in message news:Murray.Muspratt-Rouse.348d50f[ at ]wordbanter.com...-
>>
>> Doug Robbins - Word MVP;373990 Wrote:-
>> I would think that your problem is with the following lines of code
>>
>> FirstRecord = .ActiveRecord
>> LastRecord = .ActiveRecord
>>
>> Which set the first and the last record to be merged to the active
>> record -
>> i.e. one record.
>>
>> --
>> 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
>>
>> Thank you for that, Doug. That's what I thought! Can I just use
>> quotes
>> to remove
>> "With .DataSource
>> FirstRecord = .ActiveRecord
>> LastRecord = .ActiveRecord
>> End With"?
>>
>> Not a good idea! It locked the database...
>>
>> The odd thing is that running the merge under Office 2007 on my PC
>> (which the client insisted that I install, despite the fact that the
>> database is installed under Office 2003) I am able to display the
>> recipients and print the letters - all of them! Perhaps the best
>> solution would be to leave the main document open, so that the users
>> can complete the merge themselves? How would that change the code?
>>
>> Murray-
>>
>>
>>
>>
>> --
>> Murray Muspratt-Rouse -
>
>
>
>
> --
> Murray Muspratt-Rouse
|
|
Doug, I have tried your suggestion. "wdDefaultFirst/LastRecord" seemed
to prevent the display of any result. Removing objapp from "With
objapp.ActiveDocument.MailMerge" had the same effect. Having run the
Access process to get only the first copy I then opened the document
with Word, asked for the merge data to be loaded, and got all the
letters. That is why I said I thought I would try to leave the Word
window open, so that after Access had returned to the form from which
the process is triggered it would be easy to produce all the letters.
I have suggested to the client that we should install Office 2007!
Murray
Doug Robbins - Word MVP;374110 Wrote:
> Try
[Quoted Text] >
> With ActiveDocument.MailMerge
> .Destination = wdSendToNewDocument
> .SuppressBlankLines = True
> With .DataSource
> .FirstRecord = wdDefaultFirstRecord
> .LastRecord = wdDefaultLastRecord
> End With
> .Execute Pause:=False
> 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
|
|
Or just
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
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
"Doug Robbins - Word MVP" <dkr[ at ]REMOVECAPSmvps.org> wrote in message
news:OW1MRD6NJHA.1172[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > Try
>
> With ActiveDocument.MailMerge
> .Destination = wdSendToNewDocument
> .SuppressBlankLines = True
> With .DataSource
> .FirstRecord = wdDefaultFirstRecord
> .LastRecord = wdDefaultLastRecord
> End With
> .Execute Pause:=False
> 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" <Murray.Muspratt-Rouse.34a34a0[ at ]wordbanter.com>
> wrote in message news:Murray.Muspratt-Rouse.34a34a0[ at ]wordbanter.com...
>>
>> That's just what I did! Please note what I said about the same code
>> producing all the letters when run with Word 2007.
>>
>> I have ordered a copy of Access 2003 Vba Programmer's Reference (Wrox
>> Press) (Programmer to Programmer) in the hopes of learning a bit more
>> about VBA. Learn something new every day - even when you are as old as
>> me - a few days short of 79!
>>
>> Murray
>> Doug Robbins - Word MVP;374051 Wrote:
>>> Use an apostrophe at the beginning of each line to convert it into a
>>> comment.
>>>
>>> --
>>> 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" Murray.Muspratt-Rouse.348d50f[ at ]wordbanter.com
>>> wrote
>>> in message news:Murray.Muspratt-Rouse.348d50f[ at ]wordbanter.com...-
>>>
>>> Doug Robbins - Word MVP;373990 Wrote:-
>>> I would think that your problem is with the following lines of code
>>>
>>> FirstRecord = .ActiveRecord
>>> LastRecord = .ActiveRecord
>>>
>>> Which set the first and the last record to be merged to the active
>>> record -
>>> i.e. one record.
>>>
>>> --
>>> 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
>>>
>>> Thank you for that, Doug. That's what I thought! Can I just use
>>> quotes
>>> to remove
>>> "With .DataSource
>>> FirstRecord = .ActiveRecord
>>> LastRecord = .ActiveRecord
>>> End With"?
>>>
>>> Not a good idea! It locked the database...
>>>
>>> The odd thing is that running the merge under Office 2007 on my PC
>>> (which the client insisted that I install, despite the fact that the
>>> database is installed under Office 2003) I am able to display the
>>> recipients and print the letters - all of them! Perhaps the best
>>> solution would be to leave the main document open, so that the users
>>> can complete the merge themselves? How would that change the code?
>>>
>>> Murray-
>>>
>>>
>>>
>>>
>>> --
>>> Murray Muspratt-Rouse -
>>
>>
>>
>>
>> --
>> Murray Muspratt-Rouse
>
>
|
|
Doug, I realised I had to add another reference to VBA - the Word OLB. I
also had to 'compact and repair' the database. Now it works with
wdDefaultFirstRecord and wdDefaultLastRecord.
Thank you again for all your efforts.
Murray
Doug Robbins - Word MVP;374138 Wrote:
> Or just
[Quoted Text] >
> With ActiveDocument.MailMerge
> .Destination = wdSendToNewDocument
> .Execute
> 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
>
> "Doug Robbins - Word MVP" dkr[ at ]REMOVECAPSmvps.org wrote in message
> news:OW1MRD6NJHA.1172[ at ]TK2MSFTNGP03.phx.gbl...[color=blue][i]
> Try
>
> With ActiveDocument.MailMerge
> .Destination = wdSendToNewDocument
> .SuppressBlankLines = True
> With .DataSource
> .FirstRecord = wdDefaultFirstRecord
> .LastRecord = wdDefaultLastRecord
> End With
> .Execute Pause:=False
> 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
|
|
|