By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,146 Members | 1,631 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,146 IT Pros & Developers. It's quick & easy.

VBA code to Import in Access 2007

P: 8

I have been running an access Database in Office 2003 for a while now. When I upgraded to Office 2007 one of my macro buttons has stopped working.

The Button looks up to VBA code to Import a specific Excel spread sheet from a specific location on my hard drive. This file is updated but never re-named or moved so to save time I have created the import macro to do the import steps for me.

This has been working fine for me in Office 2003. Once I upgraded to Office 2007 this has stopped working.

the code that I have been using is as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Import_PBs_Click()
  2. DoCmd.TransferSpreadsheet acImport, 0, _
  3. "z new pbs", "....\new pbscrs\newpbs.xls", True
  4. End Sub
where "z new pbs" is the table in access.

I have found that Office 2007 uses code starting: "DoCmd.RunCommand acCmdImportAttachExcel"

however I cannot seem to get this to automatically import the excel sheet, instead it opens the import wizard.

I have the imports saved in the "saved imports" wizard but i want to be able to run this from a macro button.

anyone have any suggestions?
Nov 19 '08 #1
Share this Question
Share on Google+
2 Replies

Expert 2.5K+
P: 3,532
Code does not run in 2007 unless your database resides in a folder that has been declared a “trusted” location.

To trust your folder, click:

Office Button (top left)
Access Options (bottom of dialog)
Trust Center (left)
Trust Center Settings (button)
Trusted Locations (left)
Add new location (button)

Linq ;0)>
Nov 19 '08 #2

P: 3
I was able to automate my Excel imports into Access 2007. I go into details here:
Nov 13 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.