vba 在不复制工作表的情况下从另一个工作簿引用 Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31697629/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reference an excel sheet from another workbook without copying the sheet
提问by Harley Walsh
Im wondering if it's possible to reference an excel sheet from another work book without making a copy of that sheet?
我想知道是否可以在不复制该工作表的情况下从另一本工作簿中引用 Excel 工作表？
The situation : I have some very large worksheets filled with various data, but i don't want to keep a copy of them open in my workbooks because while each workbook uses the same data source, they're slightly different.
情况：我有一些非常大的工作表，里面装满了各种数据，但我不想在我的工作簿中打开它们的副本，因为虽然每个工作簿使用相同的数据源，但它们略有不同。
I have a vba routine that takes this data and creates input files for other codes, vba expects this data to be available on the defined sheet names.
我有一个 vba 例程，它接受这些数据并为其他代码创建输入文件，vba 希望这些数据在定义的工作表名称上可用。
Is it possible to make either excel or vba to know that when i request worksheet("Example_1") it instead knows that i mean example_1 from a different workbook?
是否可以让 excel 或 vba 知道当我请求工作表（“Example_1”）时，它知道我的意思是来自不同工作簿的 example_1？
Thanks
谢谢
回答by mielk
Yes, it is possible.
对的，这是可能的。
You need to add those lines to your code:
您需要将这些行添加到您的代码中：
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Set wkb = Excel.Workbooks("name_of_workbook.xlsx")
Set wks = wkb.Worksheets("Example_1")
Now, every time you want to refer to a range from this other workbook, you need to add wks.before, i.e.:
现在，每次要引用其他工作簿中的范围时，都需要在wks.之前添加，即：
'Printing value in cell.
wks.Range("A1") = "x"
'Selecting range.
call wks.Range(wks.Cells(1,1), wks.Cells(2,2)).Select
回答by Mark Butler
=SUM('C:\test\[test.xlsx]sheet_name'!A1:A25)
is an example of a formula which references sheet sheet_namein workbook C:\test\text.xlsx.
是引用sheet_name工作簿中工作表的公式示例C:\test\text.xlsx。
Note that when the other workbook is opened, the formula automatically changes to
请注意，打开另一个工作簿时，公式会自动更改为
=SUM([test.xlsx]sheet_name!A1:A25)
and then when it is closed, the formula will change back.
然后当它关闭时，公式会变回来。
