REPORT TO EXCEL FORMAT

[sbaud2003]

Dear Sir/Madam,
I have a report to transfer in excel format, but the heading of the report is not taken, is there any method to get the title of the report in the 1st rown of excel sheet.

MY VBA CODE is

Private Sub CMDFB_Click()
Dim strReportName As String
Dim strPathUser As String
Dim strFilePath As String

strReportName = "RFINAL_BILLS"
strPathUser = Application.CurrentProject.Path & "\REPORTS"
strFilePath = strPathUser & strReportName & Format(Date, "yyyymmdd") & ".xls"

'export to excel
DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS, strFilePath

'launch excel file
Dim Shex As Object
Set Shex = CreateObject("Shell.Application")
Shex.Open (strFilePath)
End Sub

 

[Gasman]

Use Automation after you have transferred the report.

 

[theDBguy]

'launch excel file
Dim Shex As Object
Set Shex = CreateObject("Shell.Application")
Shex.Open (strFilePath)

The launch Excel bit is not necessary, because the OutputTo method has an argument to automatically open the new file after it is created; just set that argument to True.

DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS, strFilePath, True

 

[sbaud2003]

The launch Excel bit is not necessary, because the OutputTo method has an argument to automatically open the new file after it is created; just set that argument to True.

DoCmd.OutputTo acOutputReport, strReportName, acFormatXLS, strFilePath, True

Thanks, for help. But how to get exported the title (Label) of the report in 1st row of excel file

 

[Gasman]

I offered Automation?

Here is ChatGPT's offering

Sub ExportReportWithTitle()
    Dim xlApp As Object
    Dim xlBook As Object
    Dim xlSheet As Object
    Dim reportName As String
    Dim exportPath As String

    reportName = "YourReportName"
    exportPath = "C:\Temp\ReportExport.xlsx"

    ' Export report to Excel
    DoCmd.OutputTo acOutputReport, reportName, acFormatXLSX, exportPath, False

    ' Open Excel and insert title
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlBook = xlApp.Workbooks.Open(exportPath)
    Set xlSheet = xlBook.Sheets(1)

    ' Insert title in the first row
    xlSheet.Rows("1:1").Insert
    xlSheet.Cells(1, 1).Value = "Sales Report - Q2 2025"

    ' Optional: Format title
    With xlSheet.Range("A1")
        .Font.Bold = True
        .Font.Size = 14
    End With

    xlBook.Save
    xlBook.Close
    xlApp.Quit

    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

 

[theDBguy]

Thanks, for help. But how to get exported the title (Label) of the report in 1st row of excel file

@Gasman already told you how. I don't usually export reports to Excel; but when I did, I don't remember not seeing the header also exported. Do you have a complicated header? Does it have images?

 

[Gasman]

@Gasman already told you how. I don't usually export reports to Excel; but when I did, I don't remember not seeing the header exported.

No, I just tried and it is skipped.

 

[theDBguy]

No, I just tried and it is skipped.

Huh, I wonder if that's a new thing with newer versions. But like I said, I don't like exporting reports to Excel, because they don't usually retain the formatting anyways, so I prefer exporting queries to Excel instead.

 

[Gasman]

Yes, not pretty. Also appears to use the field names and not the captions or report captions?

Result