VB.NET COM AddIn - Activesheet object

Tags:
Compaq / Presario 5000
excel
Microsoft Office
June 28, 2010 at 06:09:19
Specs: Microsoft Windows XP Home, 3.079 GHz / 2046 MB
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


See More: VB.NET COM AddIn - Activesheet object

Report •

#1
June 28, 2010 at 06:12:07
Better question: What does the variable _Workbook1 contain?

Report •

#2
June 28, 2010 at 06:44:46
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


Report •

#3
June 28, 2010 at 09:55:47
Have you tried:
XL.Application.ActiveWorkbook
?

Report •

Related Solutions
See More ↓

#4
June 28, 2010 at 10:13:09
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


Report •

#5
June 28, 2010 at 11:50:40
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.

Report •

#6
June 28, 2010 at 12:09:06
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


Report •

#7
June 28, 2010 at 12:21:30
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.

Report •

#8
June 28, 2010 at 13:42:23
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


Report •

#9
June 30, 2010 at 04:06:55
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 Function

The Declarations section includes this line: 

Imports XL = Microsoft.Office.Interop.Excel

The 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


Report •
Start a discussion

Ask Question