Remotely getting all the tab names of a given excel spreadsheet

Screenshots / code below refer:

VB code:

adapted from Microsoft office and (chiefly) Dalgleish, D (2016)

Option Explicit
Sub Button1_Click()
    Dim fd As Office.FileDialog
    Dim j As Integer
    Dim file As Variant
    Dim aWbk As Workbook
    Dim aSh As String
    Dim lcount, lfields As Long
    
    Set aWbk = ActiveWorkbook
    aSh = ActiveSheet.Name
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    j = -1
    aWbk.Sheets(aSh).Range("B:G").Clear
    With fd
        .Filters.Clear
        .Title = "Select an Excel File"
        .Filters.Add "Excel Files", "*.xlsx?", 1
        .AllowMultiSelect = True
        
        'Dim sFilePaths As Array(String)
    
        If .Show = -1 Then
              lcount = 2
              lfields = 6
            
              With aWbk.Sheets(aSh)
                 .Range(.Cells(1, 2), _
                 .Cells(1, lfields + 1)).Value _
                  = Array( _
                        "File Name", _
                        "Sheet Name", _
                        "Used Range", _
                        "Range Cells", _
                        "Shapes", _
                        "Last Cell")
            End With

            For Each file In .SelectedItems
                Call ListSheetsRangeInfo(file, 3 * j, aWbk, aSh)
            Next

        End If
    End With
    

End Sub


Sub ListSheetsRangeInfo(sTheSourceFile As Variant, j As Integer, aWbk As Workbook, aSh As String)
'https://contexturesblog.com/archives/2016/03/10/list-all-excel-sheets-with-used-range/
Dim ws As Worksheet
Dim lcount As Long
Dim wsTemp As Worksheet
Dim rngF As Range
Dim lfields As Long
Dim strLC As String
Dim strSh As String
Dim sha As Shape
Dim sh As Variant

Application.EnableEvents = False
Application.ScreenUpdating = False
Dim src As Workbook
'On Error Resume Next
  
  Set src = Workbooks.Open(sTheSourceFile, False, True)      ' Open the source file.
  lfields = 6
  'lbcount = lcount
  src.Activate
  For Each sh In src.Sheets
      strLC = sh.Cells _
        .SpecialCells(xlCellTypeLastCell) _
          .Address
      If sh.ProtectContents = True Then
        strSh = ""
      Else
        If sh.Shapes.Count > 0 Then
          strSh = ""
          For Each sha In sh.Shapes
            strSh = strSh & sha.TopLeftCell _
                .Address & ", "
          Next sha
          strSh = Left(strSh, Len(strSh) - 2)
        End If
      End If
      
      With aWbk.Sheets(aSh)
        lcount = .Range("b1:b" & .Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count + 1
        .Range(.Cells(lcount, 2), _
          .Cells(lcount, lfields + 1)).Value _
          = Array( _
              sTheSourceFile, _
              sh.Name, _
              sh.UsedRange.Address, _
              sh.UsedRange.Cells.Count, _
              strSh, _
              strLC)
        'add hyperlink to sheet name
        .Hyperlinks.Add _
            Anchor:=.Cells(lcount, 2), _
            Address:=sTheSourceFile, _
            SubAddress:="'" & sh.Name _
                & "'!A1", _
            ScreenTip:=sh.Name, _
            TextToDisplay:=sTheSourceFile
        'add hyperlink to last cell
        .Hyperlinks.Add _
            Anchor:=.Cells(lcount, lfields + 1), _
            Address:=sTheSourceFile, _
            SubAddress:="'" & sh.Name _
                & "'!" & strLC, _
            ScreenTip:=strLC, _
            TextToDisplay:=strLC
        
        
      End With
  Next sh
 
With aWbk.Sheets(aSh)
    .Range(.Cells(1, 2), .Cells(1, lfields + 1)) _
      .EntireColumn.AutoFit
    .Rows(1).Font.Bold = True
End With

Application.EnableEvents = True
Application.ScreenUpdating = True


src.Close False         ' False, so you don't save the source file.
Set src = Nothing

End Sub

Mechanics

Comprises 2 modules:

1. Button1_Click() : to attach to a command button (or can run directly from VBA project explorer) - this module allows you to browse for files of interest (multi-selection possible). It then calls the next module/program (2), and repeats this step for every file you selected

2. ListSheetsRangeInfo() : retrieves relevant info (used ranges, sheetnames etc) from excel files you selected in module 1 - info returned will be unaffected by whatever modifications users made (it will simply identify the modified range across all existing sheets - customize to allow for hidden sheets as req.).

You can also override ranges in col D as desired e.g.

• remove final row
• select desired columns
• retrieve all / select used ranges etc.etc.

You are know able to reference unbroken/robust cell ranges given the entire series of file paths, used ranges constituents of every respective sheet as req. (no broken links and the link due to inserting a column, rows, or even relabelling / creating new or deleting sheet(s) within resp. workbooks).

One way to retrieve, for instance, would be to set up a formula in column A as follows:

=IF(B2="","",LET(x_,LEN(B2)-MATCH("\",MID(B2,LEN(B2)-ROW($B$1:OFFSET($B$1,LEN(B2)-1,0,1,1)),1),0),"'"&MID(B2,1,x_)&"["&MID(B2,x_+1,LEN(B2)-x_+4)&"]")&C2&"'!"&D2)

(assumes you have Office 365 compatible version Excel)

Then continue building the range reference to exernal sheets as follows:

="="&A2

Copy this and paste special values - which leaves you with an unevaluated external workbook range reference (select, press F2 and return carriage will result output from the external file in question (if the external workbook in Q was not recalculated when last closed, Excel will prob. prompt a dialogue box advising you - just press enter/OK and a #Spill! will return the values desired/specified.

PS - will just mention: the benefit of this approach is that if someone has unscrupulously included dozens of additional sheets, you'll be able to screen - and keep your main workbook light-weight/only containing desired info