Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a COM-Class in Excel (Visual Basic 2005)

    Hello everybody,

    I try to do a simple task. I have Visual Studio 2005 Professional and Excel XP SP3 installed on the same computer. I want to create a COM-Class in VS2005 to use in Excel VBA. The source in .Net is very simple, just a proof of concept:
    <pre><ComClass(NetObject.ClassId, NetObject.InterfaceId, NetObject.EventsId)> _
    Public Class NetObject

    #Region "COM-GUIDs"
    ' Diese GUIDs stellen die COM-Identität für diese Klasse
    ' und ihre COM-Schnittstellen bereit. Wenn Sie sie ändern, können vorhandene
    ' Clients nicht mehr auf die Klasse zugreifen.
    Public Const ClassId As String = "bf961a06-fc3d-4cce-8308-56b8a2f0dff3"
    Public Const InterfaceId As String = "706b3aa2-f1f1-4477-a9cf-2dc751c7e4b2"
    Public Const EventsId As String = "d5378604-290f-4217-829f-62dcf244e1df"
    #End Region

    ' Eine erstellbare COM-Klasse muss eine Public Sub New()
    ' ohne Parameter aufweisen. Andernfalls wird die Klasse
    ' nicht in der COM-Registrierung registriert und kann nicht
    ' über CreateObject erstellt werden.
    Public Sub New()
    MyBase.New()
    End Sub

    Public Sub Test(ByVal arg As String)
    MsgBox(arg)
    End Sub
    End Class
    </pre>



    The "register for COM-Interop"-flag is set in the project properties

    In Excel, I set the reference to the corresponding *.tlb file. I can define a variable as type NetObject. Also, IntelliSense is working perfectly well. Only I cannot make the program run! I use the following code in Excel (just for your information, because I'm sure. it's not the code):

    <pre>Sub test()
    Dim oTest As NetObject.MyObject
    Set oTest = New NetObject.MyObject
    oTest.test "Success!"
    End Sub
    </pre>


    My problem is that I receive an automation error '-2146232576 (80131700)' and I have no idea how to prevent it.
    I am desperate, any help will be highly welcome!

    Here's some information about my system:
    Visual studio 2005
    Microsoft Visual Studio 2005
    Version 8.0.50727.762 (SP.050727-7600)
    Microsoft .NET Framework
    Version 2.0.50727 SP1

    Installierte Edition: Professional

    Microsoft Visual Basic 2005 77891-009-0000007-41421
    Microsoft Visual Basic 2005

    Visual Studio 2005 Tools for Office Second Edition 77891-009-0000007-41421
    Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System

    Crystal Report AAC60-H0CSA4B-U700045
    Crystal Reports für Visual Studio 2005


    EventToaster 1.0
    PowerToy demonstrating how to respond to IDE events from a package.

    IndexedFind 1.0
    Finds strings in files using Microsoft Indexing Service.

    Microsoft Visual Studio 2005 Professional Edition - DEU Service Pack 1 (KB926606)
    Dies ist ein Service Pack für Microsoft Visual Studio 2005 Professional Edition - DEU.
    Wenn Sie zu einem späteren Zeitpunkt einen aktuelleren Service Pack installieren, wird dieser Service Pack automatisch deinstalliert.
    Weitere Informationen erhalten Sie unter http://support.microsoft.com/kb/926606
    Excel XP:
    Microsoft Excel 2002 (10.6501.6714) SP3

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Using a COM-Class in Excel (Visual Basic 2005)

    If you check the Windows registry, has the object NetObject.MyObject been registered successfully?

    If you follow its CLSID, does it point to the correct location of your DLL file?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a COM-Class in Excel (Visual Basic 2005)

    Yes, it does! Unfortunately, the solution is somehow more complicated.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using a COM-Class in Excel (Visual Basic 2005)

    <P ID="edit" class=small>(Edited by rory on 22-Apr-08 11:31. Edited to add comments following testing!)</P>What is <code>MyObject</code>? I don't see any mention of it in your code.

    I replicated your code in VB2008 and called it from Excel using:
    <pre>Dim objTest as NetObject.NetObject
    Set objTest = New NetObject.NetObject
    objTest.Test "Success!"
    </pre>

    and it ran fine.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a COM-Class in Excel (Visual Basic 2005)

    Hello Rory,

    sorry for replying so lately (is this proper English?), but I had some other things in mind, i.e. a change of view with my boss. I visited him with my view and came back with his...
    You are right, MyObject is not in the code; it's name should be NetObject. Since I experimented with this a lot, I somehow mixed up two different attemts in my post. Please take my apologies.
    One question: which version of Excel is running on your computer? I only face this problem in combination with Excel XP. If your version was younger, I wouldn't be surprised.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using a COM-Class in Excel (Visual Basic 2005)

    I can't remember offhand which version I tested it on - my development laptop has 2000, 2002, 2003 and 2007 on it. I will check when I have a moment and get back to you. I suspect it was either 2002 or 2003.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using a COM-Class in Excel (Visual Basic 2005)

    Checked it with Excel 2002 - it worked fine.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a COM-Class in Excel (Visual Basic 2005)

    Hello Rory,

    thanks for checking. I still can't make it work. In a couple of days, I will receive office 2007 in addition to my actual installation. Hopefully, I will then get rid of my problem. But still, if anyone has an idea what's going wrong on my side, I would be grateful to learn about it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •