[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Automate the import of a spreadsheet into Access Database

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
BBlu
Asked:
BBlu
  • 3
1 Solution
 
mbizupCommented:
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
 
BBluAuthor Commented:
oh..great idea. I'll try that.  And look at the sample code.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
0
 
BBluAuthor Commented:
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
 
BBluAuthor Commented:
I found the mistake I was making and altered it.  After doing so, the solution worked perfectly!  Thanks mbizup.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now