Reading cell value from a closed Excel file error

[Pablo Espressobar]

Hello Everyone,

I have a workbook where i want to collect numeric data from another excel files (from only 1 cell) without opening them manually.
I found a neat macro for this, but something is still not okay and i would like to ask for some help.

In the main excel i have a unique string in "C2" i.e. "S2NKL561102" which is the start of my file's name, but after that there is always a space character and an unknown string, that's why i wanted to use a Wildcard.
The template of the searched excel file is always the same, i always want to get the value from cell "E1" from Sheet1.

Using the code above doesn't give a pop-up error, but the value in "F2" cell is always #REF . (it should be a number like 20)

I think it has to do something with the Wildcard in my filename, but i don't know what's the problem with it.

Here is the code:

Sub TestGetValue()
p = "C:\Test\Budget"
f = Range("C2").Value & " *.xlsx"
s = "Sheet1"
a = "E1"

Range("F2").Value = GetValue(p, f, s, a)

End Sub


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

Thank you for your help

 

[ZVI]

Tu support wildcard try this modification of the GetValue function:

Private Function GetValue(path, ByVal 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 & "\"
  file = Dir(path & file)
  If 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

 

[Pablo Espressobar]

Thank you so much, it works!!!

 

[ZVI]

Happy to help, Pablo!