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.

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

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

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

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.

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.