Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Workbook -> Access (Access 2000)-MULTI-Excel Wo

    I have an Excel workbook consisting of multiple sheets, that I'd like to convert to an Access database. Each sheet has identical column structure, and I'm wondering what is the best way to do the conversion (from .xls, tab delimited text file etc.)

    I also have VBA code in the workbook (in particular, numerous forms to display cell values and calculations involving same). Can I import these or do they need to be manually redone?

    From some of the complications I've encountered so far (eg. only one worksheet importable at a time) I'm wondering if the more manual copy & paste methods might end up being the quickest in the long run.

    Any suggestions for a first timer at this are appreciated.

    thanks

    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Workbook -> Access (Access 2000)-MULTI-Excel Wo

    There are various way to import from Excel, either by first exporting to a tab delimited file, or by naming your ranges in Excel so that you can import just the specific ranges into Access, or just by linking to particular worksheets and then appending the data as if they were tables. However, you don't want a bunch of identical tables in Access because then you might as well leave the data in Excel, so you'll probably need to think about table design *before* you start importing data. If you need help with that, post questions back here and you'll get lots of suggestions.

    As for the code, forms, and calculations, get ready to manually create them in Access. Access does *not* use the same forms as the rest of Office and it does not use the Worksheet functions you may be familiar with in Excel. The object model is entirely different, so your learning curve may be steep for a while and quite a bit of your code won't translate.
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Workbook -> Access (Access 2000)-MULTI-Excel Wo-MULTI-Re: Excel Workbook -> Access (Access 2000)-MULTI-Excel Wo

    Thanks Charlotte. Considering the apparent fundamental differences between this and other office apps, and the fact that the existing records number only in the hundreds, I might try constructing it from scratch, save for some copying/pasting. I'll have to jump on the learning curve somewhere, so it might as well be at the bottom [img]/forums/images/smilies/smile.gif[/img]

    Alan

Posting Permissions

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