vba - 复制带有表格引用的 Excel 工作表

Question

我有一个包含多个工作表的 Excel 2007 文档，我们将其命名为 source.xls。我想将一些工作表复制到文件夹中的所有 Excel 文档中，例如 C:\some_folder。

我想出了如何循环目录:

Dim file As String
file = dir("C:\some_folder\*.xlsx")
Do While file <> ""

   Rem do_stuff

   file = dir()
Loop

以及如何在工作簿之间复制工作表:

For Each ws in ActiveWorkbook.Worksheets
    Dim wb as Workbook
    Set wb = Workbook.Open(file)
    ws.Copy , wb.sheets(w.sheets.Count)
    wb.Close SaveChanges:=True
Next ws

到目前为止一切顺利。

现在其中一个工作表包含一个表，其中包含来自 SQL Server 的外部数据。复制它效果很好。

另一个工作表将该表中的数据引用为 Table_MYSERVER_MYDB[[row][col]]。当我复制它时，引用会自动变成 source.xls!Table_MYSERVER_MYDB[[row][col]]

更新:我只是尝试按工作表和单元格引用表中的数据，例如=Other_Sheet!A1。还是同样的问题，引用神奇地变成了 =[source.xls]Other_Sheet!A1。

更新2:下一次尝试是使用 =INDIRECT("Other_Sheet!"&CELL("address")) 访问另一个工作表中的单元格，但这似乎会触发 Excel 2007 中的错误。所有单元格都会显示相同的值。亲自尝试一下:)

我希望目标文档中的工作表引用同一工作簿中的表格。我该怎么做？

我也愿意接受 VBA 之外的其他解决方案

Answer 1

我自己想出来了:

我最后一次绝望的尝试是对所有公式使用搜索和替换来删除 [source.xls]。

这时一位同事建议使用:

wb.ChangeLink Name:=source.xls NewName:=wb.Name Type:=xlExcelLinks

正是我正在寻找的东西!