excel - 远程获取给定 excel 电子表格的所有选项卡名称

Question

我正在尝试构建一个电子表格工具，该工具可以快速从其他电子表格中提取选定的数据，从而允许我检查大量电子表格中的错误值或错误，每个电子表格都有 40 多个标签。我已经成功地使用“单元格内”Excel 函数对我的电子表格进行了专门的编程以

1 识别目标电子表格(这应该是唯一的用户输入)

2 从多个选项卡中的目标电子表格中的一系列选定单元格中提取值。

问题是，虽然 =indirect 和 =concatenate 允许我构建一个正常运行的系统，但如果有人修改模板的单个行或列，该工具就会中断，或者至少会从错误的单元格中提取数据。

所以现在我正在寻找如何适应这种情况。我找到了使用索引、匹配和偏移移动单元格的解决方案，我只需要每个人都不要从相邻单元格中删除搜索词标签，这感觉很合理。只要工作表上只有一个单元格显示“总计”，我就可以得到它右边的值，这就足够了。

但现在我遇到了“如果他们重命名选项卡怎么办？”的问题。是的。是的，他们确实。很多。

因此，为了将我的索引和匹配定位在正确的位置，我需要将选项卡名称连接到间接索引中，并且为了获得那些不需要我检查的每个电子表格 20 分钟的人工劳动，我需要自动生成一个列表我定位的电子表格上的选项卡名称。我需要在不向目标电子表格添加宏的情况下生成此列表，将其保留在我的工具中。

我意识到这可能超出了 excel 的能力，我还没有准备好开始编写 80 行的宏。但我希望有某种查询或一系列嵌套函数可以远程列出 excel 选项卡的名称，并且有人知道。

Answer 1

以下截图/代码引用:

---

VB代码:

改编自 Microsoft office和(主要)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

---

力学

包含 2 个模块:

1. Button1_Click() :附加到命令按钮(或可以直接从 VBA 项目资源管理器运行)- 此模块允许您浏览感兴趣的文件(可以进行多项选择)。然后它调用下一个模块/程序 (2)，并为您选择的每个文件重复此步骤

2. ListSheetsRangeInfo() :从您在模块 1 中选择的 excel 文件中检索相关信息(使用的范围、工作表名称等)- 返回的信息将不受用户所做的任何修改的影响(它只会识别所有现有的修改范围工作表 - 自定义以允许根据需要隐藏工作表。)。

您还可以根据需要覆盖 D 列中的范围，例如

• 删除最后一行
• 选择所需的列
• 检索所有/选择使用的范围等等

您知道能够在给定整个文件路径系列的情况下引用完整/可靠的单元格范围，每个工作表的使用范围成分作为要求。 (没有断开的链接和由于插入列、行，甚至重新标记/创建新的或删除工作簿中的工作表而导致的链接)。

例如，一种检索方法是在 A 列中设置一个公式，如下所示:

=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)

(假设您有 Office 365 兼容版 Excel)

然后继续构建对外部工作表的范围引用，如下所示:

="="&A2

复制此并粘贴特殊值 - 这会留下未评估的外部工作簿范围引用(选择，按 F2 并返回回车将导致相关外部文件的输出(如果 Q 中的外部工作簿在上次关闭时未重新计算, Excel 可能会提示一个对话框来为您提供建议 - 只需按回车键/确定，#Spill! 将返回所需/指定的值。

---

PS - 只会提到:这种方法的好处是，如果有人肆无忌惮地添加了数十张额外的工作表，您将能够进行筛选 - 并使您的主工作簿保持轻量级/仅包含所需信息

---