Used A named ranged to get a sheet name in another workbook
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
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