Solved

Automate the import of a spreadsheet into Access Database

Posted on 2012-03-27
5
355 Views
Last Modified: 2012-04-01
I'm working on a very simple database that allows me to reduce hundreds of employee salary change records into the most recent for each employee.  

Each time we update this query/report, I need to re-import the spreadsheet.  Is there a way to automate this such that the user can click a button on a report (or form) that will prompt the user to 'browse' for the spreadsheet.  I'd like to automatically use Sheet1 of the selected spreadsheet and place it into T_SalaryHistory, overwriting the current file.

Does anyone know how to do that?
0
Comment
Question by:BBlu
  • 3
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 300 total points
ID: 37774483
There is sample code by Ken Getz for browsing here:
http://access.mvps.org/access/api/api0001.htm

And for importing here:

http://www.accessmvp.com/kdsnell/EXCEL_Import.htm#ImpBrowseFile

(And a simple alternative is to have a predetermined location for your Excel sheet, and link to it from Access - so that the data is available for querying, reports, etc.  You can replace the linked spreadsheet when needed)
0
 

Author Comment

by:BBlu
ID: 37774551
oh..great idea. I'll try that.  And look at the sample code.
0
 
LVL 21
ID: 37774703
0
 

Author Comment

by:BBlu
ID: 37792374
mbizup-

I am trying to use your code but get an error that is saying "Field1" doesn't exist.  Attached are images of both the error popup and the line of code on which the code apparently breaks.
RunTimeError-2391.jpg
LineOfError.jpg
0
 

Author Closing Comment

by:BBlu
ID: 37793561
I found the mistake I was making and altered it.  After doing so, the solution worked perfectly!  Thanks mbizup.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question