Remotely getting all the tab names of a given excel spreadsheet

Question

I am trying to build a Spreadsheet tool that can quickly pull selected data from other spreadsheets, allowing me to check for bad values or errors on a multitude of spreadsheets each of which will have 40+ tabs. I have been successful in programming my spreadsheet exclusively with "in cell" Excel functions to

1 Identify the target spreadsheet (this should be the only user input)

2 pull the values from a series of selected cells on the target spreadsheet across multiple tabs.

The problem is that while =indirect and =concatenate have allowed me to build a functioning system, if anyone modifies the template by a single row or column, the tool breaks, or at least pulls data from the wrong cell.

So now I'm looking for how to adjust to this. I've found solutions to moving cells using index, match, and offset, I just need everyone to not delete the search term label from the adjacent cell, and that feels reasonable. As long as one and only one cell on the sheet says "Total", I can get the value to the right of that, and that's more than enough.

But now I have the problem "what if they renamed the tabs?" And yes. Yes they did. A lot.

So in order to aim my index and match at the right spot, I need the tab names to concatenate into the indirects, and to get those without twenty minutes of manual labor per spreadsheet I check I need to automatically generate a list of the tab names on the spreadsheet I'm targeting. I need to generate this list without adding macros to the target spreadsheet, keeping it exclusively on my tool.

I realize this may simply be beyond the capacity of excel, and I'm not ready to start coding 80 line macros. But I'm hoping that there is some sort of query or series of nested functions that will remotely list the names of an excel's tabs, and that someone out there knows it.

Answer 1

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

---