Excel VBA import data to existing Access database

Question

I've found similar questions asked before, but I can't seem to figure it out using those examples.

I'm writing a macro in Excel where the last step needs to import all the data in Sheet1!A:Q (headers in row 1) to Table1 in an Access database. I need to make sure each column in Excel corresponds to the correct field in Access.

I am using Excel 2010/2013 and Access 2010/2013. Code I am currently using is:

Sub AccImport()

Dim acc As New Access.Application
acc.OpenCurrentDatabase "C:\Users\Benjamin\Documents\Database1.accdb"
acc.DoCmd.TransferSpreadsheet _
        acImport, _
        acSpreadsheetTypeExcel12Xml, _
        "Sheet1", _
        Application.ActiveWorkbook.FullName, _
        True, _
        "A1:Q1000"         'Need to change to reflect the last row in the sheet
acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing

End Sub

The code results in error message, highlighting acc As New Access.Application and displaying "Compile error - User-defined type not defined"

Any help would be greatly appreciated. Thanks!

I figure I need to use some of this syntax -- msdn.microsoft.com/en-us/library/office/… and perhaps code from here ozgrid.com/forum/showthread.php?t=76110. If you can point me towards some code I think I feel comfortable adapting it, I just haven't found something I can adapt. @thunderblaster — blahblahblah
– blahblahblah, Commented Jan 12, 2014 at 23:11
I had tried this, but got an error: Dim acc As New Access.Application acc.OpenCurrentDatabase "C:\Users\Benjamin\Documents\Database1.accdb" acc.DoCmd.TransferSpreadsheet _ acImport, _ acSpreadsheetTypeExcel12Xml, _ "tblExcelImport", _ Application.ActiveWorkbook.FullName, _ True, _ "CallLog" acc.CloseCurrentDatabase acc.Quit Set acc = Nothing — blahblahblah
– blahblahblah, Commented Jan 12, 2014 at 23:19
Can you edit your question and include that code and also describe the error you're getting (both the specific error number & message, and also the line it errors on)? Your syntax looks correct, so it's hard to say what the problem is without the error details. — thunderblaster
– thunderblaster, Commented Jan 12, 2014 at 23:32
@thunderblaster added the code to the question. I'm still learning how to use stackoverflow properly! — blahblahblah
– blahblahblah, Commented Jan 13, 2014 at 0:54

thunderblaster · Accepted Answer · 2014-01-13 01:07:16Z

2

Try this: In the VBA Editor, go to Tools -> References. Scroll down to "Microsoft Access 11.0 Object Library" (yours might not be 11.0, but you're looking for the one that says Microsoft Access) and ensure the box is checked. If not, check it and click OK. This solved the compile error when I tested it.

answered Jan 13, 2014 at 1:07

thunderblaster

91811 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

blahblahblah Over a year ago

Thanks! I would have never though of that or found that!

JMG · Accepted Answer · 2014-01-13 01:08:55Z

1

Have you included references in VBA for Access?

Tools > references > Microsoft Access xx.x Object Library

answered Jan 13, 2014 at 1:08

JMG

16313 bronze badges

Collectives™ on Stack Overflow

Excel VBA import data to existing Access database

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related