Works fine for me like this.
Public Function GetValueFromClosedWorkbook(path, file, sheet, ref)
Dim arg As String
' Let's check whether the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValueFromClosedWorkbook = "File Not Found"
Exit Function
End If
' We create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Address(, , xlR1C1)
'MsgBox arg
' Now we execute an XLM macro
'All references must be given as R1C1 strings.
GetValueFromClosedWorkbook = ExecuteExcel4Macro(arg)
End Function
Sub test()
x = GetValueFromClosedWorkbook("D:\", "TwoDArray.xlsm", "Sheet1", "I12")
End Sub
PS: Please use Code Tags when posting code. It makes your code much easier to read and copy for testing, and it also maintains VBA formatting.
Bookmarks