CODEWITHSUNDEEP
vbadatasourcems-wordmailmerge
Daniel Olsen
Daniel Olsen

Reputation: 1050

Word Mail Merge recordcount returns -1

I am trying to go through a data source for a word mail merged document.

My code to run through the mail merge data source looks like this:

ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord
lastDoc = ActiveDocument.MailMerge.DataSource.RecordCount

Now everything works really well. I run through the data source and do what I need to do.

Now the user that I am making this for are, due to other reasons, using the small helper function: Mail Merger Helper (it exists in Word 2010)

When they use this however, my ActiveDocument.MailMerge.DataSource.RecordCount returns -1. Which means that it can't decide the number of records in the datasource. My assumption is that the Mail Merger Helper doesn't set the datasource correctly.

But that prevents me from iterating the mail merge (when he uses the helper, he can still activate the usual mail merge functions such as seeing the result of the merge).

What is wrong? Is there an alternative to iterating the merge, so I can get the records one by one?

Upvotes: 0

Views: 4264

Answers (2)

Daniel Olsen
Daniel Olsen

Reputation: 1050

Thank you bibadia, you lead me down the right track.

When you set the ActiveRecord to wdLastRecord. You can read the last data source off of ActiveRecord. So I just needed to grab that, and then set ActiveRecord to wdFirstRecord again.

Dim count As Integer

ActiveDocument.MailMerge.DataSource.ActiveRecord = wdLastRecord
count = ActiveDocument.MailMerge.DataSource.ActiveRecord
ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord

Upvotes: 7

user1379931
user1379931

Reputation:

Unfortunately, for some data sources, Word does set the RecordCount to -1. In some cases, that may be a result of Word using an ADO/OLE DB connection, because for example, when you retrieve a RecordSet using ADO, you sometimes have to use .MoveLast to retrieve the actual record count, and even that depends on the ADO Cursor type and other ADO settings. However, even for a DDE connection to Excel Word can return a -1 recordcount.

I wasn't completely clear exactly what you needed to be able to do, but in some cases you can establish the current largest record number by setting

theDoc.MailMerge.DataSource.ActiveRecord

to any number larger than the record count. I suspect the best you can do is

theDoc.MailMerge.DataSource.ActiveRecord = 2147483647#

However, I am not completely sure that even that is enough. For one thing, if the data source is a multi-user database, it is not inconceivable that the record count could change during the merge, so setting .lastrecord at the beginning may not actually work (although I would have thought that in most cases, you would actually be retrieving and processing the records in a Transaction, in which case you'd probably see the same set of records the whole time). You can deal with that possibility by trying to increment the .ActiveRecord in each iteration. If you have reached the end of the records, .ActiveRecord will not increment. On Windows Word, you may also have to deal with the possibility that the user can exclude individual records via the Edit Recipient List dialog. In that case, when you try to increment .ActiveRecord, it will actually skip to the next Included/Selected record (if any) and Word may also raise an error. So the following shows how you may be able to code for all the possibilities I know on Windows Word. (This particular version is not tested, though):

Sub MailMergeOneThingPerDataSourceRecord()
Dim lngSourceRecord As Long
Dim objMerge As Word.MailMerge
Dim bError As Boolean
Dim bTerminateMerge As Boolean
Set objMerge = ActiveDocument.MailMerge
bError = False
bTerminateMerge = False
With objMerge
  lngSourceRecord = 1
  Do Until bTerminateMerge
    On Error Resume Next
    .DataSource.ActiveRecord = lngSourceRecord
    If Err.Number = 0 Then
      On Error GoTo 0
      If .DataSource.ActiveRecord < lngSourceRecord Then
        bTerminateMerge = True
      Else
        lngSourceRecord = .DataSource.ActiveRecord
        .DataSource.FirstRecord = lngSourceRecord
        .DataSource.LastRecord = lngSourceRecord
        .Destination = wdSendToNewDocument
        .Execute
        lngSourceRecord = lngSourceRecord + 1
      End If
    Else
      bTerminateMerge = True
      If Err.Number <> 5853 Then
        bError = True
      End If
    End If
  Loop
End With
If bError Then
  ' deal with the error (you may prefer to do your error
  ' handling some other way) but e.g....
  MsgBox "Error " & Err.Number & ": " & Err.Description
End If
Set objMerge = Nothing
End Sub

However, that only really works if you know that the mail merge main document only consumes 1 data source record at a time. If it has fields such as { NEXT } in it, it may consume multiple records per iteration. If you know how many, you can attempt to skip the relevant number of records. If the number can vary (e.g. via { NEXTIF } statements, it is not obvious how you can detect in VBA exactly how many records have been consumed in each merge.

Upvotes: 3

Related Questions