Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Tabbed Excel Spreadsheet into Access? (a2k (9.0.6926) SP-3 Jet 4.0

    I have an Excel spreadsheet with the following 12 Tabs Jan through Dec for an entire year of transactions

    Each Tab can have a variable number of line items (from none to many) in the following format:

    <pre>Permit# Date Fee Value Name Address Desc CodeType
    1-2007 01/02/08 $156 $12,000 Brown, John 1234 Main St Remodel A 3
    2-2007 01/02/08 $176 $15,000 Brown, Mary 2345 Main St Deck C 5

    2 $332 $17,000
    </pre>


    I want to open a Access database an import the detail in each Excel Tab into one access table

    I need some code within access to automate opening the spreadsheet and import Jan into a new table and import Feb through Dec into the same existing new table while ignoring any blank records and the total counts record in each Tab

    See attached db for what the Jan Tab looks like

    Any examples or help would be appreciated

    Thanks, John
    Attached Files Attached Files

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

    Re: Import Tabbed Excel Spreadsheet into Access? (a2k (9.0.6926) SP-3 Jet

    I'd remove the totals rows from all the sheets in Excel. You should then be able to import them directly into Access.

    If that is not possible, please attach a representative sample workbook instead of a database.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Tabbed Excel Spreadsheet into Access? (a2k (9.0.6926) SP-3 Jet

    Here you go!
    Attached Files Attached Files

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

    Re: Import Tabbed Excel Spreadsheet into Access? (a2k (9.0.6926) SP-3 Jet

    The sheets aren't suitable for importing into Access since column E is blank. I'd delete column E and delete all the superfluous rows manually. That's probably faster than working around these problems using VBA.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Tabbed Excel Spreadsheet into Access? (a2k (9.0.6926) SP-3 Jet

    Thanks for the information

Posting Permissions

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