Can I do this?

C

CrankyLemming

Hi,

Please could someone let me know if this is possible?

Can an open workbook import data from another, closed, workbook? Or
would they both need to be open? The problem I have is this:

I've compiled a list of all UK state benefits, qualification criteria,
amounts, useful contact numbers and addresses and so on. It isn't a
large file – around 1 meg, but I want it available to around 50
people, and our IT section would moan about everyone having the
document on their computer.

So what I wanted to do was have the one central information Workbook,
"Workbook B". Everyone has a small file on their desktop ("Workbook
A") . Every time they open it, Workbook A imports in the values from
Workbook B. When they close Workbook A it dumps the information again.

This is ideal because it means I can control the regular updates.

Obviously, if Workbook B has to be open, then this wouldn't work
anyway.

And if so, how do I do it?

TIA

Steve
 
L

Lady Layla

Why not just let everyone have read only access to the centralized workbook?
Put a shortcut icon on the desktop of those who need it, they will click it and
Voila -- they have access but they cant make any changes


: Hi,
:
: Please could someone let me know if this is possible?
:
: Can an open workbook import data from another, closed, workbook? Or
: would they both need to be open? The problem I have is this:
:
: I've compiled a list of all UK state benefits, qualification criteria,
: amounts, useful contact numbers and addresses and so on. It isn't a
: large file - around 1 meg, but I want it available to around 50
: people, and our IT section would moan about everyone having the
: document on their computer.
:
: So what I wanted to do was have the one central information Workbook,
: "Workbook B". Everyone has a small file on their desktop ("Workbook
: A") . Every time they open it, Workbook A imports in the values from
: Workbook B. When they close Workbook A it dumps the information again.
:
: This is ideal because it means I can control the regular updates.
:
: Obviously, if Workbook B has to be open, then this wouldn't work
: anyway.
:
: And if so, how do I do it?
:
: TIA
:
: Steve
 
C

CrankyLemming

Lady said:
Why not just let everyone have read only access to the centralized workbook?
Put a shortcut icon on the desktop of those who need it, they will click it
and Voila -- they have access but they cant make any changes
Click to expand...

Because the imported information is only part of the package. There's
also individual staff's working hours / flexible working time records,
sick records and training links etc, they have their own editable
contacts section as well.

Even without that, if I did strip it back to do as you suggested
(thanks for the quick reply, by the way), I've been led to believe
that several people accessing the one document would slow down the
system and impair performance.

Steve
 
M

Myrna Larson

What do you mean by "imports"?

If there are formulas in workbook A that refer to workbook B, if a person
opens A and B is not open, you get a message about the links and are asked if
you want to update them. If you say Yes, Excel retrieves the information but
does that "under the hood". The person doesn't end up with both workbooks
open.

If, OTOH, you mean that A has a VBA Workbook_Open event macro that retrieves
the data from B and enters it as literal values on A, then the workbook could
be opened, then immediately closed. This would all happen very quickly.
 
H

hgrove

CrankyLemming wrote...
...
. . . I've been led to believe that several people accessing the
one document would slow down the system and impair
performance.
Click to expand...

Who's led you astray like this? It ain't so unless a lot of people ope
the file at exactly the same time, and even then it's not that much of
performance hit.

Besides, if you think having several people open the file into Exce
would impair performance, why would you think the same number of peopl
accessing the closed file would be any less of a performance hit
Reading data from files *ALWAYS* requires opening those files, it'
just that some forms of opening files leave them open for users to vie
while other forms pull needed data then close the file. But (to repeat
*BOTH* require opening the file to read its contents
 
C

CrankyLemming

Myrna said:
If there are formulas in workbook A that refer to workbook B, if a person
opens A and B is not open, you get a message about the links and are asked if
you want to update them. If you say Yes, Excel retrieves the information but
does that "under the hood". The person doesn't end up with both workbooks
open.
Click to expand...

*smacks forehead*
Of course it does. I should have thought of that. That's going to be
my answer, I think.

But...
If, OTOH, you mean that A has a VBA Workbook_Open event macro that retrieves
the data from B and enters it as literal values on A, then the workbook could
be opened, then immediately closed. This would all happen very quickly.
Click to expand...

So how would I do this?

Thanks for the help.

Steve
 
H

Harold

yes you can do it and No you dont need them to be open

however if you need to make adjustments ect later on it is a pain i
the behind and some formula's that work easily in one spreadsheet get
bit touchy

I did this with my league spreadsheet and have spent a lot of tim
redesigning and converting it to one spreadsheet (and have learned th
hard way from the experience)
 
M

Myrna Larson

If you wanted to develop a macro, turn on the macro recorder and carry out the
steps manually. The code may need some "tweaking", depending on what you are
doing.
 
Top