Importing Excel Sheet automatically to Access

jplivingston08

New Member
Joined
May 14, 2010
Messages
7
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!!!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top