Excel #VALUE! error when using GetValue

Question

I am attempting to use the code

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

But receive #VALUE! when used as shown below:

Target ='C:\Temp[pulltest.xlsx]Sheet1'!$A$1

Parameters C:\Temp\ pulltest.xlsx Sheet1 A1

Function =getvalue(B3,B4,B5,B6)

I'm using Excel2010 in Windows 7.

Grateful for any help

score 0 · Answer 1 · answered Jun 16 '17 at 12:27

0

Your problem is that the GetValue function cannot be called from a worksheet cell.

So the following works:

Option Explicit
Sub GetVal()
    Dim path As String, file As String, sheet As String, ref As String

path = [a7]
file = [a8]
sheet = [a9]
ref = [a10]

[a11] = GetValue(path, file, sheet, ref)

End Sub

Private Function GetValue(path, file, sheet, ref)

     'Retrieves a value from a closed workbook
    Dim Arg
     'Make sure the file exists
   If Right(path, 1) <> "\" Then path = path & "\"
   If Dir(path & file) = "" Then
       GetValue = "File not  Found"
       Exit Function
    End If
     'Create the argument
    Arg = "'" & path & "[" & file & "]" & CStr(sheet) & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)

    GetValue = ExecuteExcel4Macro(Arg)
End Function

If the link is still active, you may refer to Excel Tips from John Walkenbach: A VBA Function To Get A Value From A Closed File which includes the caveat "Note: You cannot use this function in a worksheet formula."

answered Jun 16 '17 at 12:27

Ron Rosenfeld

53,870
7
28
60

I had intended to use this function to import 14 data items from around 35 workbooks each month. I guess it's of not much use in that situation? – Keith Jun 16 '17 at 14:51
Why not? Just run the macro instead of the function. You could just store the argument values in worksheet cells, and iterate through them; or use it in any number of ways. – Ron Rosenfeld Jun 16 '17 at 17:52
I have no idea what you mean, but thanks again for your contributions. – Keith Jun 17 '17 at 17:52
As you learn more about using VBA, what I wrote will become more clear. – Ron Rosenfeld Jun 18 '17 at 01:25
As you can tell, I'm not that familiar with VBA and moreover it's unlikely I'll ever use it again. I'd hoped to use it in this unusual (for me) situation. But as I can't invest the time in building on the code you have supplied, I will have to change the approach I was hoping to use. Regards. – Keith Jun 18 '17 at 09:01

Excel #VALUE! error when using GetValue

1 Answers1