Thank you Dave for your continuing support!
"Dave Peterson" wrote in message
om...
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