Counting Merged Records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

G-Oker - 05 Sep 2007 10:36 GMT

Hello,

I've been writing a word macro that takes 2 excel files and merges
them into 2 different documents.

What I am trying to do is to count the Records being merged and
display them in a message box.
However, where ever I put MsgBox .RecordCount, I get a message box
saying -1.

The code I am currently using is :-

Sub MergeStickers()
'
' MergeStickersMacro
' Macro recorded 3/19/2007 by GOakham
'
strFileZ = Format$(Date, "yy-mm ")
Const sFILE As String = "Fulfillment"
Const sPATH As String = "\\Server\Dir\Dir2\Dir3\"
Const sVAR As String = ""
Const sVAR2 As String = " UK"

ChangeFileOpenDirectory "\\Server\Dir\Dir2\Dir3\"
Documents.Open FileName:="New Merge Settings2.doc",
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False,
PasswordDocument:="", _
PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:=sPATH & sVAR & _
Format(Now - 1, "dd-mm-yy ") & sFILE & ".csv" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="",
SQLStatement1 _
:="", SubType:=wdMergeSubTypeOther
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
LNewDOC = ActiveDocument.Name
End With
.Execute Pause:=False
End With

' Windows("New Merge Settings 2.doc").Activate
' ActiveWindow.ActivePane.VerticalPercentScrolled = 0
' Windows(1).Activate
'MsgBox ActiveDocument.Sections.Count & " Pages."
'MsgBox ActiveDocument.MailMerge.DataSource.RecordCount
''

ChangeFileOpenDirectory "\\Server\Dir\Dir2\Dir3\"
Documents.Open FileName:="New Merge Settings2.doc",
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False,
PasswordDocument:="", _
PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:=sPATH & sVAR & _
Format(Now - 1, "dd-mm-yy ") & sFILE & sVAR2 & ".csv" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="",
SQLStatement1 _
:="", SubType:=wdMergeSubTypeOther
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
MsgBox "Auto Merge complete.Now put the stickers in the Printer,and
press the OK Button", vbInformation, "Phew...Finished"
End Sub

Any help would be great

Reply to this Message

Peter Jamieson - 05 Sep 2007 19:51 GMT

You can't rely on a lot in this area, partly because Word cannot necessarily
get a reliable recordcount from its data source.

If you need to know at the beginning of the merge how many records are to be
merged,
a. and your source is something like an ADO source, you can always consider
using ADO to issue a SELECT COUNT query to return the record count.
b. or you need to rely on Word MailMerge capabilities (i.e. you do not know
what the data source is) then you'll probably have to cycle through the
records once before you actually merge,

I don't know if it will help, but the loop I have for doing "one merge per
record in the data source" looks like this:

With ActiveDocument
intSourceRecord = 1
bTerminateMerge = False
Do Until bTerminateMerge
.DataSource.ActiveRecord = intSourceRecord
If .DataSource.ActiveRecord <> intSourceRecord Then
bTerminateMerge = True
Else
.DataSource.FirstRecord = intSourceRecord
.DataSource.LastRecord = intSourceRecord
.Execute
intSourceRecord = intSourceRecord + 1
End If
Loop
End With

I'm not sure how you mght translate that into a situation where you want to
d o a single, multi-record merge, but all I can say is that as far as I
know, no property accessible vi VBA tells you how many records were merged.

The other approach is probably to use MailMerge Events to count the records
as you go along, and report the count at the end.

Signature

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

> Hello,
>
[quoted text clipped - 89 lines]
>
> Any help would be great

Reply to this Message