从其他工作簿引用Excel工作表，而不复制工作表 [英] Reference an excel sheet from another workbook without copying the sheet

问题描述

我想知道是否可以在不复制该工作表副本的情况下引用另一本工作簿中的excel工作表吗?

Im wondering if it's possible to reference an excel sheet from another work book without making a copy of that sheet?

这种情况:我有一些非常大的工作表，其中填充了各种数据，但是我不想在工作簿中打开它们的副本，因为尽管每个工作簿都使用相同的数据源，但是它们却略有不同.

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.

我有一个vba例程，该例程可以获取这些数据并为其他代码创建输入文件，vba希望在定义的工作表名称上可以使用此数据.

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.

是否可以使excel或vba知道我请求工作表("Example_1")时，而是知道我的意思是来自其他工作簿的example_1?

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?

谢谢

推荐答案

是的，有可能.

您需要将这些行添加到您的代码中:

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

现在，每次您要引用该其他工作簿中的范围时，都需要在前面添加wks.，即:

Now, every time you want to refer to a range from this other workbook, you need to add wks. before, i.e.:

'Printing value in cell.
wks.Range("A1") = "x"

'Selecting range.
call wks.Range(wks.Cells(1,1), wks.Cells(2,2)).Select

这篇关于从其他工作簿引用Excel工作表，而不复制工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！