vba ExecuteExcel4Macro 从关闭的工作簿中获取价值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9259862/
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
ExecuteExcel4Macro to get value from closed workbook
提问by DevilWAH
I found this bit of code and thought it might be good to use if I just need to pull one value from a closed sheet.
我发现了这段代码，并认为如果我只需要从封闭的工作表中提取一个值，它可能会很好用。
strInfoCell = "'" & strPath & "[" & strFile & "]Sheet1'!R3C3"
myvalue = ExecuteExcel4Macro(strInfoCell)
When I run this code I get a value for strinfocellof
当我运行此代码，我得到一个值strinfocell的
'C:\Users\my.name\Desktop[QOS DGL stuff.xlsx]Sheet1'!R3C3
'C:\Users\my.name\Desktop[QOS DGL stuff.xlsx]Sheet1'!R3C3
But when I run the code a dialogue pops up, showing desktop files with "QOS DGL suff" showing.
但是当我运行代码时会弹出一个对话框，显示带有“ QOS DGL suff”的桌面文件。
What's causing this, why is it not just pulling back the data as expected?
这是什么原因造成的，为什么不只是按预期撤回数据？
I know the path and file name are right, because if I copy them from the debug output and paste them in to start>>runthen the correct sheet opens.
我知道路径和文件名是正确的，因为如果我从调试输出中复制它们并将它们粘贴到start>>run然后正确的工作表打开。
I know that Sheet1(named: ACL), does have a value in cells(3,3)
我知道Sheet1(named: ACL), 确实有一个价值cells(3,3)
回答by Siddharth Rout
It depends on how you use it. The open file dialog box is being showed to you because the "strPath" doesn't have a "\" in the end ;)
这取决于你如何使用它。正在向您显示打开文件对话框，因为“strPath”最后没有“\”；)
Try this code. It works
试试这个代码。有用
Tried and Tested
久经考验
Option Explicit
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
回答by RexBarker
Similar application, but no hard coded paths as in the examples above. This function copies the value from another closed workbook, similar to the =INDIRECT() function, but not as sophisticated. This only returns the value...not a reference..so it cannot be used with further functions which require references (i.e.: VLOOKUP()). Paste this code into a new VBA module:
类似的应用程序，但没有上面例子中的硬编码路径。此函数从另一个关闭的工作簿复制值，类似于 =INDIRECT() 函数，但没有那么复杂。这只返回值......不是引用......所以它不能与需要引用的其他函数一起使用（即：VLOOKUP（））。将此代码粘贴到新的 VBA 模块中：
'Requires filename, sheetname as first argument and cell reference as second argument
'Usage: type in an excel cell -> =getvalue(A1,B1)
'Example of A1 -> C:\TEMP\[FILE1.XLS]SHEET1'
'Example of B1 -> B3
'This will fetch contents of cell (B3) located in (sheet1) of (c:\temp\file1.xls)
'Create a module and paste the code into the module (e.g. Module1, Module2)
Public xlapp As Object
Public Function getvalue(ByVal filename As String, ref As String) As Variant
' Retrieves a value from a closed workbook
Dim arg As String
Dim path As String
Dim file As String
filename = Trim(filename)
path = Mid(filename, 1, InStrRev(filename, "\"))
file = Mid(filename, InStr(1, filename, "[") + 1, InStr(1, filename, "]") - InStr(1, filename, "[") - 1)
If Dir(path & file) = "" Then
getvalue = "File Not Found"
Exit Function
End If
If xlapp Is Nothing Then
'Object must be created only once and not at each function call
Set xlapp = CreateObject("Excel.application")
End If
' Create the argument
arg = "'" & filename & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)
'Execute an XLM macro
getvalue = xlapp.ExecuteExcel4Macro(arg)
End Function
回答by Neil
Code above
上面的代码
strInfoCell = "'" & strPath & "[" & strFile & "]Sheet1'!R3C3"
myvalue = ExecuteExcel4Macro(strInfoCell)
Should read
应该读
strInfoCell = "'" & strPath & "[" & strFile & "]" & "Sheet1'!R3C3"
myvalue = ExecuteExcel4Macro(strInfoCell)
It is missing " & "
缺少“&”
No need for a function
不需要函数
Cheers Neil
尼尔干杯
回答by Gorodeckij Dimitrij
Data = "'" & GetDirectory & "[" & GetFileName & "]" & Sheet & "'!" & Range(Address).Range("A1").Address(, , xlR1C1)
Address = "$C"
GetDirectory = "C:\Users\my.name\Desktop\"
GetFileName = "QOS DGL stuff.xlsx"
Sheet = "ACL"
