Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
retrieve data from different workbook | Excel Worksheet Functions | |||
RETRIEVE DATA FROM ANOTHER WORKBOOK BY CHECKING ON WORKBOOK DATE | Excel Worksheet Functions | |||
Retrieve text from filter data in workbook 1 and place values in 2 | Excel Discussion (Misc queries) | |||
user cannot access his share workbook after opening network path. | Excel Discussion (Misc queries) | |||
user cannot access his share workbook on network drive. | Excel Worksheet Functions |