如何从封闭工作簿中的特定单元格中读取信息，然后使用Microsoft Excel中的VBA将其粘贴到活动工作表中的单元格中? [英] 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?
问题描述
我想知道是否有人知道如何使用VBA从封闭的工作簿中引用一个单元格.
I was wondering if anyone knew how one can reference a cell from a closed workbook using VBA.
我知道如何使用ADO和SQL来引用一系列单元格，但是我不知道如何为特定单元格创建SQL查询.
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.
在浏览Internet时，我遇到了一些使用"ExecuteExcel4Macro"的代码，但是我找不到该功能/命令的任何真实文档.实际上，MSDN网站上有关此命令的文档是垃圾且模糊不清，坦率地说完全没有帮助.
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.
无论如何，我离题了；理想情况下，我想从外部工作簿中调用一个单元，而不必打开该工作簿.我尝试使用的代码如下:
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
当我运行宏时，它返回的唯一内容是#REF
When i run the macro the only thing it returns is #REF
我也尝试过:
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
当代码到达MsgBox时，出现类型不匹配错误.如果我摆脱了msgbox命令，并尝试使用
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
ThisWorkbook.Activate
Sheets("Overview").Select
With Selection
ActiveSheet.Range("C5").Clear
ActiveSheet.Range("C5").Select
ActiveCell = ExecuteExcel4Macro(Ret)
我仍然得到#REF！错误
I still get the #REF! error
谁能告诉我:1)这是要使用的最佳技术吗?2)我的代码有什么问题?和，3)是否有更好的方法使用ADO或DOA或我不知道的技术从外部工作簿中引用单个单元格.
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.
也没有人知道有关如何使用ExecuteExcel4Macro函数的大量文档.
Also does anyone know of any extensive documentation on how to use the ExecuteExcel4Macro function.
请帮助；谢谢
仅供参考，我使用的是Excel 2003
FYI I am on excel 2003
推荐答案
您可以尝试以下方法:
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
这篇关于如何从封闭工作簿中的特定单元格中读取信息，然后使用Microsoft Excel中的VBA将其粘贴到活动工作表中的单元格中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
