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
"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