如何从关闭的工作簿中的特定单元格读取信息，然后使用 Microsoft Excel 中的 VBA 将其粘贴到活动工作表中的单元格中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15978391/
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 can I read information from a specific cell in a closed workbook then paste it in a cell in my active worksheet using VBA in Microsoft Excel?
提问by alfandango
I was wondering if anyone knew how one can reference a cell from a closed workbook using VBA.
我想知道是否有人知道如何使用 VBA 从封闭的工作簿中引用单元格。
I know how to reference a range of cells using ADO and SQL but I don't know how to create a SQL query for a specific cell.
我知道如何使用 ADO 和 SQL 引用一系列单元格，但我不知道如何为特定单元格创建 SQL 查询。
While browsing the internet i came across some code that uses "ExecuteExcel4Macro" but I am unable to find any real documentation for this function/command. In fact the documentation on the MSDN website regarding this command is rubbish and vague and quit frankly not helpful at all.
在浏览互联网时，我遇到了一些使用“ExecuteExcel4Macro”的代码，但我无法找到有关此功能/命令的任何真实文档。事实上，MSDN 网站上关于此命令的文档是垃圾和模糊的，坦率地说，退出根本没有帮助。
Anyway I digress; Ideally, I would like to call on a cell from an external workbook without having to open said workbook. The code I am trying to get to work is as follows:
无论如何我离题了；理想情况下，我想从外部工作簿调用单元格，而无需打开所述工作簿。我试图开始工作的代码如下：
Sub update_overview()
Dim wbPath As String
Dim wbName As String
Dim wsName As String
Dim cellRef As String
Dim data As Variant
wbPath = "C:\examplepath\"
wbName = "Core (N)i.xls"
wsName = "Sheet1"
cellRef = "C5"
'data = GetData(wbPath, wbName, wsName, cellRef)
ThisWorkbook.Activate
Sheets("Overview").Select
With Selection
ActiveSheet.Range("C5").Clear
ActiveSheet.Range("C5").Select
ActiveCell = GetData(wbPath, wbName, wsName, cellRef)
End With
End Sub
Private Function GetData(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetData = ""
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
GetData = ExecuteExcel4Macro(arg)
End Function
When i run the macro the only thing it returns is #REF
当我运行宏时，它返回的唯一内容是#REF
I have also tried:
我也试过：
Sub Sample()
Dim wbPath As String, wbName As String
Dim wsName As String, cellRef As String
Dim Ret As String
'wbPath = "C:\Documents and Settings\Siddharth Rout\Desktop\"
wbPath = "C:\Users\my.name\Desktop\"
wbName = "QOS DGL stuff.xls"
wsName = "ACL"
cellRef = "C3"
Ret = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, -4150)
MsgBox ExecuteExcel4Macro(Ret)
End Sub
When the code reaches MsgBox I get a type missmatch error. If i get rid of the msgbox command and try to continue to paste to the cell with
当代码到达 MsgBox 时，我收到类型不匹配错误。如果我摆脱了 msgbox 命令并尝试继续粘贴到单元格
ThisWorkbook.Activate
Sheets("Overview").Select
With Selection
ActiveSheet.Range("C5").Clear
ActiveSheet.Range("C5").Select
ActiveCell = ExecuteExcel4Macro(Ret)
I still get the #REF! error
我仍然得到#REF！错误
Can anyone tell me: 1) is this the best technique to be using? 2) What is wrong with my code? and, 3) Is there a better way to reference a single cell from an external workbook using ADO or DOA or a technique i am unaware of.
谁能告诉我：1）这是最好的技术吗？2）我的代码有什么问题？并且，3) 有没有更好的方法可以使用 ADO 或 DOA 或我不知道的技术从外部工作簿中引用单个单元格。
Also does anyone know of any extensive documentation on how to use the ExecuteExcel4Macro function.
还有谁知道有关如何使用 ExecuteExcel4Macro 函数的任何广泛文档。
Please help; Thanks
请帮忙; 谢谢
FYI I am on excel 2003
仅供参考，我在 excel 2003
回答by David Zemens
You could try something like this:
你可以尝试这样的事情：
arg = "='" & wbPath & "[" & wbName & "]" & wsName & "'!" & cellRef
arg = "='" & wbPath & "[" & wbName & "]" & wsName & "'!" & cellRef
Sub Test()
Dim wbName As String
Dim wbPath As String
Dim wsName As String
Dim cellRef As String
Dim calcState As Long
calcState = Application.Calculation
wbPath = "C:\users\david_zemens\"
wbName = "a report.xlsx"
wsName = "Sheet1"
cellRef = Range("B2").Address
Dim arg As String
arg = "='" & wbPath & "[" & wbName & "]" & wsName & "'!" & cellRef 'Range(cellRef).Address(True, True, xlR1C1)
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
ActiveCell.Value = arg
ActiveCell.Value = ActiveCell.Value 'essentially a paste/values over the formula.
Application.DisplayAlerts = True
Application.Calculation = calcState
End Sub
