使用 VBA 从单元格中的名称引用工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20025202/
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 a worksheet from its name in a cell using VBA
提问by user3000531
I am trying to find out how to make a reference that is defined by a cell value using VBA.
我试图找出如何使用 VBA 进行由单元格值定义的引用。
I have a form that inputs the name and location of a workbook into a cell (eg A1). I then want my VBA code to take the value of this cell as a reference to the location of that file.
我有一个表单，可以将工作簿的名称和位置输入到单元格（例如 A1）中。然后我希望我的 VBA 代码将此单元格的值作为对该文件位置的引用。
Instead of the reference being "f:test/file.xls" I would like it to be 'f:test/filedata.xls sheet1 A1' (clearly not a filepath but hopefully you get the idea).
而不是参考是“f:test/file.xls”，我希望它是“f:test/filedata.xls sheet1 A1”（显然不是文件路径，但希望你能明白）。
This will allow me to build a set of interconnected workbooks that can be stored in different places and have different names without hard coding the locations every time (userform to input locations into the cells is used).
这将允许我构建一组互连的工作簿，这些工作簿可以存储在不同的位置并具有不同的名称，而无需每次对位置进行硬编码（使用用户表单将位置输入到单元格中）。
i am not sure if this is possible, and I certainly can't see anything like it yet. Here's hoping!
我不确定这是否可能，而且我当然还看不到类似的东西。这里是希望！
Cheers
干杯
回答by Derrik
I'm not entirely clear on what you are seeking to achieve, so I am not going to attempt to answer your question explicitly. Instead, hopefully this will get you going in the right direction.
我并不完全清楚您要实现的目标，因此我不会尝试明确回答您的问题。相反，希望这会让你朝着正确的方向前进。
Below is a snippet of code that I have that references a cell on a spreadsheet and uses that cell as the file path to be combined with an adjacent cell that contains a file name. They are then merged together and used to open that workbook.
下面是一段代码，它引用了电子表格上的一个单元格，并将该单元格用作要与包含文件名的相邻单元格组合的文件路径。然后将它们合并在一起并用于打开该工作簿。
I had to chop out a bunch of code from what I began with to make this example a lot more clear so I have not tested this as is. However, I hope it serves a good launching point for what you are trying to achieve.
我不得不从一开始就删掉一堆代码，以使这个例子更加清晰，所以我没有按原样进行测试。但是，我希望它可以为您尝试实现的目标提供一个很好的起点。
Private Sub OpenWBs(iCell)
Dim FilePath As String
Dim iCell As String
'refers to file path in cell A1
FilePath = ActiveSheet.Range("A1").Value
'refers to name of the workbook in cell A2
iCell = ActiveSheet.Range("A2").Value
If Not IsEmpty(iCell) Then 'checks for empty value, if found exits, if not opens workbook
Workbooks.Open Filename:=FilePath & iCell & ".xls", ReadOnly:=True
Else
Exit Sub
End If
End Sub
Good luck!
祝你好运！
回答by dee
Let's say in active sheet in cell A1 you will have string like this:
假设在单元格 A1 的活动表中，您将有这样的字符串：
C:\Temp\VBA\source.xls!Sheet1!A1
Then reference to workbook 'source.xls' and then range 'Sheet1!A1' can be done e.g. like this:
然后可以参考工作簿“source.xls”然后范围“Sheet1!A1”，例如：
Dim fullRef As String
fullRef = ActiveSheet.Range("a1")
' fullRef = "C:\Temp\VBA\source.xls!Sheet1!A1"
Dim path As String
path = Left(fullRef, InStr(fullRef, "!") - 1)
' path = "C:\Temp\VBA\source.xls"
Dim wb As Workbook
Set wb = Workbooks.Open(path)
' now workbook 'source.xls' should be opened
Dim rangeAddress As String
rangeAddress = Right(fullRef, Len(fullRef) - InStr(fullRef, "!"))
' rangeAddress = "Sheet1!A1"
Dim rn As Range
Set rn = Range(rangeAddress)
' now rn is referencing cell 'A1' on sheet 'Sheet1' in workbook 'source.xls'
