Excel引用其它工作簿中的单元格的值及使用VBA操作

一个工作薄的工作表的单元格可以使用公式直接引用另一个工作簿的工作表的单元格的值，自然，被引用的工作表的单元格的值更新后，打开引用了此工作表的工作簿后，其值也会自动更新。

如以下路径“F:\工作簿间数据引用”，有两个文件夹：

现打开3个工作簿：

其中“F:\工作簿间数据引用\a\a.xlsx”的Sheet1的内容如下：

	a	b	c
m1	1	2	3
m2	4	5	6
m3	77	8	9

其中“F:\工作簿间数据引用\b\b.xlsx”的Sheet1的内容如下：

	a	b	c
m1	11	12	13
m2	14	15	16
m3	17	18	19

其中“F:\工作簿间数据引用\c.xlsx”的Sheet1使用公式引用其它工作簿中单元格的数据：

n	sum
a	=SUM([a.xlsx]Sheet1!$B$2:$B$4)
b	=SUM([b.xlsx]Sheet1!$C$2:$C$4)

注意其语法规则。

此时关闭工作簿a、b，工作簿C的工作表Sheet1的公式的引用会将公式引用工作簿的绝对路径显示出来：

n	sum
a	=SUM('F:\工作簿间数据引用\a\[a.xlsx]Sheet1'!$B$2:$B$4)
b	=SUM('F:\工作簿间数据引用\b\[b.xlsx]Sheet1'!$C$2:$C$4)

试图将上面的绝对路径改为相对路径：

n	sum
a	=SUM('..\a\[a.xlsx]Sheet1'!$B$2:$B$4)
b	=SUM('[b.xlsx]Sheet1'!$C$2:$C$4)

结果如下：

n	sum
a	=SUM('C:\Users\ww\a\[a.xlsx]Sheet1'!$B$2:$B$4)
b	=SUM('F:\工作簿间数据引用\b\[b.xlsx]Sheet1'!$C$2:$C$4)

可以知道，工作簿之间的数据引用，只能使用绝对路径。

除非工作盘下的文件夹整体移动到其它工作盘，否则文件夹或文件的移动，打开工作簿C.xlsx时，会提示以下错误：

原因是绝对路径错误（工作盘盘符可以自动改变）。

同样的，引用未打开的工作簿的数据，我们也可以使用VBA代码。

从上面可知，对于引用其它打开的工作簿，公式引用时切换工作簿即可。在VBA代码中，可以用VBA代码打开其它工作簿，并引用对象，所以我们引用未打开的工作簿，可以将指定的工作簿以只读方式打开，获取数据后又将其关闭。将屏幕更新属性值设置为False，用户看不到打开工作簿的过程。也可以不打开工作簿，使用公式，将路径按既定的语法规则写清楚或定义一个函数。实现这种方法的VBA 代码如下：

Sub 获取其他工作簿数据() Dim wb As Workbook '以只读方式打开工作簿 Set wb = Workbooks.Open('F:\工作簿间数据引用\a\a.xlsx', True, True) With ThisWorkbook.Worksheets('Sheet1') '从工作簿中读取数据 ' 方式1，从打开的工作簿引用 .Range('B2') = wb.Worksheets('Sheet1').Range('B2') + _ wb.Worksheets('Sheet1').Range('B3') + _ wb.Worksheets('Sheet1').Range('B4') ' 方式2，使用公式和绝对路径 .Range('B3').Formula = '=SUM('F:\工作簿间数据引用\b\[b.xlsx]Sheet1'!$C$2:$C$4)' ' 方式3，将方式2的使用定义为一个函数 .Range('B4').Formula = GetClosedData('F:\工作簿间数据引用\b', 'b.xlsx', 'Sheet1', 'D2:D4') End With wb.Close False '关闭打开的工作簿且不保存任何变化 Set wb = Nothing '释放内存End SubFunction GetClosedData(ByVal path As String, ByVal WorkbookName As String, _ ByVal SheetName As String, ByVal RangeName As String) '参数Path 为工作簿路径 '参数WorkbookName 为工作簿名称 '参数SheetName 为工作表名称 '参数RangeName 为单元格区域 Dim r r = '=sum('' & path & '\[' & WorkbookName & ']' r = r & SheetName & ''!' & RangeName & ')' GetClosedData = rEnd Function

ref

吴永佩 成丽君 《征服Excel VBA：让你工作效率倍增的239 个实用技巧》

－End－