How to Append an Excel Worksheet to a Database Using DAO

Article translations Article translations
Article ID: 145826 - View products that this article applies to.
This article was previously published under Q145826
Expand all | Collapse all

On This Page

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

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. 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

  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
    						
NOTE: This macro utilizes the Northwind.mdb sample database that is installed with Microsoft Office Professional, versions 97 and 7.0. With version 7.0, by default, this file is located in the directory C:\MSOffice\Access\Samples. With version 97, by default, this file is located in the directory C:\Program Files\Microsoft Office\Office\Samples. You may need to modify this macro so that the path to the sample database is correct for your installation.

  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.
After the macro runs, the two records that you created in the workbook Book1.xls have been added to the Shippers table in the sample Microsoft Access database Northwind.mdb.

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

Properties

Article ID: 145826 - Last Review: October 11, 2006 - Revision: 2.3
APPLIES TO
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
Keywords: 
kbdtacode kbhowto kbprogramming kbualink97 KB145826
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com