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


Joined
Oct 1, 2009
Messages
2
Reaction score
0
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?
 
Ad

Advertisements


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

Top