Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Canno Verms (A2K 9.0.4402 SR-1)

    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?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canno Verms (A2K 9.0.4402 SR-1)

    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 !
    Francois

  3. #3
    Star Lounger
    Join Date
    Sep 2001
    Location
    SC, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canno Verms (A2K 9.0.4402 SR-1)

    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.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canno Verms (A2K 9.0.4402 SR-1)

    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.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canno Verms (A2K 9.0.4402 SR-1)

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

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canno Verms (A2K 9.0.4402 SR-1)

    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?
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canno Verms (A2K 9.0.4402 SR-1)

    Great idea!

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •