Results 1 to 5 of 5

Importing Excel Sheet automatically to Access

This is a discussion on Importing Excel Sheet automatically to Access within the Microsoft Access forums, part of the Question Forums category; We have an access database that currently draws information from tables within it and cross references that data with an ...

  1. #1
    New Member
    Join Date
    May 2010
    Posts
    7

    Default Importing Excel Sheet automatically to Access

    We have an access database that currently draws information from tables within it and cross references that data with an excel doc for additional information (linked Excel Doc). Our problem is that the excel doc locks when a user is in editing items in access and this then does not allow anyone to access the queries or reports or edit any others. The excel doc is purely just reference... it is never changed from access. We have a program that scrapes our core system here and spits out a new excel file each day that replaces the old. So, i am not sure how we can fix this. I think that if we were to import the excel doc everyday into an access table then multiple people could edit and view at once. It would be preferred that the excel doc overwrite all data in the table in access so that there are not multiple entries that are the same. Is there an automated way of doing this? through a Macro or .BAT file? I do not know anything about coding with VB and Access... Maybe there could be a bat file that would delete the tables data in access and replace it with the current excel data...? Any ideas? We are a growing company and more and more people need access to this information.. Thanks for all and any help in advance!!!

  2. #2
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,745

    Default Re: Importing Excel Sheet automatically to Access

    What you should look at is creating a MACRO that will run several stages, first it should look at Delete Object (Delete the table), then look to TransferSpreadsheet and it will then import the spreadsheet from the location where it is located.

    Once you have these steps working add additional arguments to Switch the Warnings Off so you don't get any prompts, then Switch the Warnings back on at the end, and finally add a Message Box to confirm the transfer has been successful.

    If using Access 2007 or above you have to create a New Macro and then tell it to show all Actions at the top.

    So the Actions would be listed like this (MAKE SURE YOU TAKE A COPY OF THE DATABASE AND SPREADSHEET AND TEST IT BEFORE RUNNING ON LIVE DATA).

    SetWarnings
    DeleteObject
    TransferSpreadsheet
    SetWarnings
    MsgBox
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2013
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  3. #3
    Board Regular
    Join Date
    May 2007
    Posts
    1,470

    Default Re: Importing Excel Sheet automatically to Access

    Hi Trevor - I have a similar obstacle. In the first step you suggest to delete the table, Wouldn't that delete all the properties of the table? Would you have to programatically create a new table and then import data? Thanks!

  4. #4
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,745

    Default Re: Importing Excel Sheet automatically to Access

    Yes it would change the properties as it is deleting the table, there wasn't any other request here, but for you perhaps what you can do is change the delete object to run a delete query to empty the table, then do the transfer into a temp table and then run an append query to fill the table.

    Does this sound OK for you to tackle?
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2013
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  5. #5
    Board Regular
    Join Date
    May 2007
    Posts
    1,470

    Default Re: Importing Excel Sheet automatically to Access

    Okay - thanks Trevor - it sounds like something I can try!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com