XL: How to Append an Excel Worksheet to a Database Using DAO

• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SUMMARY

The example code in this article demonstrates how to append the data from a Microsoft Excel worksheet to a table in a Microsoft Access database (.mdb). The code uses the SQL INSERT INTO statement to append the records from the Microsoft Excel Worksheet to the table in the Microsoft Access database.

NOTE: SQL syntax is not supported by Microsoft Technical Support and this Visual Basic example is provided as an example of this method only for reference.

MORE INFORMATION

The procedure uses the following algorithm:

• With data access objects (DAO), attach the Microsoft Excel table to a Microsoft Access database.
• Use an append query to add the records from the attached Microsoft Excel table to a table in a Microsoft Access database.

The Visual Basic Code Example

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

1. In a new workbook, enter the following data in cells A1:B3.

      CompanyName        Phone
      United Shipping    (111)222-3333
      Carriers Inc.      (999)888-7777
   
   

2. Select cells A1:B3. Point to Name on the Insert menu, and then click Define. Type the name "MyTable" and click OK.

3. Save this workbook as "C:\My Documents\Book1.xls" and close the workbook.

4. 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
   
   

1. With the module sheet active, click References on the Tools menu to reference the DAO Object library. If you are using Microsoft Excel version 7.0, click to select "Microsoft DAO 3.0 Object Library" in the list of Available References. If you are using Microsoft Excel version 97, click to select "Microsoft DAO 3.5 Object Library" in the list of Available References.

2. Run the macro.

REFERENCES

For more information about Data Access Methods, click the Contents tab in Microsoft Excel Help. Double-click "Microsoft Data Access Objects (DAO)," double-click "Data Access Methods Reference," and then double- click the appropriate letters to display the following topics:

   OpenRecordset, Execute, CreateTableDef, Append