PDA

View Full Version : Solved: Excel to Word mailmerge problem



lambic
06-21-2006, 09:06 AM
Hi everyone,

This is my first post so please bear with me!

I'm using Office 2003

I'm trying to automate a mailmerge to Word from Excel. I have a button which poplulates a range in Excel & then opens Word to merge the range data into some labels.

The Excel part works fine & the Word merge document opens, but the merge fails. I have some code in the Word DocumentOpen event, which looks like this:

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute

However, the code fails at the:

.Destination = wdSendToNewDocument

line with the following error:

Run-time error '5852'
Requested object is not available

I'm tearing whats left of my hair out with this, has anybody got any ideas?
:dunno
Many Thanks

austenr
06-21-2006, 09:29 AM
In you code do you have a reference set to this:

Microsoft Word 11.0 Object Library. Not saying this is your whole problem but it might get you a little closer. Also, could you post all of your code?

lambic
06-22-2006, 01:00 AM
Hi,

Thanks for the reply. Yep, got the reference, but still have the problem :(

mdmackillop
06-22-2006, 05:09 AM
Is this any help?
http://vbaexpress.com/kb/getarticle.php?kb_id=122

lambic
06-22-2006, 06:07 AM
Hi,

Cheers for the suggestion.

I tried the sample code, but it's still failing on the

.Destination = wdSendToNewDocument

line with the 'Requested object not available' error :(

lambic
06-22-2006, 07:26 AM
Sorted it!

I changed the way I was opening the Word doc from Excel from:

.documents.open
etc

to the hyperlink method:

ActiveWorkbook.FollowHyperlink Address:="C:\ etc", NewWindow:=True

as suggested in one of your other threads.

Thanks to mdmackillop & austenr for all your help.

mdmackillop
06-22-2006, 12:43 PM
Hi Lambic,
Glad you've got the solution. Can you post your final code for others to see?
Regards
MD

lambic
06-23-2006, 01:42 AM
Sure - code in Excel is:


stPathName = Worksheets("Label Generator").Range("Path").Value
stDocName = Worksheets("Label Generator").Range("Document").Value
stDocAndPath = stPathName + stDocName

'Start Word...

ActiveWorkbook.FollowHyperlink Address:=stDocAndPath, _
NewWindow:=True


and in Word (which has been set up as a mailmerge main doc), I have:


Public Sub MergeDoc()

Dim DocName As String

DocName = ActiveDocument.Name

'Display datasource to check ...

If ActiveDocument.MailMerge.DataSource.Name <> "" Then _
MsgBox ActiveDocument.MailMerge.DataSource.Name

'Perform mail merge ...

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With


Which is called on the Document - Open event when the doc is opened in Excel. The MsgBox bit just displays the datasource & was there to aid my checking.

Thanks again for your help.