It works...it works not

[Naz]

Hi

I have a macro that copies some cells from Excel and pastes them into Word.

However, the code stops at this line

Set oXL = GetObject(, "Excel.Application")

and gives the message "ActiveX component can't create object"

But the macro works fine if i copy the module to the Normal document template.

Do i have to declare the application call differentetly if calling from
within a document.


All help is appreciated

 

[Jezebel]

GetObject() will fail if you there's no existing instance of Excel to get.
In that case you need to use CreateObject() instead. Fortunately the error
is trappable, you so can use something like ---

ON error resume next
Set oXL = GetObject(, "Excel.Application")
on error goto 0

If oXL is nothing then
Set oXL = CreateObject("Excel.Application")
end if

 

[Tony Jollans]

I have no idea how you make it work in Normal and not anywhere else. It
should work if you have a currently running instance of Excel, and fail if
not.

AFAIK, the only way to deal with it is to trap the error

On Error Resume next
Set oXL = GetObject(, "Excel.Application")
If Err.Number = 429 then Set oXL = CreateObject("Excel.Application")
On Error Goto 0