If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Is there a way to referance data from or to a closed workbook from
Is there a way to referance data from or to a closed workbook from an open
one? I'm trying to referance data used in one workbook that is linked to others and would like to have some of the data automaticly transfer the same data into another workbook at the same time so as to cut down on having to enter the data more than once. Any help would be greatly appreciated. |
#2
|
|||
|
|||
Is there a way to referance data from or to a closed workbook from
Pulling data from closed workbook using a formula is pretty straight-forward.
Pushing data to another workbook (closed or open) is not so easy. I would use a macro and then my macro could open the "receiving" workbook. Ken C wrote: Is there a way to referance data from or to a closed workbook from an open one? I'm trying to referance data used in one workbook that is linked to others and would like to have some of the data automaticly transfer the same data into another workbook at the same time so as to cut down on having to enter the data more than once. Any help would be greatly appreciated. -- Dave Peterson |
#3
|
|||
|
|||
Is there a way to referance data from or to a closed workbook
Where would I find that type of macro?
"Dave Peterson" wrote: Pulling data from closed workbook using a formula is pretty straight-forward. Pushing data to another workbook (closed or open) is not so easy. I would use a macro and then my macro could open the "receiving" workbook. Ken C wrote: Is there a way to referance data from or to a closed workbook from an open one? I'm trying to referance data used in one workbook that is linked to others and would like to have some of the data automaticly transfer the same data into another workbook at the same time so as to cut down on having to enter the data more than once. Any help would be greatly appreciated. -- Dave Peterson |
#4
|
|||
|
|||
Is there a way to referance data from or to a closed workbook
I would imagine that the way you push the data to the other workbook would be
unique. It would have to be built for your application. Just to add... This type of thing is ripe for failure. If you run the macro that updates the receiving workbook and make a mistake, then you'll want to fix the error that was propogated to the other workbook. This could be a miserable task to accomplish--since there are so many ways to screw up. Personally, I do my best to keep all my data in a single sheet of a workbook. I'll be able to sort or filter to show the info I want. If that's not enough, then I'd try to pull the data into the other workbooks--maybe using =vlookup()'s and a unique key column to match data on. If I _had_ to do it (and I still wouldn't want to!), I'd have a dedicated macro -- not some event that does the work -- that I could run when I needed to send the data. Dim myCell as range dim ToFolderName as string dim ToWkbkName as string Dim ToWksName as string Dim DestCell as range dim ToWkbk as workbook dim ToWks as worksheet dim ToWkbkWasOpen as boolean set myCell = activesheet.range("A1") 'something tofoldername = "C:\my documents\excel" if right(tofoldername,1) "\" then tofoldername = tofoldername & "\" end if towkbkname = "Book1.xls" towksname = "Sheet1" 'check to see if it's open set towkbk = nothing on error resume next set towkbk = workbooks(towkbkname) on error goto 0 if towkbk is nothing then 'not open towkbkwasopen = false on error resume next set towkbk = workbooks.open(filename:=towkbkname) on error goto 0 if towkbk is nothing then 'still not open! msgbox "file not open and couldn't be opened!" exit sub end if else towkbkwasopen = true end if 'check to see if that sheet name exists set towks = nothing on error resume next set towks = towkbk.worksheets(towksname) on error goto 0 if towks is nothing then msgbox "No sheet in the to workbook by that name!" if towkbkwasopen = true then 'leave it open else 'close it without saving towkbk.close savechanges:=false end if exit sub end if with towks set destcell = .range("A1") 'for example 'or to get to the next open cell in column A (after the last used cell) set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0) end with destcell.value = mycell.value if towkbkwasopen then 'leave it open, maybe save it on error resume next towkbk.save if err.number 0 then err.clear msgbox "Error while trying to save the already opened workbook" _ & vblf & err.number & vblf & err.description end if on error goto 0 else 'close it, but try to save it on error resume next towkbk.close savechanges:=true if err.number 0 then err.clear msgbox "Error while trying to save and close the workbook" _ & vblf & err.number & vblf & err.description end if on error goto 0 end if ========= All untested, uncompiled. And I still wouldn't want to use something like this. set towkbk = nothing Ken C wrote: Where would I find that type of macro? "Dave Peterson" wrote: Pulling data from closed workbook using a formula is pretty straight-forward. Pushing data to another workbook (closed or open) is not so easy. I would use a macro and then my macro could open the "receiving" workbook. Ken C wrote: Is there a way to referance data from or to a closed workbook from an open one? I'm trying to referance data used in one workbook that is linked to others and would like to have some of the data automaticly transfer the same data into another workbook at the same time so as to cut down on having to enter the data more than once. Any help would be greatly appreciated. -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
Is there a way to referance data from or to a closed workbook from
Hi Ken, dalmon (do a search on this) has just asked a similar question
elsewhere. I have just put up 2 files for dalmon at:- www.pierrefondes.com (1st 2 files on home page) - that I think will give you what you want. Please hit Yes if my comments have been helpful to you. Thanks! "Ken C" wrote: Is there a way to referance data from or to a closed workbook from an open one? I'm trying to referance data used in one workbook that is linked to others and would like to have some of the data automaticly transfer the same data into another workbook at the same time so as to cut down on having to enter the data more than once. Any help would be greatly appreciated. |
#6
|
|||
|
|||
Is there a way to referance data from or to a closed workbook
sorry dalmon should read dalmom (search on this to see other posting).
"trip_to_tokyo" wrote: Hi Ken, dalmon (do a search on this) has just asked a similar question elsewhere. I have just put up 2 files for dalmon at:- www.pierrefondes.com (1st 2 files on home page) - that I think will give you what you want. Please hit Yes if my comments have been helpful to you. Thanks! "Ken C" wrote: Is there a way to referance data from or to a closed workbook from an open one? I'm trying to referance data used in one workbook that is linked to others and would like to have some of the data automaticly transfer the same data into another workbook at the same time so as to cut down on having to enter the data more than once. Any help would be greatly appreciated. |
Thread Tools | |
Display Modes | |
|
|