Call VSTO through Automation GetVSTOBject is not working

Question

Hi,

I have an application-level Excel AddIn. This AddIn open an ExcelFile which contains a ListObject. To give this ListObject Data I set .datasource to an bindingsource oBject. All well.

To do this I do the following because as Microsoft.Office.Interop.ListObject has NO datasource property.

Dim vstoList As Microsoft.Office.Tools.Excel.ListObject = cWorksheet.ListObjects(1).GetVstoObject



vstoList.DataSource = bs

Running in debug it runs fine. Deployed everthing is fine.

No I expose the AddIn as COM-AddIn to acces it through my old VB6-App

Dim addIn As COMAddIn

Dim automationObject As Object

 

Set addIn = xlAnw.COMAddIns("TMK")

Set automationObject = addIn.Object

 

Dim x As Object

 

Set x = automationObject.Auftragsbestand

x.Create

Works fine. But in this case .GetVstoObejct() will return Nothing.

Any suggestions.

Bernhard

Answer 1

Can you show the full code for the Auftragsbestand method that you have exposed in your add-in? Also please show the code you use to expose the method (i.e. your override of RequestComAddInAutomationService), and specify which version of Visual Studio, Excel, and .NET Framework you are using.

I just tested this scenario out with a Windows Forms application (rather than a VB6 application), and I can generate a VSTO ListObject just fine when calling a method I exposed from my Excel add-in.

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Hi,

it's not the problem to create a ListObject than than getting the VSTOObject of it. I tried to adopt the the solution from Beth Massi (http://blogs.msdn.com/b/bethmassi/archive/2009/01/22/ado-net-data-services-building-an-excel-client.aspx)

The interessting part for me was that when you use VSTO the ListObject is extended by VSTO and the ListObject gets a "datasource" property.
Beth is is designing a document-level-addin. I need an application-level-AddIn. So I tried to to fully programmatically what she's doing in the designer.

This is the class for creating. I shortended it to the (for me) important parts...

Imports System.ComponentModel
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Tools.Excel
Imports Microsoft.Office.Tools.Excel.Extensions

Public Class Auftragsbestand
  Implements IAuftragsbestand

Private xlApplication As Microsoft.Office.Interop.Excel.Application

Public Sub New(ByRef xlApp As Microsoft.Office.Interop.Excel.Application)

    xlApplication = xlApp
End Sub

Public Function Create() As Boolean Implements IAuftragsbestand.Create
    ' Datentabelle ausgeben
    Dim frmProgress As Progress
    Try
      Dim cWorkbook As Microsoft.Office.Interop.Excel.Workbook
      Dim cWorksheet As Microsoft.Office.Interop.Excel.Worksheet
      Dim context As SVCStundenerfassung.stundenerfassungEntities
      Dim bs As New System.Windows.Forms.BindingSource
      Dim _ProjekteList As List(Of SVCStundenerfassung.DruckAusgabe_Auftragsbestand)
      Dim Allgemein As New TMKAddIn.Allgemein
      Dim ZielDatei As String

      ZielDatei = Allgemein.CreateFileName(_ZielDateiName)
      FileSystem.FileCopy(_Vorlage, _ZielPfad & ZielDatei)

      cWorkbook = xlApplication.Workbooks.Open(_ZielPfad & ZielDatei)

      context = Globals.ThisAddIn.Context

      Dim q = From p In context.DruckAusgabe_Auftragsbestand Select p
      _ProjekteList = q.ToList
      
      cWorksheet = cWorkbook.Sheets("Auftragsbestand")
                
      Dim vstoList As Microsoft.Office.Tools.Excel.ListObject = cWorksheet.ListObjects(1).GetVstoObject()

      vstoList.datasource = bs

      vstoList.ListColumns(1).Name = "letzter Eintrag"
      vstoList.ListColumns(1).DataBodyRange.NumberFormat = "0"
      vstoList.ListColumns(1).DataBodyRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter


' Doing a lot more of formmatting....

Return True
    Catch ex As Exception
      MsgBox(ex.Message)
      xlApplication.ScreenUpdating = True
      If Not frmProgress Is Nothing Then frmProgress.Close()
    End Try
  End Function
End Class

The Interface:

Public Interface IAuftragsbestand
  Function Create() As Boolean
  Property Vorlage() As String
  Property Mode() As CreateMode
  Property ZielDateiName() As String
  Property ZielPfad() As String
  ReadOnly Property Progress() As ICreateProgress
End Interface

For exposing the AddIn as COM-AddIn I followed this guide (http://msdn.microsoft.com/en-us/library/bb608614.aspx)

Public Class ThisAddIn
  Private utilities As AddInUtilities
  

  Protected Overrides Function RequestComAddInAutomationService() As Object
    If utilities Is Nothing Then
      utilities = New AddInUtilities()
    End If
    Return utilities
  End Function

End Class

And the Class "AddInUtilities"

Imports System.Runtime.InteropServices
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Win32

<ComVisible(True)> _
Public Interface IAddInUtilities
  Sub ImportData()
  Sub ImportData2()
  ReadOnly Property Stundenübersicht() As IStundenübersicht
  ReadOnly Property Auftragsbestand() As IAuftragsbestand
  ReadOnly Property Ausbildungsbericht() As IAusbildungsbericht
  Sub Test()
End Interface

<Guid("B4C17439-C35A-44e9-812A-DF0F6CCE8007"), ComVisible(True), ClassInterface(ClassInterfaceType.AutoDual)> _
Public Class AddInUtilities
  Implements IAddInUtilities
  Implements Extensibility.IDTExtensibility2

  Private xlApplication As Microsoft.Office.Interop.Excel.Application
  Private _Auftragsbestand As Auftragsbestand

<ComVisible(True)> _
  Public ReadOnly Property Auftragsbestand() As IAuftragsbestand Implements IAddInUtilities.Auftragsbestand
    Get
      If _Auftragsbestand Is Nothing Then
        _Auftragsbestand = New Auftragsbestand(Globals.ThisAddIn.Application)
      End If
      Return _Auftragsbestand
    End Get
  End Property
End Class

Also this code is shortend I left out all the parts I copied from upper named guide. Making it ComRegister, etc.

Now we have the following scenarios:

1) I call Auftragsbestand.Create() out of the AddIn eg. through a Ribbon-Button => Works

2) I start Excel open the VisualBasic-Editor, create a Macro:

Sub TestforAddIn()
 Application.COMAddIns("TMK").Object.Auftragsbestand.Create
End Sub

Works also very fine.

3) Call it via Excel-Com-Automation out of an window-app (VB6 / VB.NET)

Sub DoItinVB
 Dim xlAnw as Excel.Application
 Set xlAnw = CreateObject("Excel.Application")

 xlAnw.visible = True
 xlAnw.COMAddIns("TMK").Object.Auftragsbestand.Create()

 Set xlAnw = Nothing

End Sub

Calling it through automation will fail because this line of Class Auftragsbestand.Create

Dim vstoList As Microsoft.Office.Tools.Excel.ListObject = cWorksheet.ListObjects(1).GetVstoObject()

will not return the VSTObject of a ListObject it will return Nothing.

As far as I read now (eg. http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/e58e0951-47d3-4558-bbbf-47fde9a8202c) this Extension (Imports Microsoft.Office.Tools.Excel.Extensions) really needs to run inside as VSTO-"Area" which will not be created calling it through COMAutomation. I thought it will be the same...

I hope I could clearify my problem.

Thanks in advance.

P.S.: I did now a workaround leaving out the VSTO-Object (setting the datasource of ListObject) I create now an array of the data and assign it directly to the cells...

Dim data = New Object(_ProjekteList.Count + 1, 16) {}

      Dim i As Integer = 1

      For Each o In _ProjekteList
        data(i, 0) = o.letzterEintrag
        data(i, 1) = o.Auftragsnummer
        data(i, 2) = o.intBeschreibung
        data(i, 3) = o.Abrechnungsmodus
        data(i, 4) = o.Lieferdatum
        data(i, 5) = o.Kundenname
        data(i, 6) = o.Auftragswert
        data(i, 7) = o.geleistAuftragswert
        data(i, 8) = o.Stunden

        data(i, 11) = o.EASub
        data(i, 12) = o.EAextMa

        data(i, 14) = o.Provisionen

        i += 1
      Next

      Dim dataRange As Microsoft.Office.Interop.Excel.Range = cWorksheet.Range("A10").Resize(_ProjekteList.Count, 16)
      dataRange.Value = data

      cWorksheet.ListObjects.AddEx(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, cWorksheet.Range(dataRange.Address), , Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes).Name = "Auftragsbestand"

      Dim vstoList As Microsoft.Office.Interop.Excel.ListObject = cWorksheet.ListObjects(1)

So I dont have to get the VSTOObject to assign the datasource-property... May be a bit slower but running in all cases

Bernhard

 

Answer 3

Oh sorry forgot the environment:

VS 2008 SP1 , Excel 2007 SP1, Framework 3.5 SP1

 

 

Answer 4

I tried to use your code to produce a simple repro on my end, but there were too many undefined variables and other assumptions that I couldn't recreate on my end. I wound up creating a simple VB add-in that exposes a method that creates a VSTO ListObject, and I was able to confirm that it works when called from a Windows Forms application that automates Excel.

Looking at your code, the first thing I would try to is to derive your AddInUtilities class from StandardOleMarshalObject (in addition to implementing IAddInUtilities). You should always derive your exposed class from StandardOleMarshalObject if you want it to be callable from out-of-process clients, such as an application that automates Excel. This is pointed out in the "Avoiding a Race Condition in Out-of-Process Clients" section in http://msdn.microsoft.com/en-us/library/bb608621.aspx, but I think this recommendation could be made a bit stronger in this topic - I'll make sure this topic gets updated. See also the discussion in http://blogs.msdn.com/b/andreww/archive/2008/08/11/why-your-comaddin-object-should-derive-from-standardolemarshalobject.aspx.

Let me know if that fixes your problem.

A few other things I noticed about your code:

• Why does AddInUtilities implement IDTExtensibility2? IDTExtensibility2 should never be implemented explicitly in a VSTO add-in (the VSTO runtime does this for you).
• If the Auftragsbestand class is in your add-in project, it would be better to simply use Globals.ThisAddIn.Application to access the current Excel instance, rather than passing the Application object into the constructor. You can access Globals members from anywhere in your add-in project.
• If possible, you might want to consider simplifying your code by making Auftragsbestand and IAuftragsbestand the exposed class/interface, rather than exposing AddInUtilities/IAddInUtilities and using these to pass calls through to Auftragsbestand/IAuftragsbestand. I haven't seen your entire add-in, but what you have shown so far seems a bit unnecessarily complex, unless these multiple classes and interfaces are indeed necessary due to the overall design of your add-in.

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 5

Hi,

sorry but I tried to post code as much as possible unchanged for you.
In your method you created a ListObject and you have been able to assign a value to the datasource-Property of the VSTO-ListObject? Or in other words you have always been able to get a result from the GetVSTOObject-Method?

I followed another guide where the poster used IDTExtensibility2 I justed copied the code... Unfortenately I dont find anymore where I took it.

Using the application-Object through Globals is much easier yes, I didn't know that really.

I think I have to expose AddInUtilities because this will be something like a header class. Auftragsbestand is the first report I created now and more should follow...

I'll try to read an understand the you hints about StandardOleMarshalObject

Bernhard

Answer 6

Just try changing your AddInUtilities definition to the following.

Public Class AddInUtilities
 Inherits StandardOleMarshalObject
 Implements IAddInUtilities

This will make your AddInUtilities class derive from StandardOleMarshalObject. Let me know if this fixes your problem.

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 7

Hi OrgMulle,

How is the issue going on in your side? Have you solved the problem?

Best Regards,
Amy Li

---

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.