ExecuteExcel4Macro Error

[anon]

Hi all,

For the first time today I have come across the GetValue function. I
am looking to get values from a (very large) workbook and would prefer
to do it without opening the other wb as it takes up to 3 mins to
open. I also cannot use any method that requires setting a reference
as my workbook is distributed to users running many different versions
of Excel - therefore GetValue seemed a simple and suitable solution.

My code is below;

sub getthevalues
'other code defining pathrr and mypath
p = pathrr
f = mypath
s = "SVRed.xls"
a = "B11"
MsgBox GetValue(p, f, s, a)
end sub


Function GetValue(Path, File, Sheet, Ref)
'Retrieves a value from a closed workbook
Dim Arg As String
'Make sure the file exists
If Right(p, 1) <> "\" Then Path = p & "\"
If Dir(Path & f) = "" Then
GetValue = "File not Found"
Exit Function
End If
'Create the argument
Arg = "'" & p & "[" & f & "]" & s & "'!" &
Range(Ref).Range(a).Address(, , xlR1C1)
'Execute XLM macro
MsgBox (Arg)
On Error Resume Next
GetValue = ExecuteExcel4Macro(Arg)
End Function


This errors on;
GetValue = ExecuteExcel4Macro(Arg)

I have checked and re-checked the Arg string and this is definately
correct. I can't seem to find much information about what
ExecuteExcel4Macro is or does and therefore am stumped! I would
appreciate any help or simply explanations.

Thanks

 

[ytayta555]

My code is below;

Function GetValue(Path, File, Sheet, Ref)
     'Retrieves a value from a closed workbook
    Dim Arg As String
    'Make sure the file exists
    If Right(p, 1) <> "\" Then Path = p & "\"

.......................... ....... .....

    MsgBox (Arg)
    On Error Resume Next
GetValue = ExecuteExcel4Macro(Arg)
End Function

This errors on;
GetValue = ExecuteExcel4Macro(Arg)

Just an idea , maybe you need an If ...Then before
this line of code : GetValue = ExecuteExcel4Macro(Arg)

 

[ytayta555]

You wrote so :

     'Execute XLM macro

    MsgBox (Arg)
    On Error Resume Next
GetValue = ExecuteExcel4Macro(Arg)
End Function

Ask :
,,ExecuteExcel4Macro(Arg),, is a name of a macro ?

Do you want to Call (Run) a macro from this function ?