Run Time Error '5631' automating merge from Word

[smejicoiii]

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

 

[SkipVought]

Hi,

Error on exactly which statement?

 

[smejicoiii]

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

 

[SkipVought]

How did you get this code?

 

[smejicoiii]

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?

 

[SkipVought]

Do the individual macros that you recorded, work as expected?

 

[smejicoiii]

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

 

[macropod]

Cross-posted at:

http://www.vbaexpress.com/forum/showthread.php?51492-Automate-Word-merge-document-with-user-input

For cross-posting etiquette, please read:

http://www.excelguru.ca/content.php?184

Cheers
Paul Edstein
[MS MVP - Word]

 

[JustinEzequiel]

for what it's worth, see this

 

[smejicoiii]

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.

http://homepage.swissonline.ch/cindymeister/MM2002/MM_VBA02.htm#FindRec2002

So by adding an additional findrecord statement that fails everything then works. but this lengthens the time for the merge to complete.

 

[Andrzejek]

When presenting piece of code, please use CODE tags to show your code like this:

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.