如何在VBA中引用关闭工作簿的工作表索引而非工作表名称？

你遇到的问题很典型——Excel公式本身不支持直接用工作表索引来引用关闭工作簿的工作表，所以直接在公式里写Sheet (30)肯定不行。下面给你两个可行的解决方案，根据你的需求选择即可：

方法1：动态生成带工作表名称的公式（保留公式链接） #

这个方法的思路是先用VBA后台打开目标工作簿，获取指定索引对应的工作表名称，再把名称拼进公式里。这样既保留了公式的链接性，又能自动适配工作表名称的变化。

完整代码示例：

Sub PullDataWithSheetIndex()
    Dim sourceWorkbook As Workbook
    Dim targetSheetName As String
    Dim sourceFilePath As String
    
    ' 替换成你的源工作簿完整路径
    sourceFilePath = "C:\YourFolder\ASE Template White Book.xlsx"
    
    ' 后台打开源工作簿（不会显示在前台）
    Set sourceWorkbook = GetObject(sourceFilePath)
    
    ' 获取第30个工作表的名称（注意：Excel工作表索引从1开始计数）
    targetSheetName = sourceWorkbook.Sheets(30).Name
    
    ' 关闭源工作簿，不保存任何可能的改动
    sourceWorkbook.Close SaveChanges:=False
    
    ' 动态生成公式，替换原来的固定工作表名称
    Range("C1196:C1219").Formula = "='" & sourceFilePath & "]" & targetSheetName & "'!$C6"
    Range("D1196:D1219").Formula = "='" & sourceFilePath & "]" & targetSheetName & "'!$D6"
    Range("C1220:C1243").Formula = "='" & sourceFilePath & "]" & targetSheetName & "'!$C38"
    Range("D1220:D1243").Formula = "='" & sourceFilePath & "]" & targetSheetName & "'!$D38"
End Sub

注意事项： #

• 一定要填写完整的源工作簿路径，否则VBA可能找不到文件
• 确认工作表索引30是正确的（比如第一个工作表是索引1，第二个是2，以此类推）
• 如果源工作簿已经被你手动打开，GetObject会直接引用已打开的实例，关闭时不会影响你正在编辑的内容（但依然会执行Close SaveChanges:=False，所以不用担心误改源文件）

方法2：直接读取单元格值（无需公式链接） #

如果你不需要保留与源工作簿的公式链接，只是想一次性获取当前数据，这个方法更高效，也不会因为源工作簿移动或重名导致公式报错。

完整代码示例：

Sub PullValuesDirectly()
    Dim sourceWorkbook As Workbook
    Dim targetSheet As Worksheet
    Dim sourceFilePath As String
    
    sourceFilePath = "C:\YourFolder\ASE Template White Book.xlsx"
    
    Set sourceWorkbook = GetObject(sourceFilePath)
    Set targetSheet = sourceWorkbook.Sheets(30)
    
    ' 直接将源单元格的值复制到目标区域
    Range("C1196:C1219").Value = targetSheet.Range("C6").Value
    Range("D1196:D1219").Value = targetSheet.Range("D6").Value
    Range("C1220:C1243").Value = targetSheet.Range("C38").Value
    Range("D1220:D1243").Value = targetSheet.Range("D38").Value
    
    sourceWorkbook.Close SaveChanges:=False
End Sub

这个方法的优势： #

• 目标单元格里是纯值，不会因为源工作簿无法访问而显示#REF!错误
• 运行速度比生成公式更快，尤其是处理大量数据时

内容的提问来源于stack exchange，提问作者Richard Hannigan