I think you're looking for something like:

Sub GetDataFromClosedFile()
filepath = "u:\my u documents\excel"
Filename = "book1.xls"
sheetname = "sheet1"
Strg = "'" & filepath & "\[" & Filename & "]" _
& sheetname & "'!r4c3"
ActiveSheet.Range("a1") = ExecuteExcel4Macro(Strg)
End Sub

Notice that I'm retrieving the value from C4 (r4c3 in R1C1 Reference style).

Then I'm dropping the value into A1 of the activesheet.

If you know what cell gets the value, you don't need code.

You can just put a formula that retrieves the value.

Open your "sending" workbook.
go to the "receiving" cell and type = (equal sign)
back to the cell you want to retrieve
point at it and hit enter.

You'll see a formula that looks like this:
=[book1.xls]Sheet1!$C$4

But when you close that other workbook, you'll see this:
='c:\my documents\excel\[BOOK1.xls]Sheet1'!$C$4

(If I understood you correctly.)


"Max Potters" wrote in message ...
Dave (and others),

I tried to use the GetValue code on the website
(http://j-walk.com/ss/excel/eee/eee009.txt), and added a few changes for my
personal use. here is the code:

Sub GetDataFromClosedFile()
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
'''
path = "C:\Documents and Settings\Max\Desktop\"
file = "test.xls"
sheet = "Sheet1"
range_ref = "A1"
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''
'''
Cell = "'" & path & "[" & file & "]" & sheet & "'!" & Range(range_ref).Value
MsgBox ExecuteExcel4Macro(Cell)
MsgBox "Cell = " & Cell
Sheet1.Range("A1").Value = Cell
End Sub

This code doesn't work. I commented the Msgbox line Execute...., and then
used another MsgBox "Cell = " & Cell. No errors here, but the messagebox
shows me just the path, file, sheet (and no cell value!!!!), and in
sheet1.range("A1").value I find the same.

THE PROBLEM:

How can I make sure that the cell value in my file (test.xls), comes in my
active workbook (where this code is)? The syntax of the line Cell = "'" &
path...etc.... is ok, but I think VB sees this as text, and I want to
extract the value of a cell!

Please help, thanks in advance

Max


"Dave Peterson" wrote in message
...
you could use a technique on John Walkenbach's site:

http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

Max Potters wrote:

Hi,

I'm having a problem with retrieving data from a workbook on another PC,
that is, a network PC. I want to copy some cell values from the network
PC,
into a local workbook. I know a simple way to do it, but this needs the
"network workbook" to be open.

I want to access the network workbook and retrieve and store data, but
without opening the workbook!

Please help me. If anything is not clear, please reply and I'll give
further
details.

Thanks in advance

Max Potters
The Netherlands

--

Dave Peterson