GetInfoFromClosedFile in xla Error

[mwb]

I have a problem with the code below, it works fine when run from an xls file but when i make it an addin (xla) I am getting an error:

"Run-time error '1004':
Method 'Range' of object '_Global' failed"

Any suggestions why this happens?

Cheers, mike

sub test()

dim mytext as string

mytext=(GetInfoFromClosedFile("C:\", "test.xls", "Page 1", "A1"))

end sub


Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
If Dir(wbPath & "" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

 

[mwb]

I solved this by not using the function; instead I used:

Dim mytext As String
mytext = "'C:\[test.xls]Page 1'!R1C1"