Hi, I am having difficulty in accessing the Excel ActiveSheet object in a COM AddIn developed in VB.NET (2008 Express edition)
For this test I have two simple functions in the AddIn:
One function THANKS() just returns a string - I use it to prove that the COM AddIn is working.
The Other function is meant to return the name of the Active Worksheet.Here is the function in it's latest form:
Public Function ActName() As Object Implements ComIF.ActName 'returns active worksheet name Dim _Workbook1 As _Workbook Dim objAws As Object Dim aws As Worksheet objAws = _Workbook1.ActiveSheet aws = CType(objAws, Worksheet) ActName = aws.Name End Function
I get a compile warning "Variable '_Workbook1' is used before it has been assigned a value. A null reference exception could result at runtime.", but it compiles OK, and the 'Thanks' function works OK.The function =ActName() in a cell returns the #VALUE error
I would be most grateful if someone could suggest appropriate code to access the ActiveSheet object and get its name.
I have searched for many hours and tried various bits of code, but so far no luck.
Regards
Humar
Better question: What does the variable _Workbook1 contain?
Hi, The code shown was derived from this MSDN post which said:
Returns an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. Returns Nothing if no sheet is active. Read-only.Based on the above I assumed that _workbook was the active workbook.
But I see your point - _Workbook1 is DIM'd but not assigned.
I have just tried assigning it, but I am not clear what to assign it to.
I have:
Imports XL = Microsoft.Office.Interop.Excel,
so I tried =XL.ActiveWorkbook,but ActiveWorkbook is not part of XL
Do you have a suggestion as to how to assign _Workbook to the active Workbook.
Regards
Have you tried: XL.Application.ActiveWorkbook?
Hi, When I use that, I get the following warning:
Reference to a non-shared member requires an object reference.Microsoft.Office.Interop.Excel.Application.ActiveWorkbook gives the same error.
With:
Microsoft.Office.Interop.Excel.ActiveWorkbook I get a warning that ActiveWorkbook is not a member of Excel.I think that I have to access ActiveWorkbook or ActiveSheet from the Application object, and not through Excel. Possibly because the AddIn does not create the instance of Excel.
Regards
It doesn't create an instance of Excel, but it should have access to the current Application object. I don't have the Office SDK installed, so you're going to have to hunt it down yourself.
Hi, Thanks, but the reason I asked was because I couldn't hunt it down.
I have spent days trying to find the right structure to do this - Google is almost burnt out :)
I am probably missing something pretty basic, but at the moment I just can't see what it is.
If anyone has accessed the ActiveBook object from a VB.NET COM AddIn, and can share the code or at least the process, the help would be gratefully received.
Regards
In less than 5 minutes of looking, I found this article from MS. It's in C#, but it shouldn't be too hard to convert it to VB.NET.
Hi, Interestingly I found the equivalent article for VB.NET, earlier today and have been working through it.
It seems a long way round to gain access to the ActiveWorkbook, and was hoping for something more obvious - but as I haven't found anything else I am giving it a go.
Currently I am not able to add:
Imports Extensibility
to the declaration section. Extensibility does not appear in the list of available Imports.I have added a reference to this:
Microsoft Visual Basic for Applications Extensibility 5.3
having downloaded the PIA's for Office 2003, as this was PIA was missing.If you have any suggestions on this one, please let me know.
Thanks so much for the help.
Regards
I was able to get the function to work as follows: Public Function ActName() As Object Implements ComIF.ActName Dim myxl As XL.Application Dim ws As XL.Worksheet myxl = GetObject(, "Excel.application") ws = myxl.ActiveSheet ActName = ws.Name End FunctionThe Declarations section includes this line:
Imports XL = Microsoft.Office.Interop.ExcelThe solution was simple - my main problem was recognizing that XL as defined in the Import is only a reference to the Excel 'model' - and not a reference to the running Excel application.
GetObject returns the running Excel application, and from there accessing the Active worksheet name was easy.
Regards
Humar
Yes (14) | ![]() | |
No (14) | ![]() | |
I don't know (15) | ![]() |