Results 1 to 2 of 2

Import Ms Excel files into one table in Ms Access

  1. #1
    Pheobius is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    1

    Talking Import Ms Excel files into one table in Ms Access

    Im familiar with Ms Access, but have never used VBA or Scripts. I have 37 Excel files with the same data and would like to import into one file. Data will be received on a monthly basis into the same directory and I would like to automatically upload the data into the same file in Access.



    Help please, Tx

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    This is quite easy in Access.

    1. Use the External Data functionality [top of your screen], and manually walk through the process of importing ONE of the Excel files into a NEW table in Access.
    This will let Access create the table for you based on the data types of each of the columns in the Excel file.

    2. Next, create a New Macro and set the first Action to Set Warnings - No.
    [This will prevent Access giving you a message box each time the Macro tries to append data to the table from another Spreadsheet].

    3. Next, use the 'ImportExportSpreadsheet' Action in the Macro to import each of the Excel files and Append it to the Table you created earlier.
    You will need to include the Excel File you used first in the list of files that you use the 'ImportExportSpreadsheet' Action on.

    If you need to empty the table each month before you start your monthly imports, create a Delete Query to empty the table, and then do an 'Open Query' Action in the Macro before any of the 'ImportExportSpreadsheet' Actions.
    If you want the data to just be appended each month, then you don't need to worry about this.

    Hope this helps.

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

Similar Threads

  1. Import excel files
    By Jim.H. in forum Import/Export Data
    Replies: 1
    Last Post: 02-21-2012, 06:03 PM
  2. import excel files into access automatically
    By jstei012 in forum Import/Export Data
    Replies: 1
    Last Post: 12-19-2011, 04:12 PM
  3. Import Excel files on SharePoint 2010
    By jgelpi16 in forum Import/Export Data
    Replies: 1
    Last Post: 10-31-2011, 01:32 PM
  4. Replies: 3
    Last Post: 10-06-2011, 06:53 AM
  5. Cannot import excel or text files
    By donald_s in forum Access
    Replies: 2
    Last Post: 04-13-2010, 11:48 PM

Tags for this Thread

Posting Permissions

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