ExcelBanter - Access and retrieve data from network workbook

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Access and retrieve data from network workbook (https://www.excelbanter.com/excel-programming/307115-access-retrieve-data-network-workbook.html)

Max Potters

Access and retrieve data from network workbook

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

Frank Stone

Access and retrieve data from network workbook

Hi,
you are a little foggy on the "copy some cells" part but
you might be able to do want you want with get external
date. (i use it all the time)
on the xl menu click DataGet Exteranl DataNew Database
Query.
A data soure dialog box will come up. click excel files
A select Workbook dialog box will come up. in the lower
right box select the drive letter of your network. In the
upper right box select the directory the file is in. the
file names will be in the upper left box. May sure that
you have permissions for the drive and directory and file.
This will create a query that you can atatch to a button.
it will bring in collumns of data that you select to an
area(range) you specify. from there you can copy the cells
you need. You can refresh the query with a macro and
probable copy the cells you want.

-----Original Message-----
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

.

Max Potters

Access and retrieve data from network workbook

I thank you alot for this help! Though I want to use VB coding to do it. I
want to access another workbook at a network computer, and read information
from that workbook, to a local workbook. Also I would like to be able to
copy values from the network workbook to the local one.

I hope some1 knows how to program this

Max
"Frank Stone" wrote in message
...
Hi,
you are a little foggy on the "copy some cells" part but
you might be able to do want you want with get external
date. (i use it all the time)
on the xl menu click DataGet Exteranl DataNew Database
Query.
A data soure dialog box will come up. click excel files
A select Workbook dialog box will come up. in the lower
right box select the drive letter of your network. In the
upper right box select the directory the file is in. the
file names will be in the upper left box. May sure that
you have permissions for the drive and directory and file.
This will create a query that you can atatch to a button.
it will bring in collumns of data that you select to an
area(range) you specify. from there you can copy the cells
you need. You can refresh the query with a macro and
probable copy the cells you want.

-----Original Message-----
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[_3_]

Access and retrieve data from network workbook

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

Max Potters

Access and retrieve data from network workbook

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

Max Potters

Access and retrieve data from network workbook

Dave Peterson[_3_]

Access and retrieve data from network workbook

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

Dave Peterson[_3_]

Access and retrieve data from network workbook

Max Potters

Access and retrieve data from network workbook

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

Dave Peterson[_3_]

Access and retrieve data from network workbook

Google locked up while I was posting. (Darn duplicate!)

Dave Peterson wrote:

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

--

Dave Peterson

All times are GMT +1. The time now is 05:24 AM.