Re: Excel and large numbers of linked files

[Elna Tymes <Etymes -at- LTS -dot- com>]

Meg Ehr wrote:

> The boss wants me to set up the structure for a series of files that will
> contain specific customer account data. She wants individual files for 800
> accounts (14+ data fields for each), plus a "snapshot" file showing selected
> data from each of the account files. The "snapshot" file should update
> automatically when data in the account files is changed, and will be
> attached to a Siebel database for viewing.
>
> The boss would like to do this in Excel, because she knows she can link the
> snapshot file to Siebel. Will Excel handle this well?

Let me see if I have this straight.  The "snapshot" file will actually be one big
spreadsheet with something like one cell (or more) for each of the 800 account
files.  Right?  And she wants to view the whole thing from a Siebel database?

First off, every time you open an Excel worksheet, it updates the data for all the
links in the worksheet. So if there are, say, 800 cells with links to other files,
it should go retrieve the updated data from each of those files as it opens. May
take some time, but yes, it will reflect updated data.

Second, your boss wants to view things from a Siebel database, which means that the
link(s) from the worksheet to the database will also have to be established.  If
any of those need calculating, that could also take time.  (Why not just view the
thing from the Excel snapshot worksheet?)

> Would Access be a better choice for this? Given the volume of data, that was
> my first instinct, but Boss's concern is that we wouldn't be able to update
> the file linked to Siebel automatically without running a report or
> something first. Is there a way to create a file that would update
> automatically with data from Access, and would also link to Siebel?

My understanding of the relationship between Access and Excel is that they are
pretty much two ways of viewing the same data.  Each has some special capabilities
- Access lets you have prettier reports and Excel gives you some incredibly
powerful formulas and other operational constructs - but you're dealing with mostly
the same stuff here.  Adding a layer of complexity by trying to work with Access
before linking to the Siebel database seems superfluous.

> If there is a resource that could help answer my questions, please point me
> to it -- I've been flailing around in the archives, Google, program help,
> and the MS website to try to figure this out, to no avail...

Have you looked at stuff at Woody's Office Watch?  I don't know how extensive the
stuff is on his web page, but he does a fairly exhaustive set of newsletters,
available free by asking, and maintains an archive of questions that have been
answered.  The only concern I would have, were I in your shoes, is whether Excel
will choke on updating links to 800 files in one worksheet, and if not, how long it
will take to update the linked contents.

Elna Tymes
Los Trancos Systems


^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Be a published author!  iUniverse gives you: a high-quality paperback, a
custom cover design, and distribution to 25,000 retailers.  And it's
affordable.  Join our almost 10,000 published authors today.
http://www.iuniverse.com/media/techwr

Sponsored by eHelp Corporation, makers of RoboHelp - the industry standard
in Help authoring. Download a trial version today or get special savings when 
you buy the RoboHelp 2002 Holiday Edition. Visit http://www.ehelp.com/techwr

---
You are currently subscribed to techwr-l as: archive -at- raycomm -dot- com
To unsubscribe send a blank email to leave-techwr-l-obscured -at- lists -dot- raycomm -dot- com
Send administrative questions to ejray -at- raycomm -dot- com -dot-  Visit 
http://www.raycomm.com/techwhirl/ for more resources and info.