Get Excel Sheet Name

I want to get user-specified sheet name for his/her Excel file through VB code. Any help is appreciated.
Who is Participating?
SwordSaintConnect With a Mentor Commented:
Dim xlApp As excel.Application
Dim xlBook As excel.Workbook
Dim xlSheet As excel.Worksheet
dim name as string

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add(filepath)
Set xlSheet = name
'name = the name get from the inputbox

xlApp.Visible = True

'I leave the code to get data from the input box to you because I don't have the VB now. Hope this solve your problem.
Éric MoreauSenior .Net ConsultantCommented:
msgbox YourExcelObject.ActiveSheet.Name
I assume you know how to open excel file and create an Excel object.

    dim XlApp As Excel.Application
    Dim WS As Worksheet, Wsh As Sheets
    On Error Resume Next

    ' Open xlapp objet
    Set Wsh = XlApp.Workbooks(TmpFileName).Sheets
    If Wsh Is Nothing Then GoTo ExitNow
    If (Wsh.Count < 1) Then Exit Sub
    For Each WS In Wsh
        debug.print  WS.Name
    Set Wsh = Nothing

where tmpfilename is your excel file name
You can use ActiveWorkBook.Fullname to get the file name with path.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.