2

Excel objects like Worksheet, Workbook, Range etc. can be passed from VBA to .net as parameters when Microsoft's interop assembly is referenced:

.NET class:

using Excel = Microsoft.Office.Interop.Excel;

[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class MyExcelLib
{
    public MyExcelLib() {}  // default constructor

    public void InsertTable(Excel.Workbook wbook, string TableName, ...)
    {
       ...
    }
}

..compiled with Register for COM Interop option in Visual Studio, or with Regasm utility (see Newman's article for a nice overview).

VBA script (excel macro) calling .net method InsertTable():

Dim xLib As New MyExcelLib
Call xLib.InsertTable(ThisWorkbook, "Price", ..)

This works; Workbook object can be passed from VBA to .NET as parameter. But the same schema doesn't work with NetOffice assemblies:

using Excel = NetOffice.ExcelApi;
using NetOffice.ExcelApi.GlobalHelperModules;
using NetOffice.ExcelApi.Enums;

As far as I understand, the Embed Interop Types property of these assemblies can't be set to true, because this causes compilation errors. Apparently, it is not possible to pass excel objects as parameters using NetOffice. Is this true, or is there a way for doing this with NetOffice?

Community
  • 1
  • 1
tuncalik
  • 1,124
  • 1
  • 14
  • 20

1 Answers1

0

Use the native proxy instead of the NO wrapper as first argument in your library method. Something like this:

public void InsertTable(object wbook, string TableName, ...)
{
    Excel.Workbook myBook = new Excel.Workbook(null, wbook);

    myBook.Dispose();
}
Jarlax
  • 1,586
  • 10
  • 20
HappyWithoutCloud
  • 91
  • 5