Results 1 to 4 of 4
  1. #1
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25

    Import Excel file to MS Access


    Hello everyone,

    I am very new to MS Access, So please help me in this.

    I need to import a excel file to MS Access database , where the excel file will be refreshed everyday and i need to get the updated data everyday how can i do this.

    I used the DoCmd.TransferSpreadsheet method but it is not updating the table in MS Access

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    instead , save the new workbook and overwrite the same place everytime...File2Import.xls
    link this excel file as an external table.
    make an append query using this tbl to 'import' your data.
    put the query into a macro, then the steps are:

    1. saveAs new excel file to File2Import.xls
    2. run import macro
    done.

  3. #3
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    Quote Originally Posted by ranman256 View Post
    instead , save the new workbook and overwrite the same place everytime...File2Import.xls
    link this excel file as an external table.
    make an append query using this tbl to 'import' your data.
    put the query into a macro, then the steps are:

    1. saveAs new excel file to File2Import.xls
    2. run import macro
    done.

    Thanks For replying this.. I do not want to link the excel file with the database because physically i do not have access to the file. only i have to write the code and give it to the user so that they can run it and i need to write a code which will generate a log file for each successful import. So can i write it in a module??

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You can do this in a macro - which you can then convert to code if you wish to get into using code
    - run a query to delete all the records from the table
    - import using ImportExportSpreadsheet to this table

    Not sure what you mean by "not updating the table".

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. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  3. Replies: 3
    Last Post: 12-06-2012, 03:47 PM
  4. Replies: 1
    Last Post: 04-20-2012, 07:20 AM
  5. Import Excel file to Access
    By emmett in forum Import/Export Data
    Replies: 3
    Last Post: 04-06-2012, 05:27 AM

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