HOWTO: Use MFC to Copy a DAO Recordset to Excel with Automation

Article translations Article translations
Article ID: 243394 - View products that this article applies to.
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

Summary

Microsoft Excel exposes the CopyFromRecordset method for a Range object to copy the contents of a Data Access Objects (DAO) Recordset onto a worksheet. This article demonstrates how you can use MFC DAO to build a recordset that you can then copy to an Excel worksheet using automation.

More information

The following steps demonstrate how you can obtain a recordset of all the records in the "Products" table in the sample Access database Northwind.mdb and then automate Excel to copy the contents of that recordset onto a worksheet. Although this example uses an Access database for its recordset, you could use any database for which you can provide an ODBC connection string. For details on using ODBC data sources with DAO, refer to the MFC DAO online help.

Sample Code

  1. Start a new MFC AppWizard EXE project that is dialog-based and named AutoExcel.
  2. On the View menu, click ClassWizard (or press CTRL+W). On the Automation tab, click Add Class and choose "From a Type Library". Browse to locate the Excel type library (Excel8.olb for Excel 97, Excel9.olb for Excel 2000, or Excel.Exe for Excel 2002 and Excel 2003).
    NOTE: The type library is located in the same directory as the Excel executable.
  3. Add all of the classes in the Excel type library that ClassWizard presents.
  4. Add a button named IDC_RUN to the dialog resource IDD_AUTOEXCEL_DIALOG and add the following code to the button handler.
    
    void CAutoExcelDlg::OnRun() 
    {
        //For optional arguments
        COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR);
    
        CDaoDatabase db;
        CDaoRecordset rs;
        CString sConn;
        long lNumCols;
    
        //Get a recordset that represents all the records in the Products 
        //table of the sample Northwind database
        sConn = 
         "C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb";
        db.Open(sConn, FALSE, FALSE);
        rs.m_pDatabase = &db;    
        rs.Open(AFX_DAO_USE_DEFAULT_TYPE, "Select * From Products", 0);
        lNumCols = rs.GetFieldCount();
    
        //Start a new workbook in Excel
        _Application oApp;
        oApp.CreateDispatch("Excel.Application");
        if (!oApp)
        {
            AfxMessageBox("Cannot start Excel");
            return;
        }
    
        Workbooks oBooks = oApp.GetWorkbooks();
        _Workbook oBook = oBooks.Add(vOpt);
        Worksheets oSheets = oBook.GetWorksheets();
        _Worksheet oSheet = oSheets.GetItem(COleVariant((short)1));
        Range oRange;
    
        //Transfer the data in the recordset to the worksheet
        COleDispatchDriver rs2;
        rs2.AttachDispatch((LPDISPATCH) rs.m_pDAORecordset);
        oRange = oSheet.GetRange(COleVariant("A2"), vOpt);
        oRange.CopyFromRecordset((LPUNKNOWN) rs2.m_lpDispatch, vOpt, vOpt);
        rs2.DetachDispatch();
        rs2.ReleaseDispatch();
    
        //Add the field names to row 1
        CDaoFieldInfo FieldInfo;
        for(long i=0; i<=lNumCols-1;i++)
        {
            oRange = oSheet.GetRange(COleVariant("A1"), vOpt);
            oRange = oRange.GetOffset(vOpt, COleVariant(i));
            rs.GetFieldInfo(i, FieldInfo, AFX_DAO_PRIMARY_INFO);
    
            oRange.SetValue(COleVariant(FieldInfo.m_strName));
            // For Excel 2003, SetValue takes two parameters.
           // oRange.SetValue(COleVariant(FieldInfo.m_strName),COleVariant());
        }
    
        //Format the worksheet
        oRange = oSheet.GetRange(COleVariant("A1"), vOpt);
        oRange = oRange.GetResize(COleVariant((short)1), 
                                  COleVariant(lNumCols));
        Font oFont = oRange.GetFont();
        oFont.SetBold(COleVariant((short)TRUE));
        oRange = oRange.GetEntireColumn();
        oRange.AutoFit();
    
        //Make Excel visible and give the user control
        oApp.SetVisible(TRUE);
        oApp.SetUserControl(TRUE);
    }
    
    						
  5. Include the header file that ClassWizard generated from the Excel type library to CAutoExcelDlg.cpp. For example:
        #include "excel8.h" /// For Excel 2003, use #include "excel.h"
    						
    NOTE: Add this directive after the include for "Stdafx.h" to avoid compiler errors.
  6. Include the header file for MFC DAO in StdAfx.h:
        #include <afxdao.h>
    						
  7. Modify CAutoExcelApp::InitInstance in AutoExcel.cpp so that COM services are initialized when your application starts:
        if(!AfxOleInit())
        {
            AfxMessageBox("Cannot initialize COM services.");
            return FALSE;
        }    
    						
    NOTE: Add this code before the call to DoModal().
  8. Build and run the application.
  9. Click the button on the dialog box. When the button handler completes, you will see the contents of the Products table from Northwind on the first worksheet of a workbook in Excel.

Properties

Article ID: 243394 - Last Review: December 24, 2014 - Revision: 5.0
Applies to
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual C++ 5.0 Professional Edition
Keywords: 
kbnosurvey kbarchive kbautomation kbhowto KB243394

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