Jeremy McClanathan
Posts: 20
Joined: 2017-02-09
|
Hello,
How do I reference Excel from a class library? I am testing the process of using class libraries to store code that will be used among multiple add-ins. I created a class library with a simple function that passes some text to my add-in and got that to work. Then, I tried creating a sub that directly adds some text to cell A1 from the class library and got an exception that I haven't been able to find an answer to yet. I am guessing that I don't have some reference that I need. So, my questions are:
1. Which references do I need in a class library to modify ranges and tables in Excel?
2. More generally, how do I find out what references I need for code I would like to write?
Below is the code from my sample class library. It builds successfully, but throws an exception when called from Excel.
Imports excel = Microsoft.Office.Interop.Excel
Public Class clibTestClassLib4
Public Shared Sub main()
End Sub
Public Sub TestingExcelSub()
Dim e As New excel.Application
e.Range("A1").Value = "This text is directly from the class library which references excel"
End Sub
End Class
Partial Exception Message:
Exception Source: Microsoft.Office.Interop.Excel
Exception Type: System.Runtime.InteropServices.COMException
Exception Message: Exception from HRESULT: 0x800A03EC
Exception Target Site: get_Range
Thanks for your help!Jeremy |
|
Andrei Smolin
Add-in Express team
Posts: 18829
Joined: 2006-05-11
|
Hello Jeremy,
A COM add-in should only use the Application object that Office passes to it at startup. In Add-in Express that object is available via the HostApplication object; the project wizard also generates a host-specific property such as ExcelApp.
That is, you shouldn't write Dim e As New excel.Application.
Instead, you can pass the value of the ExcelApp property to the constructor of a class from the class library; in the code fragment you supplied, this is the clibTestClassLib4 class.
Consider this raw sketch:
Imports Excel = Microsoft.Office.Interop.Excel
Public Class clibTestClassLib4
Dim theExcelApp as Excel.Application
Public Sub New(anExcelApp as Excel.Application)
theExcelApp = anExcelApp
End Sub
Public Sub TestingExcelSub()
theExcelApp.Range("A1").Value = "This text is directly from the class library which references excel"
End Sub
End Class
Andrei Smolin
Add-in Express Team Leader |
|
Jeremy McClanathan
Posts: 20
Joined: 2017-02-09
|
Thank you. I tried the code you provided and it builds successfully, but gets the same exception. See below. Can you provide some insight as to why it is throwing this exception?
- Exception Source: Microsoft.Office.Interop.Excel
- Exception Type: System.Runtime.InteropServices.COMException
- Exception Message: Exception from HRESULT: 0x800A03EC
- Exception Target Site: get_Range
Jeremy |
|
Andrei Smolin
Add-in Express team
Posts: 18829
Joined: 2006-05-11
|
|