Solved

Automate the import of a spreadsheet into Access Database

Posted on 2012-03-27
5
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

626 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