It works...it works not

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Naz - 31 Oct 2005 01:16 GMT

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

Signature

_______________________
Naz,
London

Reply to this Message

Jezebel - 31 Oct 2005 09:59 GMT

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

> Hi
>
[quoted text clipped - 14 lines]
>
> All help is appreciated

Reply to this Message

Tony Jollans - 31 Oct 2005 14:32 GMT

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

--
Enjoy,
Tony

> 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
[quoted text clipped - 31 lines]
> > Naz,
> > London

Reply to this Message

Rate this thread: