Import Excel 2007 sheet into Access 2007 Table

I have a Excel 2007 spreadsheet
C:\Documents and Settings\All Users\Documents\DayImport.xlsx
I want to import the sheet DayRcpt into an Access 2007 table named tblDayRcpt in the database
C:\Documents and Settings\All Users\Documents\Orders.accdb

I tried the following script



Private Sub btnImport_Click()
Const acImport = 0
Const acSpreadsheetTypeExcel112 = 9
 
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Documents and Settings\All Users\Documents\Orders.accdb"
 
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"tblDayRcpt", "C:\Documents and Settings\All Users\Documents\DayImport.xlsx", True
 
 
End Sub

Open in new window

LVL 1
ScamquistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
and what happened?
0
ScamquistAuthor Commented:
I receive error code 424 and it fails on the statement

objAccess.OpenCurrentDatabase "C:\Documents and Settings\All Users\Documents\Orders.accdb"

Error424.jpg
ErrorCode.jpg
0
ScamquistAuthor Commented:
My mistake, I commented out the line above the statement.

When I re-ran, I got the following error:
Error code 3734
Error3734.jpg
ErrorCode3734.jpg
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jeffrey CoachmanMIS LiasonCommented:
If you are trying to import the same file and/or sheet you currently using, I can see things like this happening.
0
Jeffrey CoachmanMIS LiasonCommented:
You could try "Pushing" the data from Excel to Access:
http://www.ozgrid.com/Services/excel-import-assistant.htm
0
ScamquistAuthor Commented:
Sorry, I was out for a couple of days.  
I am not importing the information from a table that is open.
Pushing the data from Excel is not an option.

I corrected the syntax of my script and it imports the data.  However, another instance of the database opens and closes, leaving the table in question fully populated.



0
Jeffrey CoachmanMIS LiasonCommented:
See my post: 24444970
0
ScamquistAuthor Commented:
The only item I have open in the Access database is the form with a button to import from Excel.  The destination table is not open.  Excel is not open.  

Is there anything you can see in the script that would cause the database to briefly open and close.  The end result is the data is being populated.  It is just strange that a second instance of the database opens and closes.
Private Sub btnImport_Click()
Const acImport = 0
Const acSpreadsheetTypeExcel112 = 9
 
DoCmd.SetWarnings False
 
 
DoCmd.SetWarnings True
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Documents and Settings\All Users\Documents\Orders.accdb"
 
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"tblDayRcpt", "C:\Documents and Settings\All Users\Documents\DayImport.xlsx", True
 
 
End Sub

Open in new window

0
peter57rCommented:
If you are running this from the database you are importing into, then all you need is...

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"tblDayRcpt", "C:\Documents and Settings\All Users\Documents\DayImport.xlsx", True

All the rest of the code can be removed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScamquistAuthor Commented:
Thank you peter57r.
I added a bit of precode to get rid of the existing records in the table.

Works like a champ.
Private Sub btnImport_Click()
 
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblDayRcpt"
DoCmd.SetWarnings True
 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"tblDayRcpt", "C:\Documents and Settings\All Users\Documents\DayImport.xlsx", True
 
End Sub

Open in new window

0
ScamquistAuthor Commented:
Thank you for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.