Results 1 to 4 of 4
  1. #1
    UTLee is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    73

    How to import excel file with multiple worksheets


    I need to be able to import an excel file that has 4 different worksheets within it into 4 different tables in Access. For example, if the excel file is named DataImport.xls, is located at C:\DataImport.xls, has worksheets named worksheet1, worksheet2, worksheet3, and worksheet4 and if Access has tables named tblImport1, tblImport2, tblImport3, and tblImport4. I'd like it to import worksheet1 into tblImport1, worksheet2 into tblImport2, worksheet3 into tblImport3, and worksheet4 into tblImport4. Also, I need to be able to do this every day and I do not want to append the data every day, I would simply like to replace all of the data every day. Thanks much for your help!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    save the workbook to the same name everytime, say: ImportData.xlsx
    attach the 4 sheets as 4 external tables

    in a macro
    run 4 delete queries to empty the target tables
    run 4 append queries to add the new data

    so the steps would be:
    1.save and overwrite ImportData.xlsx
    2.run macro
    done.

  3. #3
    UTLee is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    73
    Thanks for your reply. Is there a way to do this with VB code? I'm not real familiar with macros in Access but can give it a shot if need be. Thanks again!

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Deleting data from a table is easy. The VBA would be something like
    Code:
    Currentdb.Execute "DELETE * FROM tblImport1;"
    Currentdb.Execute "DELETE * FROM tblImport2;"
    etc......

    You can manually import the sheets and save the import specs, but editing the specs if the Excel file name changes or the path changes is very difficult.
    You basically have to manually import the sheet again (saving the spec) for each worksheet.

    You will have to run each saved import to import the 4 sheets.
    See https://support.office.com/en-us/art...042BDDB640#bm2



    As an alternative you might look at Ken Snell's site for an all Access method:
    http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

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

Similar Threads

  1. Import Excel Worksheets and Skip Certain Columns
    By vcs1161 in forum Import/Export Data
    Replies: 5
    Last Post: 07-20-2015, 04:36 PM
  2. Replies: 3
    Last Post: 03-16-2014, 08:09 PM
  3. Replies: 9
    Last Post: 10-31-2013, 06:51 AM
  4. Splitting an Excel File into Multiple Access Tables on Import
    By TheWolfster in forum Import/Export Data
    Replies: 4
    Last Post: 04-29-2010, 04:52 AM
  5. Import Excel Worksheets into Access 2003
    By KramerJ in forum Programming
    Replies: 0
    Last Post: 03-18-2009, 04:11 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