[RESOLVED] Access excel tab name to use in form textbox

[snjdev]

I am in the process of creating an excel add-in. As the default value for one of my form textboxes, I would like to use the excel active worksheet name. How do I go about accessing this information. I am set up to create new tabs sucessfully, but I am unable to locate any commands to access the active worksheet's name so that I can store it in a variable.

Can anyone give me some insight into this problem?

I am new at this and I have been given this project with very little instruction. Trial by fire.

Thanks,
snjdev

[ForumAccount]

Imports Microsoft.Office.Interop
Public Class Form1
    Public xlsObj As New xlsObj
    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        xlsObj.xlsApp.Quit()
    End Sub
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        MsgBox(xlsObj.xlsWS.Name)
    End Sub
End Class

Public Class xlsObj
    Public xlsApp As New Excel.Application
    Public xlsWB As Excel.Workbook = xlsApp.Workbooks.Open("C:\test\test.xls")
    Public xlsWS As Excel.Worksheet = xlsWB.ActiveSheet
    Public Sub New()
        xlsApp.DisplayAlerts = False
        xlsApp.Visible = True
    End Sub
End Class

[snjdev]

I tried the provided solution, but it did not recognize the "Activesheet" reference when I ran it. I ge the following error.

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Workbook'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208DA-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

I think I need to explain my project better so.

I am working on an excel add-in, so when I am trying to access the name of the active worksheet that I just used, I already have excel open.

I have tried tons of different ways to access the activesheet name and assign it to a string variable, but I must not be doing something right.

Can anyone give me a little more direction on the topic?

Thanks,
snjdev

[Dnereb]

Did you try to search all workbooks in your application?

something like

Sub test()

Dim xlsApp As Excel.Application
Dim WB As Excel.Workbook
Dim Ws As Excel.Worksheet

Set xlsApp = GetObject(, "Excel.application")
Set Ws = xlsApp.ActiveSheet



End Sub

if this doesn't result in a sheet.... there is no activesheet.