如何在 VBA 中引用另一个（打开或关闭）工作簿并将值拉回？- Excel 2007
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1801598/
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
How do I reference to another (open or closed) workbook, and pull values back, in VBA? - Excel 2007
提问by Rob A
Basically I need to gather a fair few figures from another workbook (Which is found and can be opened by a UserForm, therefore the location and names are variable). I need to use VBA for this as I also need to populate a chart with this data. I would prefer to not have to open the other workbook in order to do it, but if it is far easier then its OK.
基本上我需要从另一个工作簿中收集一些数字（可以找到并且可以由用户窗体打开，因此位置和名称是可变的）。我需要为此使用 VBA，因为我还需要用这些数据填充图表。我宁愿不必打开另一个工作簿来执行此操作，但如果它更容易，那么就可以了。
The UserForm is done and works fine, and I have the code I need to populate the chart, however I cant get VBA to pull back the data from the other workbook and assign it to the variables that I need it to.
用户窗体已完成并且工作正常，我有填充图表所需的代码，但是我无法让 VBA 从其他工作簿中提取数据并将其分配给我需要的变量。
Any ideas on how I can get VBA to do this? It would be greatly appreciated.
关于如何让 VBA 做到这一点的任何想法？这将不胜感激。
Thanks,
谢谢，
Rob.
抢。
回答by Adam Ralph
You will have to open the file in one way or another if you want to access the data within it. Obviously, one way is to open it in your Excel application instance, e.g.:-
如果要访问其中的数据，则必须以一种或另一种方式打开文件。显然，一种方法是在您的 Excel 应用程序实例中打开它，例如：-
(untested code)
（未经测试的代码）
Dim wbk As Workbook
Set wbk = Workbooks.Open("C:\myworkbook.xls")
' now you can manipulate the data in the workbook anyway you want, e.g. '
Dim x As Variant
x = wbk.Worksheets("Sheet1").Range("A6").Value
Call wbk.Worksheets("Sheet2").Range("A1:G100").Copy
Call ThisWorbook.Worksheets("Target").Range("A1").PasteSpecial(xlPasteValues)
Application.CutCopyMode = False
' etc '
Call wbk.Close(False)
Another way to do it would be to use the Excel ADODB provider to open a connection to the file and then use SQL to select data from the sheet you want, but since you are anyway working from within Excel I don't believe there is any reason to do this rather than just open the workbook. Note that there are optional parameters for the Workbooks.Open() method to open the workbook as read-only, etc.
另一种方法是使用 Excel ADODB 提供程序打开与文件的连接，然后使用 SQL 从您想要的工作表中选择数据，但由于您无论如何都在 Excel 中工作，我不相信有任何这样做的原因而不是仅仅打开工作簿。请注意， Workbooks.Open() 方法有可选参数以只读方式打开工作簿等。
