Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting Multiple Sheets to Access (Excel 2000)

    I have an Excel workbook with 100 identical structured worksheets with different data. I would like to put each sheet as a data record into an access database. What is the best way to do that ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exporting Multiple Sheets to Access (Excel 2000)

    Can you explain how you want to fit a worksheet in one data record?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Multiple Sheets to Access (Excel 2000)

    I guess it would be simplest to first use Excel to create a database like table in *one* worksheet. Then import that single worksheet into Access.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Multiple Sheets to Access (Excel 2000)

    If each worksheet is a personal information form with about 100 items arranged in an array, and I have 100 worksheets (100 persons' info). WHat is the best way to put them into an access database ?

    Thanks in advance for your help, HansV.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Multiple Sheets to Access (Excel 2000)

    Is the database designed? Is everything from each sheet going into one row or one table (if so, the database is probably not normalized), or does information go to different tables?

    What you are probably going to have to do is write some VBA code that will create a worksheet for each table and copy the data that goes into that table from all of the worksheets so that you end up with a row in the worksheet for each person.
    Legare Coleman

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Exporting Multiple Sheets to Access (Excel 2000)

    You could Jan's suggestion getting all 100 sheets of data into one sheet. Then use Access to "import" this worksheet as a table.

    Crating the "one sheet" either via VB code, or if the sheets are setup IDENTICALLY, you could use the INDIRECT function with a little setup to "extract all" the data into a sheet with almost no coding whatsoever. (I would run some code to get the 100 sheet names rather than typing them all in), but all else could be done "relatively easily" without ANY code.

    Steve

Posting Permissions

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