Canno Verms (A2K 9.0.4402 SR-1)

Topic

New Reply

I tried this message over in the Excel group, got no takers…

I’ve just ‘inherited’ a bunch of spreadsheets (152) that are programmed to simulate a database. Yes, 152. some of them have more than one sheet.

Is there a tool that will analyze this ‘thing’ and tell me how it works?

Can Access do anything with this mess?

Reply | Quote

Replies

Yes Excel can be read into Access.
But:
What kind of mess have you,
What kind of mess do you want,
With so few info how can we answer something else then mess !

Reply | Quote

The mess I have is … a mess!

There are links to various other spreadsheets embedded in various cells.

What i want is a tool that reads the tables and shows me all the links. Best would be something like the Access relations display.

At this point, I want a tool that tells me what the mess is.

Reply | Quote

Since you say the spreadsheets simulate a database, I am guessing you mean they deal with related data, or very similar information. If this is the case then you probably want to end up with one Access file(with multiple reports) instead of 152 seperate Excel files, which is a nightmare to maintain. Check the Access help on importing Excel files. If there is some commanality between the spreadsheets, you can design a new Access database with a table or tables that will contain the data from your spreadsheets. Data that is common to multiple spreadsheets, such as names, ssn numbers, etc…, should end up in only one Access table. One more suggestion: If many of the spreadsheets are almost identical(many users design a spreadsheet for every little difference they can think of), you should be able to design a few reports in Access to replace a large number of similar Excel spreadsheets. I’m just mentioning this because I worked on a project in which users had created over 200 related spreadsheets, and they wanted me to maintain these files, and to even create more spreadsheets for them. Instead I created one Access database with only a handful of reports, that varied mostly by one or two parameters.

Reply | Quote

Being lazy, I had hoped to avoid actually _looking_ at the sheets, and that a tool could do the work.

Reply | Quote

Peter:

It’s beyond my Excel programming ability, but one should be able to loop through each worksheet and list, say, all of the named ranges, references in one worksheet to another, etc.. Maybe somebody in the VB/VBA forum can lend a hand?

Reply | Quote

Great idea!

With the books I have lying around, lemme do some research ere I ask yet another dumb question …

Reply | Quote