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

[Jonathan]

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"
  
  
  
  db.Close
  
  
  
  End Sub

[/font]Any ideas?