EDIT:由于以下内容不适用于封闭的工作簿,因此以下是如何使用 VBA 执行此操作的笨拙概念验证(我想有更好的方法可以做到这一点):
Sub ClosedWorkbook()
Dim currSht As Worksheet
Dim targetWbk As Workbook
Dim Path As String
' Turn off updating
Application.ScreenUpdating = False
' Set a reference to the current sheet
Set currSht = ActiveWorkbook.ActiveSheet
' Get the value in the formula cell (A1 here, could be ActiveCell if in a loop, etc.)
PathSheet = currSht.Range("A1").Value
' Split out the path - this is very clunky, more of a proof (?) of concept
' This is depends on the path being as you mentioned, and the IF block is to
' check if the apostrophe is present in the cell (since it is the escape character,
' it is usually skipped)
If Left(PathSheet, 1) = "'" Then
Path = Replace(Mid(PathSheet, 2, InStr(PathSheet, "]") - 2), "[", "")
Sheet = Mid(PathSheet, InStr(PathSheet, "]"))
Sheet = Left(Sheet, Len(Sheet) - 2)
Else
Path = Replace(Mid(PathSheet, 1, InStr(PathSheet, "]") - 1), "[", "")
Sheet = Mid(PathSheet, InStr(PathSheet, "]") + 1)
Sheet = Left(Sheet, Len(Sheet) - 2)
End If
' Open the target workbook
Set targetWbk = Workbooks.Open(Path)
' Grab the value from E4 and drop it in a cell (D1 here, could be ActiveCell, etc.)
MyValue = targetWbk.Sheets(Sheet).Range("E4").Value
currSht.Range("D5").Value = MyValue
' Close the target
targetWbk.Close
Application.ScreenUpdating = True
End Sub
OLD WAY(不适用于封闭的工作簿)
我相信这捕获了您正在寻找的内容。在这个例子中,细胞A1
我的工作表路径:
'C:\temp\[externalworkbook.xlsx]Sheet1'!
在单元格 B1 中我有公式:
=INDIRECT(A1 & "$E$4")
将工作表值与$E$4
为了生成完整路径,然后将其转换为值INDIRECT
。需要注意的一件事:撇号是转义字符,因此根据您的数据,您可能必须在公式中特别考虑它:
=INDIRECT("'" & A1 & "$E$4")
如果您使用的是 Excel 2007 或更高版本,您可以将其包装在IFERROR
消除猜测的公式:
=IFERROR(INDIRECT(A1 & "$E$4"),INDIRECT("'" & A1 & "$E$4"))