Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Run Time Error '5631' automating merge from Word

Status
Not open for further replies.

smejicoiii

IS-IT--Management
Jan 8, 2015
5
US
Hi all first time here and I am trying to automate a merge document with input from the user. Debugging everything seems to be working fine until it gets time to finish and merge the document, any help would greatly be appreciated.

Code is below...

Sub testmerge()
'
' testmerge Macro
'
'
ActiveDocument.MailMerge.OpenDataSource Name:= _
"H:\worddata\Setup\database.mdb SetupFile.odc", ConfirmConversions:= _
False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=""DSN=CaseList;DBQ=\\10.0.0.10\database.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"";Initial Catalog=\\10.0.0.10\database.mdb" _
, SQLStatement:="SELECT * FROM `SetupFile`", SQLStatement1:="", SubType:= _
wdMergeSubTypeOther
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
Dim dsMain As MailMergeDataSource
Dim numRecord As Integer
Dim strReference As String
strReference = InputBox("Enter Client Reference")
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
Set dsMain = ActiveDocument.MailMerge.DataSource
If dsMain.FindRecord(FindText:=strReference, _
Field:="File_no") = True Then
numRecord = dsMain.ActiveRecord
End If
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With dsMain
.FirstRecord = numRecord
.LastRecord = numRecord
End With
.Execute Pause:=False
End With
End Sub
 
It is showing the error on the .Execute Pause:=False. Now if i run the routing and when prompted for input put nothing and hard code a record number for .FirstRecord = 1552 and .LastRecord = 1552 it will work as expected. I do not know if that helps you help me. Also I am using Word 2010. The error message is "word could not merge the main document with the data source because the data records were empty
 
Most of the code came from recording two different macros with the key strokes and then I was able to find the user input and then combining it all. Is there a better way to do this?
 
1st part is to ask for user input to locate the correct record...
ActiveDocument.MailMerge.OpenDataSource Name:= _
"H:\worddata\Setup\database.mdb SetupFile.odc", ConfirmConversions:= _
False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=""DSN=CaseList;DBQ=\\10.0.0.10\database.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"";Initial Catalog=\\10.0.0.10\database.mdb" _
, SQLStatement:="SELECT * FROM `SetupFile`", SQLStatement1:="", SubType:= _
wdMergeSubTypeOther
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
Dim dsMain As MailMergeDataSource
Dim numRecord As Integer
Dim strReference As String
strReference = InputBox("Enter Client Reference")
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
Set dsMain = ActiveDocument.MailMerge.DataSource
If dsMain.FindRecord(FindText:=strReference, _
Field:="File_no") = True Then
numRecord = dsMain.ActiveRecord
End If

this does work and brings up the record as the active one in the word document. However running the above makes the below one not work.

If i open the document manually connect to the datasource and then use the "find recipient" and type in to locate the record and then run the below macro it also works....

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
.LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
End With
.Execute Pause:=False
End With
End Sub

 
Sorry for the cross posting macropod, my bad. Thank you for the information I just quickly tried using the SkipIf you mentioned but was not able to get it to work. I will try to look into it more tomorrow. It seems that this may be a known bug with the findrecord and vba as i found. So by adding an additional findrecord statement that fails everything then works. but this lengthens the time for the merge to complete.
 
When presenting piece of code, please use CODE tags to show your code like this:

Code:
With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
        .FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
        .LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
    End With
    .Execute Pause:=False
End With

It is a lot easier to read.
(nothing new in this code, just the sample from your post)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top