ExecuteExcel4Macro
 

Hi All,

Using Excel 2003.


Code:


Option Explicit
Const ALT_PLANNING_PATH As String = "D:\Data\Watch\"
Const PLANNING_FILE As String = "ROC 2009.xls"

Sub test()
Dim result As Variant
result = GetValue(ALT_PLANNING_PATH, PLANNING_FILE, "Engineers", "A1")
Debug.Print result
End Sub
'''GetValue(ALT_PLANNING_PATH,PLANNING_FILE,"Engin eers", A1)

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''
''' Function: GetValue
'''
''' Comments: Looks up the value in the planning file
'''
'''
'''
''' Date Developer Action
''' -------------------------------------------------------------------------
''' dd/mm/yyyy John Walkenbach Created
'''
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)
If GetValue = 0 Then GetValue = ""
End Function

As I mentioned in the function comments, I have found the code on John's
page, honour to whom it belongs to.

Is there a way I could use this to get a range of cells instead of one
single cell?

With kind regards,

JP