This should do the job:
Sub test()
MsgBox GetValueFromWB("C:\", "WB_Value_Test.xls", "Sheet1", "B2")
End Sub
Function GetValueFromWB(strPath As String, _
strFile As String, _
strSheet As String, _
strRef As String) As Variant
'Retrieves a value from a closed workbook
'----------------------------------------
Dim strArg As String
'make sure we have the trailing backslash
'----------------------------------------
If Right$(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
'Make sure the file exists
'-------------------------
If bFileExists(strPath & strFile) = False Then
GetValueFromWB = "File Not Found"
Exit Function
End If
'Create the argument
'-------------------
strArg = "'" & strPath & "[" & strFile & "]" & strSheet & "'!" & _
Range(strRef).Range("A1").Address(, , xlR1C1)
'Execute an XLM macro
'--------------------
GetValueFromWB = ExecuteExcel4Macro(strArg)
End Function
Function bFileExists(ByVal sFile As String) As Boolean
Dim lAttr As Long
On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0
End Function
RBS
Bob Zimski said:
All the threads on this subject are about copying data from a closed
workbook
to an active workbook. What I would like to do is just pickup the value of
a
specific cell in a specific sheet in a closed workbook and use that value
in
a variable for processing purposes. What can I use to do this?
Thanks
Bob