Hi I wonder if anyone can help me. I'm pretty new to VBA and have been doing some browsing on the net. Basically, im looking to write a macro that appends data in an Excel '07 spreadsheet to an Access table 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
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
- 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