VBA code to Import in Access 2007

[DAF LAD]

Hi.

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:

Private Sub Import_PBs_Click()
DoCmd.TransferSpreadsheet acImport, 0, _
"z new pbs", "....\new pbscrs\newpbs.xls", True
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?

[missinglinq]

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)>

[rjgibson1985]

I was able to automate my Excel imports into Access 2007. I go into details here:

http://bytes.com/topic/access/answer...-a#post3525566