Results 1 to 4 of 4
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Import XLSX Into Existing Table


    What are the vba steps to import a spreadsheet into an existing access table?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    have you tried google with something like: "import spreadsheet to access"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    save the xl book as a generic name say: c:\temp\file2import.xlsx
    attach (link) the external sheet as an external table
    make an append query to add the xl data.

    then
    1. overwrite the file with new data: c:\temp\file2import.xlsx
    2. run query
    done.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Here is one example I use, excel file is called Comments. The first part checks to see if the excel file is there. Last part removes all records from table Sheet1, then loads new records from excel file using the RunSavedImportExport function. To get the import spec name, manually import the excel file first time and save the import specs which you then call in that command below.

    Dim dbs As Database
    Set dbs = CurrentDb

    ' Check for file
    If Dir("x:\Comments\DataFile\Comments.Xlsx") = "" Then
    MsgBox "The Comments File does not exist, please download a current Comments File, rename to Comments.xlsx and place into directory X:\Comments\DataFile."
    End
    End If


    'Import Comments from Excel file
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDelete_Sheet1"
    DoCmd.RunSavedImportExport "ImportCommentsNew"
    DoCmd.SetWarnings True

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

Similar Threads

  1. Replies: 6
    Last Post: 03-26-2018, 08:18 AM
  2. Import Excel file into an existing table via a macro
    By Gaby2811 in forum Import/Export Data
    Replies: 3
    Last Post: 09-23-2016, 06:12 AM
  3. Replies: 5
    Last Post: 02-21-2014, 07:55 PM
  4. Replies: 7
    Last Post: 04-15-2011, 08:46 AM
  5. Import multiple dbf files into existing table?
    By sbg2 in forum Import/Export Data
    Replies: 0
    Last Post: 06-12-2006, 02:06 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