ExecuteExcel4Macro從已關閉的工作簿中獲取值我找到了這段代碼，并認為如果我只需要從一個封閉的表中提取一個值，那么使用它可能會很好。strInfoCell = "'" & strPath & "[" & strFile & "]Sheet1'!R3C3"myvalue = ExecuteExcel4Macro(strInfoCell)當我運行此代碼，我得到一個值strinfocell的'C:\Users\my.name\Desktop[QOS DGL stuff.xlsx]Sheet1'!R3C3但是當我運行代碼時會彈出一個對話框，顯示帶有“ QOS DGL suff”顯示的桌面文件。造成這種情況的原因是什么，不僅僅是按預期撤回數據？我知道路徑和文件名是正確的，因為如果我從調試輸出中復制它們并將它們粘貼到start>>run那么打開正確的工作表。我知道Sheet1（名為:) ACL，確實有價值cells(3,3)
3 回答
qq_遁去的一_1
TA貢獻1725條經驗 獲得超8個贊
類似的應用程序，但沒有上面示例中的硬編碼路徑。此函數從另一個已關閉的工作簿復制值，類似于= 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 ObjectPublic 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
MYYA
TA貢獻1868條經驗 獲得超4個贊
上面的代碼
strInfoCell = "'" & strPath & "[" & strFile & "]Sheet1'!R3C3"myvalue = ExecuteExcel4Macro(strInfoCell)
應該讀
strInfoCell = "'" & strPath & "[" & strFile & "]" & "Sheet1'!R3C3"myvalue = ExecuteExcel4Macro(strInfoCell)
它缺少“＆”
不需要功能
- 3 回答
- 0 關注
- 1349 瀏覽
相關問題推薦
添加回答
舉報
0/150
提交
取消