从 Excel 中的 vba 中的函数中访问不同工作簿中的工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8978916/
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
accessing sheets in a different workbook from within a function in vba in Excel
提问by user387184
I have two workbooks in Excel 2007, A and B.
我在 Excel 2007 中有两个工作簿，A 和 B。
Within A I have a function that needs to get certain values from workbook B. I find no way to do this within a function (within a normal sub is no problem, but I need a function - which actually is more complicated than just getting the cell(1,1) )
在 AI 中，有一个函数需要从工作簿 B 中获取某些值。我找不到在函数中执行此操作的方法（在普通子程序中没问题，但我需要一个函数 - 这实际上比获取单元格更复杂(1,1) )
in A:
在一个：
function getDataFromB(sheetName,row,col)
x = Application.run ("E:\B.xlsm!getData",sheetName,row,col)
getDataFromB = x
end getDataFromB
In B
在乙
function getData(sheetName,row,col)
x=sheets(sheetName).cells(row,col)
getData = x
end getData
Whenever getData within B is called it looks for sheetName in workbook A - not B. And writing
每当调用 B 中的 getData 时，它会在工作簿 A 中查找 sheetName - 而不是 B。并写入
x = workbooks("E:\B.xlsm").sheets(sheetName).cells(row,col)
does not work
不起作用
How would I solve this?
我将如何解决这个问题？
回答by Siddharth Rout
TIRED AND TESTED
疲惫和考验
Change
改变
function getDataFromB(sheetName,row,col)
x = Application.run("E:\B.xlsm!getData",sheetName,row,col)
getDataFromB = x end getDataFromB
function getDataFromB(sheetName,row,col)
x = Application.run("E:\B.xlsm!getData",sheetName,row,col)
getDataFromB = x end getDataFromB
to
到
Function getDataFromB(sheetName, row, col)
Dim FilePath As String, FileName As String
Dim wbTarget As Workbook
Dim x As Variant
Dim CloseIt As Boolean
'~~> Set file name and path here.
FileName = "B.xlsm"
FilePath = "E:\"
On Error Resume Next
Set wbTarget = Workbooks(FileName)
If Err.Number <> 0 Then
'~~> Open the workbook
Err.Clear
Set wbTarget = Workbooks.Open(FilePath & "\" & FileName)
CloseIt = True
End If
'~~> Check and make sure workbook was opened
If Err.Number = 1004 Then
MsgBox "File does not exist!"
Exit Function
End If
On Error GoTo 0
x = Application.Run(wbTarget.Name & "!getData", sheetName, row, col)
getDataFromB = x
If CloseIt = True Then
'~~> If the target workbook was opened by the macro, close it
wbTarget.Close savechanges:=False
Else
'~~> If the target workbook was already open, reactivate this workbook
ThisWorkbook.Activate
End If
End Function
Also in File B Change the code to
同样在文件 B 中将代码更改为
Function getData(sheetName,row,col)
x=sheets(sheetName).cells(row,col)
getData = x
End Function
You need to add "End Function" as I have done above.
您需要像我上面所做的那样添加“结束功能”。
