Used A named ranged to get a sheet name in another workbook

Svgmassive
Svgmassive used Ask the Experts™
on
I am using the function below using a named range to get the sheet name in a closed workbook.Is there a better way.


Function GetSheetsNames(file, RAN_GE As String) As String: Dim wb As Object: Dim wsht As Excel.Worksheet
    On Error Resume Next
 Set wb = GetObject(file): Set wsht = wb.Worksheets(wb.Names(RAN_GE).RefersToRange.Parent.name): GetSheetsNames = wsht.name: wb.Close False: Set wb = Nothing
End Function
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Is there something wrong with the way it works? If not then I would just adjust things slightly including eliminating the jamming together of several statements into one line. That method doesn't gain you anything and only makes it harder to read and maintain.

Function GetSheetsNames(strFile, RAN_GE As String) As String
Dim wb As Excel.Workbook
Dim wsht As Excel.Worksheet

    On Error Resume Next
    Set wb = GetObject(strFile)
    Set wsht = wb.Worksheets(wb.Names(RAN_GE).RefersToRange.Parent.Name)
    GetSheetsNames = wsht.Name
    wb.Close False
    Set wb = Nothing
    Set wsht = Nothing
End Function

Open in new window

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial