Outputing report to excel with naming.

[monplankton]

Hi all,
Been hunting a round looking for some code that will output my access report to excel but more inportantly add a unique field from the report to the name of the file. example would be [FileName] & [ReferenceNo].xls

Any help much apprciated.

By the way working in access 2003

 

[GinaWhipp]

Here's some code from http://www.btabdevelopment.com/ts/default.aspx?PageId=10

Function SendToExcel(strTQName As String, strSheetName As String)
On Error Resume Next
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
 
        Dim rst As DAO.Recordset
        Dim ApXL As Object
        Dim xlWBk As Object
        Dim xlWSh As Object
        Dim fld As DAO.Field
        Dim lngMaxRow As Long
        Dim lngMaxCol As Long
 
        Dim strPath As String
 
        On Error GoTo Err_Handler
 
            strPath = "Path Goes Here"
 
            Set rst = CurrentDb.OpenRecordset(strTQName)
            Set ApXL = CreateObject("Excel.Application")
 
 
            Set xlWBk = ApXL.Workbooks.Open(strPath)
            'Moved to end so Excel doesn't open before the report is finished rendering
            'ApXL.Visible = True
 
            Set xlWSh = xlWBk.Worksheets(strSheetName)
            xlWSh.Range("A6").Value = Date
 
            rst.MoveFirst
            xlWSh.Range("A8").CopyFromRecordset rst
            ' selects the first cell to unselect all cells
            xlWSh.Range("A8").SELECT
 
 
        xlWSh.Activate
        xlWSh.Cells.rows(7).AutoFilter
        xlWSh.Cells.rows(7).EntireColumn.AutoFit
 
        rst.Close
        Set rst = Nothing
        ApXL.DisplayAlerts = False
        xlWBk.SaveAs "C:\Your Path\" & Me.YourFieldName & ".xlsx", 51
        ApXL.DisplayAlerts = True
        'Open after report is completes
        ApXL.Visible = True
 
        Exit Function
Err_Handler:
        DoCmd.SetWarnings True
        MsgBox Err.Description, vbExclamation, Err.Number
        Exit Function
End Function

Hopefully, will get you started...

 

[ions]

Dim strFileName as string

strFileName = "FileName" & Reports![myReportName]![ReferenceNo] & ".xls"

DoCmd.OutputTo acOutputReport, "myReportName", acFormatXLS, strFileName