VBA - Automating Word from Excel

[passion_to_be_free]

I am working on a project where I need word and excel to talk to each
other. I don't know if this should go in the Word users group or the
Excel users group, so I will post it in both (I'm new to users groups,
so it that is against the "users group etiquette" let me know)

I have been successful at automating Excel from Word, and that code
seems to work just fine. However, I now have an excel spreadsheet that
needs to talk to a word document. Everytime I try to create a new
Word.Application object, I get the dreaded "Run-time error 429: ActiveX
object can't create object." here is my code:

Sub Test()

Dim wApp As Object
Dim wDoc As Object

Set wApp = CreateObject("Word.Application")
Set wDoc = wdApp.Documents.Add

wApp.Application.Dialogs(wdDialogFileOpen).Show
Set wDoc = wApp.Documents(1)

wDoc.Range(Start:=0).Select
wDoc.Application.Selection.TypeText ("this is a test")

wDoc.Save
wDoc.Close

End Sub

The error happens on the Set wApp = CreateObject line. I've also tried
wApp = new Word.Application with the same results. I also opened Tools

References and made sure that "Microsoft Word 11.0 Object Library"

has been checked.

All of the help files I can find on the internet deal with editing
registry keys and other PC only activities. I'm running Panther 10.2.8
and I am using Microsoft Office 2004. Does anyone know why this error
might be coming up? Why is it that I can automate excel from within
word, but when I try to automate word from excel, I get this error?

Thanks in advance.

 

[JE McGimpsey]

The error happens on the Set wApp = CreateObject line.

The problem you're having is that Mac applications are single instance,
so if Word is running, you can't create a new object.

Try something like:

Public Sub Test2()

Dim oWdApp As Object
Dim oWdDoc As Object

On Error Resume Next
Set oWdApp = GetObject(, "Word.Application")
On Error GoTo 0
If oWdApp Is Nothing Then _
Set oWdApp = CreateObject("Word.Application")
With oWdApp
Set oWdDoc = .Documents.Add
'.Application.Dialogs(wdDialogFileOpen).Show
'Set oWdDoc = .Documents(.Documents.Count)
oWdDoc.Range(Start:=0).Select
.Selection.TypeText ("this is a test")
oWdDoc.Save
oWdDoc.Close
End With
End Sub

 

[passion_to_be_free]

Works like a gem! In fact, I tried quitting Microsoft Word and running
my original code, and it worked. So it sounds like you're right. I
can only have one word.Application going at any given time.

Thanks.