Excel Appending data from Excel 2007 to Access the joys of VBA

Oct 1, 2009
Reaction score
Hi I wonder if anyone can help me. I'm pretty new to VBA and have been doing some browsing on the net but I haven't been able to find what i'm looking for.

Basically, im looking to write a macro that appends data in an Excel '07 spreadsheet to an Access table.So far the closest i've I found the following code on MSDN.

As I mentioned i'm pretty new to VBA so i'm not sure how much the syntax has changed over the years.

[font=Verdana,Arial,Helvetica,sans-serif]The Visual Basic Code (Example I've used)
  • In a new workbook, enter the following data in cells A1:B3.CompanyName Phone

    United Shipping (111)222-3333

    Carriers Inc. (999)888-7777
  • Select cells A1:B3. Point to Name on the Insert menu, and then click Define. Type the nameMyTable and click OK.
  • Save this workbook as "C:\My Documents\Book1.xls" and close the workbook.
  • Start a new workbook and create the following procedure in the new workbook: Sub AppendTable()

    Dim db As database

    Dim rs As recordset

    Dim XLTable As TableDef

    Dim strSQL As String

    'Open the Microsoft Access database.

    Set db = OpenDatabase("C:\MSOffice\Access\Samples\Northwind.mdb")

    'Attach the Microsoft Excel 5.0 table "MyTable" from the file

    'Book1.xls to the Microsoft Access database.

    Set XLTable = db.CreateTableDef("Temp")

    'In Microsoft Excel 97, use


    ' XLTable.Connect = "Excel 8.0;DATABASE=...


    'The rest of the line is the same.


    XLTable.Connect = "Excel 5.0;DATABASE=C:\My Documents\Book1.xls"

    XLTable.SourceTableName = "MyTable"

    db.TableDefs.Append XLTable

    'Run the append query that adds all of the records from MyTable

    'to the Shippers table.

    strSQL = "Insert into Shippers Select * from Temp"

    'Execute the SQL statement.

    db.Execute strSQL

    'Remove the attached table because it's no longer needed.

    db.TableDefs.Delete "Temp"


    End Sub
Any ideas?


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question