• You build a query to retrieve data from tables that are linked to an Excel workbook.
• You build a form that accesses data from tables that are linked to an Excel workbook.
• You use DAO or ADO to update tables programmatically that are linked to an Excel workbook.

Back to the top

• You are using Office Access 2007.
• You have installed Microsoft Office 2003 Service Pack 2 (SP2) or a later service pack or any Access 2003 updates that were released after Office 2003 SP2.
  
  For more information about Microsoft Office 2003 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
  887616  (http://kbalertz.com/Feedback.aspx?kbNumber=887616/ ) Description of Office 2003 Service Pack 2
• You have installed the update for Access 2002 (KB904018) that is dated October 18, 2005.
  
  For more information about the update for Access 2002 (KB904018), click the following article number to view the article in the Microsoft Knowledge Base:
  904018  (http://kbalertz.com/Feedback.aspx?kbNumber=904018/ ) Description of the update for Access 2002: October 18, 2005
• You have installed an Access runtime application that includes Microsoft Office 2003 Service Pack 2 (SP2) or a later service pack, any Access 2003 updates that were released after Office 2003 SP2, or the update for Access 2002 (KB904018) that is dated October 18, 2005 or later.
  For more information about how to include Office update files with an Access runtime application, click the following article number to view the article in the Microsoft Knowledge Base:
  916176  (http://kbalertz.com/Feedback.aspx?kbNumber=916176/ ) The wizard uses the run-time files that are included with the release version of Access when you create an installation package for an application in Access 2003 or in Access 2002

Back to the top

Back to the top

Method 1: Use Microsoft Excel

Back to the top

Method 2: Use Office Access 2007, Access 2003, or Access 2002

Public Sub WorkArounds()
On Error GoTo Leave

    Dim strSQL, SQL As String
    Dim Db As ADODB.Connection
    Set Db = New ADODB.Connection
    Db.CursorLocation = adUseClient
    Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=<AccessPath>"
    'Note: In Office Access 2007, use the following line of code:
    'Db.Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=<AccessPath>"
    SQL = "<MyQuery>"
    CopyRecordSetToXL SQL, Db
    Db.Close
    MsgBox "Access has successfully exported the data to excel file.", vbInformation, "Export Successful."
    Exit Sub
Leave:
        MsgBox Err.Description, vbCritical, "Error"
        Exit Sub
End Sub

Private Sub CopyRecordSetToXL(SQL As String, con As ADODB.Connection)
    Dim rs As New ADODB.Recordset
    Dim x
    Dim i As Integer, y As Integer
    Dim xlApp As Excel.Application
    Dim xlwbBook As Excel.Workbook, xlwbAddin As Excel.Workbook
    Dim xlwsSheet As Excel.Worksheet
    Dim rnData As Excel.Range
    Dim stFile As String, stAddin As String
    Dim rng As Range
    stFile = "<ExcelPath>"
    'Instantiate a new session with the COM-Object Excel.exe.
    Set xlApp = New Excel.Application
    Set xlwbBook = xlApp.Workbooks.Open(stFile)
    Set xlwsSheet = xlwbBook.Worksheets("<WorkSheets>")
    xlwsSheet.Activate
    'Getting the first cell to input the data.
    xlwsSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
    y = xlApp.ActiveCell.Column - 1
    xlApp.ActiveCell.Offset(1, -y).Select
    x = xlwsSheet.Application.ActiveCell.Cells.Address
    'Opening the recordset based on the SQL query and saving the data in the Excel worksheet.
    rs.CursorLocation = adUseClient
    If rs.State = adStateOpen Then
        rs.Close
    End If
    rs.Open SQL, con
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        x = Replace(x, "$", "")
        y = Mid(x, 2)
        Set rng = xlwsSheet.Range(x)
        xlwsSheet.Range(x).CopyFromRecordset rs
    End If
    xlwbBook.Close True
    xlApp.Quit
    Set xlwsSheet = Nothing
    Set xlwbBook = Nothing
    Set xlApp = Nothing

End Sub

• <AccessPath>
• <ExcelPath>
• <MyQuery>
  <MyQuery> is placeholder for the query that you run against the tables in the Access database. The result of the query is exported to the Excel workbook.
• <WorkSheets>
  <WorkSheets> is a placeholder for the worksheet in Excel to which you want to export the result.

Back to the top