Import multiple pages from Excel file into Access Table

Posted on 2012-08-21
Last Modified: 2012-08-23
I have an excel file that contains 71 pages, each page contains approx 50-60 rows of data. I need to import all the rows from each page into a single new access table.
Access 2003 and Excel 2003. I would like to do this from a button on an access form. The location of the excel file is C:\Reports\monthly.xls the location of the access database is C:\Database_Reports. The first row will be the header on the first page, each page has a header and is formatted the same
Question by:mickeyshelley1
    LVL 84
    EE works much better when you provide us with the code that you've done so far, and not when you post your project requirements. All that's missing from this is "I expect this by close of business tomorrow".

    Off my soapbox now ...

    How do you import them now? Are you using the built-in functionality of Access to do this, or are you using something like TransferSpreadsheet?

    Here is one way to do it:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourTableName", "Path to your spreadsheet", True, "1!"

    If it ALWAYS has 71 tabs:

    Dim i As Integer

    For i = 1 to 71
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourTableName", "Path to your spreadsheet", True, cstr(i) & "!"
    Next i

    See this link:
    LVL 119

    Accepted Solution


    Author Closing Comment

    Worked great, saved me a headache, my police officers cause me enough headaches
    LVL 119

    Expert Comment

    by:Rey Obrero
    glad to help!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now