Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi,

    I have rptITDReports that I need to print to excel AND have excel automatically open with the report. I can send the query to excel but it doesn't give the totals that are in the report. Presently they have a macro for the old version of their report that prints the report in excel format. The problem is that they have to then hunt to find the report. This was not a big deal form them but now there are new users in the office and they want to simplify it but having the report automatically open in excel vs having to print to excel and then hunt to find it........................trying to save mouse clicks.

    Thanks,
    Leesha

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    It is possible to export an Access report (not the query) to Excel, and then use Automation to open that file so it is visible on the workstation. However there are issues with 2007 unless you have SP2 installed. And I'm not sure about totals lines at the bottom - they should work, but..... and after some testing, it appears that headers and footers do not in either 2002 or 2007. So if you totals are there, you won't get them. In fact it appears it exports the data source for the report in most regards. Thus to really get what the users are asking for would probably involve Automating Excel and creating the report that way. Which is a fair bit of work...
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='WendellB' post='797895' date='14-Oct-2009 10:10']>>It is possible to export an Access report (not the query) to Excel, and then use Automation to open that file so it is visible on the workstation.

    This is what I'm trying to do but am not totally sure how to code it.

    >>However there are issues with 2007 unless you have SP2 installed. And I'm not sure about totals lines the bottom - they should work, but.....

    Figures!! And of course this is yet another account that has a mixture of Access versions. I grow to hate 2007 by the day. I know this is because I haven't worked it in enough to "think like it".[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't have Access 2007, but in earlier versions exporting a report to Excel is rather disappointing - only the detail records are exported, the group headers and footers, report header and footer and page header and footer are lost.
    You *can* automatically open the exported file:

    DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="rptITDReports", OutputFormat:=acFormatXLS, OutputFile:="ITDReport.xls", AutoStart:=True

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post
    This worked perfectly. The headers and the totals are there. I'll have to save this code to my "cheat sheet" files! Now lets hope it works in 2007 LOL. Worked great in my 2002 version.

    Thanks,
    Leesha

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Interesting - it appears the DoCmd.OutputTo works differently than the export from the Print Preview of a report. That's good to know. Thanks!
    Wendell

Posting Permissions

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