Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports export to Excel (2003)

    Hi,

    I would like to export one report to excel file by clicking one button. I know this have been discussed many times, but I did research and I can't find the answers for my questions.

    Since the report have a date range and let user to select date in a form. So how should I handle date range as I export to excel?

    Is any great example of exporting report to excel?

    Regards

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports export to Excel (2003)

    You can create a query that selects records that fall within the date range specified by the user.
    The criteria for the date field in the query would look like this:

    Between [Forms]![frmSelect]![txtStartDate] And [Forms]![frmSelect]![txtEndDate]

    where frmSelect is the name of the form, and txtStartDate and txtEndDate are the text boxes in which the user specifies the start and end dates.
    Use this query as Record Source for the report.
    Create a command button on the form, and put code like this in the On Click event of the button:

    Private Sub cmdExport_Click()
    DoCmd.OutputTo acOutputReport, "rptMyReport", acFormatXLS, "C:TestMyReport.xls"
    End Sub

    where cmdExport is the name of the button, rptMyReport is the name of the report, and C:TestMyReport.xls is the path and filename of the file to be created.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports export to Excel (2003)

    Thank you so much, Hans.

    I try use the code and I am able export it to excel file with data. But all text fields are gone and the format are different. So what should I do now?

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports export to Excel (2003)

    It depends on what you want to do. Do you want to have a faithful copy of the report?

    If so, try exporting to snapshot format, or if you have a PDF printer, print it to PDF format.

    If you're more interested in the data, you should export a table or query instead of a report.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports export to Excel (2003)

    I am more interested in the data and I should export a query. But after I export a query to excel, how can I make excel to the format that I want.

    Should I create a template and export the data from query to the template? What's the best way to do it?

    Thanks

    Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports export to Excel (2003)

    A template won't help. You'll have to format the Excel workbook after exporting the data. There are two ways you can do this:

    1) Use DoCmd.OutputTo or DoCmd.TransferSpreadsheet to export the query to an Excel workbook.
    Then use Automation to start Excel from Access, open the workbook and format it. You'll be executing Excel VBA code from Access, which can be a daunting task to program.
    See Wendell’s tutorial Automation 101 for a general introduction to Automation and useful links.

    or

    2) You can use the CopyFromRecordset method in Excel to import data from an Access query into a worksheet, then format the result. You'd be writing all the code in Excel, which might be easier than combining Access and Excel VBA. If you type CopyFromRecordset in the Visual Basic Editor for Excel and press F1, you'll get help, with a complete example.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports export to Excel (2003)

    Thanks, Hans.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports export to Excel (2003)

    Hi Hans,

    I put the following criteria for the date field in the query:

    Between [Forms]![frmSelect]![txtStartDate] And [Forms]![frmSelect]![txtEndDate]

    Then I use Do.Cmd.TransferSpeadsheet acExport, acSpreadsheetTypeExcel8, to export to excel.

    Everything looks good. But I would like date range that I enter on the form to shows on the excel too.

    So how can I do it?

    Thanks

    Regards

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reports export to Excel (2003)

    You can add two columns to the query:

    StartDate: [Forms]![frmSelect]![txtStartDate]

    and

    EndDate: [Forms]![frmSelect]![txtEndDate]

    The dates will be displayed in each row.

  10. #10
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports export to Excel (2003)

    Thanks, Hans. This works.

Posting Permissions

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