Try this code:

Function GetValueFromWB(path, file, sheet, ref)

'Retrieves a value from a closed workbook
'----------------------------------------
Dim strSep As String
Dim arg As String

strSep = "\"

'Make sure the file exists
'-------------------------
If Right$(path, 1) < strSep Then path = path & strSep
If bFileExists(path & file) = False Then
GetValueFromWB = "File Not Found"
Exit Function
End If

'Create the argument
'-------------------
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

'Execute an XLM macro
'--------------------
GetValueFromWB = ExecuteExcel4Macro(arg)

End Function

Function bFileExists(strFile As String) As Boolean
bFileExists = (Len(Dir(strFile)) 0)
End Function


RBS


"jm" wrote in message
...
I have two excel spreadsheets: test.xls and test2.xls

test.xls has information i want to automatically pull into test2.xls, and
I
would like keep test.xls closed during this process. test2.xls will be
open
when the command is given.

test.xls has the following characteristics
path: K:\Data Directories\
name: test.xls
sheet: mastedb Query
cells needed: A1:DV126

test2.xls has the follwing characteristics
path: K:\Data Directories\
name: test2.xls
sheet: test
can put data starting at cell: A1

It would be great, because I am an intern and under the gun to figure this
out, if someone could help me write the whole VBA command using the
information that I've placed above. I just can't see where to put
everything, and I keep getting errors. Thank you in advance.