Hopefully a straight forward VBA query

[danboi10]

Hi,

Im very new to this and it's the first time I've posted in here. I have tried to solve the issue by search the web but I don't think that I fully understand the problem.

When I run the code everything works fine; it opens the word document mail merge, then it open's the find entry box in the mailings tab on the ribbon. The problem occurs when I go back to the access document and I get a run time error '424' Object required. When i click debug the

WordBasic.MailMergeFindEntry.Execute

is highlighted in yellow.

Below is the code in full

Public Sub cmd_Run_Click()

Dim WrdApp As Word.Application
Dim WrdDoc As Word.Document
Set WrdApp = CreateObject("Word.Application")
WrdApp.Visible = True
WrdApp.Activate
Set WrdDoc = WrdApp.Documents.Open("J:\filepath.docx")
With WrdDoc.mailmerge
               .MainDocumentType = wdFormLetters
               .Destination = wdSendToNewDocument
               .OpenDataSource Name:="J:\filepath.mdb", sqlstatement:="SELECT * FROM [CRB]"
               .ViewMailMergeFieldCodes = wdToggle
          WordBasic.MailMergeFindEntry.Execute

    End With
End Sub

Any help would be appreciated.

 

[pr2-eugin]

What is WordBasic ??

 

[danboi10]

The only way i could figure out how to code the 'find entry' box was to record a macro in word07 then open VB editor and copy and paste the code into Access 07.

the wordbasic code is what the word07 macro recorded. As i said it does work first time around it's only on the second click of the button that the error is recorded.

 

[mdlueck]

As i said it does work first time around it's only on the second click of the button that the error is recorded.

I seem to remember reading somewhere of trouble encountered on subsequent executions when each connection does not clean up its connection / objects correctly. Since you say the code works fine once, sounds like this is yet another case.

I would look into how to correctly terminate / cleanup the connection.

Perhaps try something along the lints of:

  'Clean up the connection to the database
  Set adoCMD = Nothing
  Set adoRS = Nothing

At the end of your sub to both of the objects created with the Set statements.

 

[danboi10]

Thanks for the replies. I did try the clean up code but I'm still getting the same run time error.

One thing that did occur to me as a possible issue was when I call up the 'find entry' box and enter the unique find number the only way I can then close the 'find entry' box is by press the cancel button. I don't know if this would be an issue with regards to breaking the code?

 

[boblarson]

The problem is that you have code that is not associated with the Word application object you instantiated and so it is opening a hidden instance as well which then causes errors on subsequent attempts. The fix from what I see is to add a period before the WordBasic word.

 

[boblarson]

Now that I'm not posting from my phone, I can post this link. Read it to understand what I was saying in my previous post. It talks about Excel but it is applicable to anything:

http://www.btabdevelopment.com/ts/excelinstance

 

[danboi10]

I know this is the most basic of questions but what would the 'add period' be? I am very new to access....this week! It was given to me as a project and through a hatchet job of recording macro's and searching the web I've been able to produce a search, find and email function...that doesn't work! Well it all works once but as i said it's the wordbasic function that keeps failing so I am unable to repeat the command unless i close and reopen access.

Again may thanks for any help provided.

 

[boblarson]

I know this is the most basic of questions but what would the 'add period' be? I am very new to access....this week! It was given to me as a project and through a hatchet job of recording macro's and searching the web I've been able to produce a search, find and email function...that doesn't work! Well it all works once but as i said it's the wordbasic function that keeps failing so I am unable to repeat the command unless i close and reopen access.

Again may thanks for any help provided.

See the exaggerated period below (I did that so you could actually see it without straining to try to find it.

Public Sub cmd_Run_Click()
 
Dim WrdApp As Word.Application
Dim WrdDoc As Word.Document
Set WrdApp = CreateObject("Word.Application")
WrdApp.Visible = True
WrdApp.Activate
Set WrdDoc = WrdApp.Documents.Open("J:\filepath.docx")
With WrdDoc.mailmerge
               .MainDocumentType = wdFormLetters
               .Destination = wdSendToNewDocument
               .OpenDataSource Name:="J:\filepath.mdb", sqlstatement:="SELECT * FROM [CRB]"
               .ViewMailMergeFieldCodes = wdToggle
          [B][SIZE=6][COLOR=red].[/COLOR][/SIZE][/B]WordBasic.MailMergeFindEntry.Execute
 
    End With
End Sub

 

[pr2-eugin]

Just replace WordBasic.MailMergeFindEntry.Execute with .WordBasic.MailMergeFindEntry.Execute

 

[danboi10]

Hi Bob,

I think we almost posted at the same time so whilst I was asking for a bit more info you were sending me the link!

Thanks will take a look into it.

 

[danboi10]

That's got it. Many thanks for your help.

For ref it works much better without the .execute!