IDTExten2ibility2 OnConnection method: Can't get it to start

[Nick Dreyer]

I am new to VB.NET coding for excel, and so am probably missing
something real elementary here:

I can get an Excel 97 VBA module to successfully include a reference
to a VB.NET project that contains the Connect class with the necessary
Guid and ProgId attributes and the five IDTExtensibility2 interface
methods, including OnConnection.

From the VBA module with the reference I need to fire-up code in the project
containing the Connect class and then have it accesses the instance of excel
with the calling VBA module. What piece of code am I missing to start up the
OnConnection method. The docs I read aren't too clear to me about how to do
this, making it sound like just the creation of the reference to the VB.NET
project containing the IDTExtensibility2 interface might set off OnConnection.
However so far I have yet to get a MsgBox to display from OnConnection.

I can get code in other classes contained in the same VB.NET project with the
IDTExtensibility2 interface to run from VBA, but nothing in OnConnection
and thus am unable to have the VB.NET project recognize my Excel 97 Workbook
as desired.

Thanks so much in advance for your insights


|\|.

[Rob Bovey]

Hi Nick,

The problem is your Excel version. Excel 2000 VBA was the first version
of Excel VBA to support the IDTExtensibility2 interface, so you won't be
able to get this working in Excel 97.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Nick Dreyer" <[email protected]> wrote in message
news:[email protected]...
>I am new to VB.NET coding for excel, and so am probably missing
> something real elementary here:
>
> I can get an Excel 97 VBA module to successfully include a reference
> to a VB.NET project that contains the Connect class with the necessary
> Guid and ProgId attributes and the five IDTExtensibility2 interface
> methods, including OnConnection.
>
> From the VBA module with the reference I need to fire-up code in the
> project
> containing the Connect class and then have it accesses the instance of
> excel
> with the calling VBA module. What piece of code am I missing to start up
> the
> OnConnection method. The docs I read aren't too clear to me about how to
> do
> this, making it sound like just the creation of the reference to the
> VB.NET
> project containing the IDTExtensibility2 interface might set off
> OnConnection.
> However so far I have yet to get a MsgBox to display from OnConnection.
>
> I can get code in other classes contained in the same VB.NET project with
> the
> IDTExtensibility2 interface to run from VBA, but nothing in OnConnection
> and thus am unable to have the VB.NET project recognize my Excel 97
> Workbook
> as desired.
>
> Thanks so much in advance for your insights
>
>
> |\|.

[Nick Dreyer]

Oh no! Is there no work-around to get an existing instance of Excel 97 to be
accessed from a COM Add-In that is launched from that Excel Instance?

|\|.

On Wed, 20 Jul 2005 04:51:05 -0400, "Rob Bovey" <[email protected]> wroth:

>Hi Nick,
>
> The problem is your Excel version. Excel 2000 VBA was the first version
>of Excel VBA to support the IDTExtensibility2 interface, so you won't be
>able to get this working in Excel 97.
>
>--
>Rob Bovey, Excel MVP
>Application Professionals
>http://www.appspro.com/
>
>* Take your Excel development skills to the next level.
>* Professional Excel Development
>http://www.appspro.com/Books/Books.htm
>
>"Nick Dreyer" <[email protected]> wrote in message
>news:[email protected]...
>>I am new to VB.NET coding for excel, and so am probably missing
>> something real elementary here:
>>
>> I can get an Excel 97 VBA module to successfully include a reference
>> to a VB.NET project that contains the Connect class with the necessary
>> Guid and ProgId attributes and the five IDTExtensibility2 interface
>> methods, including OnConnection.
>>
>> From the VBA module with the reference I need to fire-up code in the
>> project
>> containing the Connect class and then have it accesses the instance of
>> excel
>> with the calling VBA module. What piece of code am I missing to start up
>> the
>> OnConnection method. The docs I read aren't too clear to me about how to
>> do
>> this, making it sound like just the creation of the reference to the
>> VB.NET
>> project containing the IDTExtensibility2 interface might set off
>> OnConnection.
>> However so far I have yet to get a MsgBox to display from OnConnection.
>>
>> I can get code in other classes contained in the same VB.NET project with
>> the
>> IDTExtensibility2 interface to run from VBA, but nothing in OnConnection
>> and thus am unable to have the VB.NET project recognize my Excel 97
>> Workbook
>> as desired.
>>
>> Thanks so much in advance for your insights
>>
>>
>> |\|.
>
>

[Rob Bovey]

"Nick Dreyer" <[email protected]> wrote in message
news:[email protected]...
> Oh no! Is there no work-around to get an existing instance of Excel 97 to
> be
> accessed from a COM Add-In that is launched from that Excel Instance?

Hi Nick,

There is no support at all for COM add-ins in Excel 97. You can create
an ActiveX DLL that exposes a publicly creatable class with an
Excel.Application property. You can then build an Excel 97 VBA application
that creates an instance of that class and passes in a reference to its
Application object. Your DLL and Excel will then be able to talk to each
other through their respective references.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Nick Dreyer" <[email protected]> wrote in message
news:[email protected]...
> Oh no! Is there no work-around to get an existing instance of Excel 97 to
> be
> accessed from a COM Add-In that is launched from that Excel Instance?
>
> |\|.
>
> On Wed, 20 Jul 2005 04:51:05 -0400, "Rob Bovey" <[email protected]> wroth:
>
>>Hi Nick,
>>
>> The problem is your Excel version. Excel 2000 VBA was the first
>> version
>>of Excel VBA to support the IDTExtensibility2 interface, so you won't be
>>able to get this working in Excel 97.
>>
>>--
>>Rob Bovey, Excel MVP
>>Application Professionals
>>http://www.appspro.com/
>>
>>* Take your Excel development skills to the next level.
>>* Professional Excel Development
>>http://www.appspro.com/Books/Books.htm
>>
>>"Nick Dreyer" <[email protected]> wrote in message
>>news:[email protected]...
>>>I am new to VB.NET coding for excel, and so am probably missing
>>> something real elementary here:
>>>
>>> I can get an Excel 97 VBA module to successfully include a reference
>>> to a VB.NET project that contains the Connect class with the necessary
>>> Guid and ProgId attributes and the five IDTExtensibility2 interface
>>> methods, including OnConnection.
>>>
>>> From the VBA module with the reference I need to fire-up code in the
>>> project
>>> containing the Connect class and then have it accesses the instance of
>>> excel
>>> with the calling VBA module. What piece of code am I missing to start
>>> up
>>> the
>>> OnConnection method. The docs I read aren't too clear to me about how
>>> to
>>> do
>>> this, making it sound like just the creation of the reference to the
>>> VB.NET
>>> project containing the IDTExtensibility2 interface might set off
>>> OnConnection.
>>> However so far I have yet to get a MsgBox to display from OnConnection.
>>>
>>> I can get code in other classes contained in the same VB.NET project
>>> with
>>> the
>>> IDTExtensibility2 interface to run from VBA, but nothing in OnConnection
>>> and thus am unable to have the VB.NET project recognize my Excel 97
>>> Workbook
>>> as desired.
>>>
>>> Thanks so much in advance for your insights
>>>
>>>
>>> |\|.
>>
>>
>

[Nick Dreyer]

Bob:

Today I discovered that I can call a subroutine in a VB.NET COM Add-in
from Excel 97 passing it the "Me" parameter that gives the Add-in code access
to the calling instance of Excel. I was able to see all the Excel Objects
from the Add-in and it looks like, once I get myself oriented to the slightly
obscure syntax needed to manipulate those objects from VB.NET - I say
"obscure" because at the very least I see it's necessary to append the "value"
property to Excel Objects like Ranges which VBA assumed for you - I'll be able
to do anything I could with VBA from the COM Add-In.

I am surprised you say there is no COM ADd-In support for Excel 97. If that
is the case, why are the Office 97 libraries available in VB.NET?

Should I abandon the path I describe above? I realize that just because
I can access Excel 97 in this way I may not really be able to achieve
any dependable results. If you have anything to say that will convince me
of this, I'd be very appreciative to hear it as it may save me much
frustrating time down a dead-end road. You'll see I am posing this question
separately under a new thread.

Thanks, |\|.

On Wed, 20 Jul 2005 11:23:13 -0400, "Rob Bovey" <[email protected]> wroth:

>"Nick Dreyer" <[email protected]> wrote in message
>news:[email protected]...
>> Oh no! Is there no work-around to get an existing instance of Excel 97 to
>> be
>> accessed from a COM Add-In that is launched from that Excel Instance?
>
>Hi Nick,
>
> There is no support at all for COM add-ins in Excel 97. You can create
>an ActiveX DLL that exposes a publicly creatable class with an
>Excel.Application property. You can then build an Excel 97 VBA application
>that creates an instance of that class and passes in a reference to its
>Application object. Your DLL and Excel will then be able to talk to each
>other through their respective references.
>
>--
>Rob Bovey, Excel MVP
>Application Professionals
>http://www.appspro.com/
>
>* Take your Excel development skills to the next level.
>* Professional Excel Development
>http://www.appspro.com/Books/Books.htm
>
>"Nick Dreyer" <[email protected]> wrote in message
>news:[email protected]...
>> Oh no! Is there no work-around to get an existing instance of Excel 97 to
>> be
>> accessed from a COM Add-In that is launched from that Excel Instance?
>>
>> |\|.
>>
>> On Wed, 20 Jul 2005 04:51:05 -0400, "Rob Bovey" <[email protected]> wroth:
>>
>>>Hi Nick,
>>>
>>> The problem is your Excel version. Excel 2000 VBA was the first
>>> version
>>>of Excel VBA to support the IDTExtensibility2 interface, so you won't be
>>>able to get this working in Excel 97.
>>>
>>>--
>>>Rob Bovey, Excel MVP
>>>Application Professionals
>>>http://www.appspro.com/
>>>
>>>* Take your Excel development skills to the next level.
>>>* Professional Excel Development
>>>http://www.appspro.com/Books/Books.htm
>>>
>>>"Nick Dreyer" <[email protected]> wrote in message
>>>news:[email protected]...
>>>>I am new to VB.NET coding for excel, and so am probably missing
>>>> something real elementary here:
>>>>
>>>> I can get an Excel 97 VBA module to successfully include a reference
>>>> to a VB.NET project that contains the Connect class with the necessary
>>>> Guid and ProgId attributes and the five IDTExtensibility2 interface
>>>> methods, including OnConnection.
>>>>
>>>> From the VBA module with the reference I need to fire-up code in the
>>>> project
>>>> containing the Connect class and then have it accesses the instance of
>>>> excel
>>>> with the calling VBA module. What piece of code am I missing to start
>>>> up
>>>> the
>>>> OnConnection method. The docs I read aren't too clear to me about how
>>>> to
>>>> do
>>>> this, making it sound like just the creation of the reference to the
>>>> VB.NET
>>>> project containing the IDTExtensibility2 interface might set off
>>>> OnConnection.
>>>> However so far I have yet to get a MsgBox to display from OnConnection.
>>>>
>>>> I can get code in other classes contained in the same VB.NET project
>>>> with
>>>> the
>>>> IDTExtensibility2 interface to run from VBA, but nothing in OnConnection
>>>> and thus am unable to have the VB.NET project recognize my Excel 97
>>>> Workbook
>>>> as desired.
>>>>
>>>> Thanks so much in advance for your insights
>>>>
>>>>
>>>> |\|.
>>>
>>>
>>
>
>

[Rob Bovey]

Hi Nick,

<<Today I discovered that I can call a subroutine in a VB.NET COM Add-in
from Excel 97 passing it the "Me" parameter that gives the Add-in code
access to the calling instance of Excel.>>

This is logically identical to what I explained you'd have to do in my
last post.

<<I am surprised you say there is no COM ADd-In support for Excel 97. If
that is the case, why are the Office 97 libraries available in VB.NET?>>

A "COM Add-in" is simply the name for a new *add-in* architecture
introduced with Excel 2000. It has no connection with Excel VBA support for
COM in general. All current versions of Excel VBA are based on COM whether
or not they support COM add-ins. This is why you are able to reference the
Excel 97 object library just like any other COM object.

<<Should I abandon the path I describe above? I realize that just because I
can access Excel 97 in this way I may not really be able to achieve any
dependable results.>>

If Excel 97 is the earliest version you need to support, there's not
much point in keeping the IDTExtensibility2 interface stuff around because
you won't be able to use it. However, there's no reason you can't create a
COM DLL that is used by an Excel application. This is a reasonably common
Excel development pattern used to gain features that VBA doesn't offer.

The only fundamental difference between do this and using a COM add-in
is that you will always need a minimal Excel-side VBA application to
bootstrap the whole thing by creating and instance of your class(es) and
passing in a reference to the Excel Application object.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Nick Dreyer" <[email protected]> wrote in message
news:[email protected]...
> Bob:
>
> Today I discovered that I can call a subroutine in a VB.NET COM Add-in
> from Excel 97 passing it the "Me" parameter that gives the Add-in code
> access
> to the calling instance of Excel. I was able to see all the Excel Objects
> from the Add-in and it looks like, once I get myself oriented to the
> slightly
> obscure syntax needed to manipulate those objects from VB.NET - I say
> "obscure" because at the very least I see it's necessary to append the
> "value"
> property to Excel Objects like Ranges which VBA assumed for you - I'll be
> able
> to do anything I could with VBA from the COM Add-In.
>
> I am surprised you say there is no COM ADd-In support for Excel 97. If
> that
> is the case, why are the Office 97 libraries available in VB.NET?
>
> Should I abandon the path I describe above? I realize that just because
> I can access Excel 97 in this way I may not really be able to achieve
> any dependable results. If you have anything to say that will convince me
> of this, I'd be very appreciative to hear it as it may save me much
> frustrating time down a dead-end road. You'll see I am posing this
> question
> separately under a new thread.
>
> Thanks, |\|.
>
> On Wed, 20 Jul 2005 11:23:13 -0400, "Rob Bovey" <[email protected]> wroth:
>
>>"Nick Dreyer" <[email protected]> wrote in message
>>news:[email protected]...
>>> Oh no! Is there no work-around to get an existing instance of Excel 97
>>> to
>>> be
>>> accessed from a COM Add-In that is launched from that Excel Instance?
>>
>>Hi Nick,
>>
>> There is no support at all for COM add-ins in Excel 97. You can create
>>an ActiveX DLL that exposes a publicly creatable class with an
>>Excel.Application property. You can then build an Excel 97 VBA application
>>that creates an instance of that class and passes in a reference to its
>>Application object. Your DLL and Excel will then be able to talk to each
>>other through their respective references.
>>
>>--
>>Rob Bovey, Excel MVP
>>Application Professionals
>>http://www.appspro.com/
>>
>>* Take your Excel development skills to the next level.
>>* Professional Excel Development
>>http://www.appspro.com/Books/Books.htm
>>
>>"Nick Dreyer" <[email protected]> wrote in message
>>news:[email protected]...
>>> Oh no! Is there no work-around to get an existing instance of Excel 97
>>> to
>>> be
>>> accessed from a COM Add-In that is launched from that Excel Instance?
>>>
>>> |\|.
>>>
>>> On Wed, 20 Jul 2005 04:51:05 -0400, "Rob Bovey" <[email protected]>
>>> wroth:
>>>
>>>>Hi Nick,
>>>>
>>>> The problem is your Excel version. Excel 2000 VBA was the first
>>>> version
>>>>of Excel VBA to support the IDTExtensibility2 interface, so you won't be
>>>>able to get this working in Excel 97.
>>>>
>>>>--
>>>>Rob Bovey, Excel MVP
>>>>Application Professionals
>>>>http://www.appspro.com/
>>>>
>>>>* Take your Excel development skills to the next level.
>>>>* Professional Excel Development
>>>>http://www.appspro.com/Books/Books.htm
>>>>
>>>>"Nick Dreyer" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>>I am new to VB.NET coding for excel, and so am probably missing
>>>>> something real elementary here:
>>>>>
>>>>> I can get an Excel 97 VBA module to successfully include a reference
>>>>> to a VB.NET project that contains the Connect class with the necessary
>>>>> Guid and ProgId attributes and the five IDTExtensibility2 interface
>>>>> methods, including OnConnection.
>>>>>
>>>>> From the VBA module with the reference I need to fire-up code in the
>>>>> project
>>>>> containing the Connect class and then have it accesses the instance of
>>>>> excel
>>>>> with the calling VBA module. What piece of code am I missing to start
>>>>> up
>>>>> the
>>>>> OnConnection method. The docs I read aren't too clear to me about how
>>>>> to
>>>>> do
>>>>> this, making it sound like just the creation of the reference to the
>>>>> VB.NET
>>>>> project containing the IDTExtensibility2 interface might set off
>>>>> OnConnection.
>>>>> However so far I have yet to get a MsgBox to display from
>>>>> OnConnection.
>>>>>
>>>>> I can get code in other classes contained in the same VB.NET project
>>>>> with
>>>>> the
>>>>> IDTExtensibility2 interface to run from VBA, but nothing in
>>>>> OnConnection
>>>>> and thus am unable to have the VB.NET project recognize my Excel 97
>>>>> Workbook
>>>>> as desired.
>>>>>
>>>>> Thanks so much in advance for your insights
>>>>>
>>>>>
>>>>> |\|.
>>>>
>>>>
>>>
>>
>>
>