Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2013
    Posts
    8

    Unanswered: Export report to Excel.

    How i can export the Data of Report to Excel in Access 2007.

    Is it possible to export because the export option for Excel is disbaled.
    How can i do this.

    Can anybody help me please...

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Set a reference to the Microsoft Excel xx.x Library in your project.
    2. Paste this procedure in a standard module:
    Code:
    Public Sub ExportQueryToExcel(ByVal QueryName As String, ByVal XlFileName As String, Optional ByVal ExportHeaders As Boolean)
    
        
        Dim appXL As Excel.Application
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        Dim strAddress As String
        Dim str1 As String
        Dim str2 As String
        Dim lngRow As Long
        Dim lngColumn As Long
        Dim i As Long
        
        Set appXL = New Excel.Application
        appXL.Workbooks.Open XlFileName
        appXL.Visible = True
        Set qdf = CurrentDb.QueryDefs(QueryName)
        Set rst = qdf.OpenRecordset
        With rst
            If ExportHeaders = True Then
                lngRow = 1
                For i = 0 To .Fields.Count - 1
                    If i > 26 Then str1 = Chr(64 + i \ 26)
                    strAddress = str1 & Chr(65 + i Mod 26) & lngRow
                    appXL.ActiveSheet.Range(strAddress).Value = .Fields(i).Name
                Next i
                lngRow = lngRow + 1
                str1 = ""
            End If
            Do Until .EOF
                For i = 0 To .Fields.Count - 1
                    If i > 26 Then str1 = Chr(64 + i \ 26)
                    strAddress = str1 & Chr(65 + (i Mod 26)) & lngRow
                    appXL.ActiveSheet.Range(strAddress).Value = .Fields(i).Value
                Next i
                lngRow = lngRow + 1
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        qdf.Close
        Set qdf = Nothing
        appXL.Quit
        Set appXL = Nothing
               
    End Sub
    When you want to export to Excel, call this procedure whith:
    a) The name of the query which is the RecordSource property of the report.
    b) The name of the Excel file (full path).
    c) Whether you want the headers (column names) to be exported.

    Ex: ExportQueryToExcel "qry_SF_Reclamations_List", "U:\Classeur1.xls", True
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •