Results 1 to 4 of 4
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    2,077

    Import Excel file with multiple sheets into single table

    Given an Excel file with multiple sheets, how does one import all sheets into a single table? Using the import wizard I only get the first sheet, whereas I'd like to keep importing each sheet sequentially until all sheets have been imported into the table.



    Can this be done, or does one have to do each sheet and then merge the tables?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    1. you can manually run thru the import for each sheet,
    or

    2. is the #sheets always the same? if so,

    a.save the workbook to the same place everytime, like: c:\temp\File2Import.xlsx
    b. manually link each sheet as an external table, ie: tSheet1, tSheet2
    c. next make an append query for each sheet: qaImportSheet1, qaImportSheet2
    d. put all queries into a macro: mImportAllXLsheets
    e run macro.

    then next time,
    1. save the workbook to c:\temp\File2Import.xlsx
    2. run macro.
    done.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    6,258
    I would use VBA and walk through each sheet and import with TransferDataBase.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    2,077
    In the current case, walking through each sheet turns out to be the simplest method and leads easily to the looping through the tables collection find what's needed.
    Thanks,
    Bill

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  2. Replies: 4
    Last Post: 07-29-2014, 06:18 AM
  3. Replies: 1
    Last Post: 08-25-2012, 06:11 PM
  4. Replies: 5
    Last Post: 12-01-2011, 05:38 PM
  5. Replies: 0
    Last Post: 04-29-2009, 04:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums