Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have some reports that I export to Excel. If the heading is one word, the heading shows in Excel but if it is a heading like, "Membership with 2 or more Products," then all that appears is Text14 for example. Is there anyway to get the heading to show in Excel?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Unfortunately, from the menus, all you get is basically the detail records in your report, and none of the headers (report, group or page) seem to show up. However, if you resort to code it seems to work. See this thread by Leesha for sample code provided by Hans.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='799210' date='21-Oct-2009 17:22']Unfortunately, from the menus, all you get is basically the detail records in your report, and none of the headers (report, group or page) seem to show up. However, if you resort to code it seems to work. See this thread by Leesha for sample code provided by Hans.[/quote]


    Where would I put this code?

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

    Thanks for your help....

    I put the code (in Access 2007) behind the Print event of the report. I get on the second line a syntax error.

    Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)
    DoCmd.OutputTo ObjectType:=acOutputReport,
    ObjectName:="Union_Current_Table",
    OutputFormat:=acFormatXLS,
    OutputFile:="Union_Current_Table.xls",
    AutoStart:=True


    End Sub

    Everything is red - not a good sign....

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you want to divide an instruction into several lines, you must end each line (except for the last one) with a space and the line continuation character _.

    Code:
    Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)
      DoCmd.OutputTo ObjectType:=acOutputReport, _
    	ObjectName:="Union_Current_Table", _
    	OutputFormat:=acFormatXLS, _
    	OutputFile:="Union_Current_Table.xls", _
    	AutoStart:=True
    End Sub
    Without the line continuation characters, VBA thinks each line is a separate instruction, but the individual lines aren't valid instructions, of course.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799219' date='21-Oct-2009 19:46']If you want to divide an instruction into several lines, you must end each line (except for the last one) with a space and the line continuation character _.

    Code:
    Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)
      DoCmd.OutputTo ObjectType:=acOutputReport, _
    	ObjectName:="Union_Current_Table", _
    	OutputFormat:=acFormatXLS, _
    	OutputFile:="Union_Current_Table.xls", _
    	AutoStart:=True
    End Sub
    Without the line continuation characters, VBA thinks each line is a separate instruction, but the individual lines aren't valid instructions, of course.[/quote]

    That worked in Excel 2007. The report opens in Outline view. It seems like when you export to Excel, the report opens without outline view. Is there some code to open without outline?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you read your post and then explain what you meant to write?

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry - I guess if the report is grouped then when you export to Excel, it opens in Outline view. I must have exported a report to Excel that wasn't grouped so it didn't open in outline view in Excel.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That's correct - if a report has at least one group level, you'll get an Excel workbook with outline turned on. If the report has no group levels, the exported workbook will not have an outline.
    You cannot change this during the export, only afterwards in Excel.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799236' date='21-Oct-2009 20:34']That's correct - if a report has at least one group level, you'll get an Excel workbook with outline turned on. If the report has no group levels, the exported workbook will not have an outline.
    You cannot change this during the export, only afterwards in Excel.[/quote]


    I had a chance to test a report using the following code:
    Private Sub Report_Activate()

    DoCmd.OutputTo ObjectType:=acOutputReport, _
    ObjectName:="Rpt_CompareCurrentandPrev2_gains", _
    OutputFormat:=acFormatXLS, _
    OutputFile:="Gains.xls", _
    AutoStart:=True
    End Sub


    Only the headings above the text fields show a heading. If the field contains numbers, then I get Text14 for example. The report shows a field that contains text and then it shows a number field going across the whole report. I just wondered why the content of the column would affect the heading not showing..

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm sorry, I find it difficult to visualize what you describe. Could you attach a sample exported workbook?

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799332' date='22-Oct-2009 11:18']I'm sorry, I find it difficult to visualize what you describe. Could you attach a sample exported workbook?[/quote]
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The export will take the column headings from the names of the text boxes, so if your text box is named Text151, that's what you'll get in the exported worksheet...

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='HansV' post='799336' date='22-Oct-2009 05:39']The export will take the column headings from the names of the text boxes, so if your text box is named Text151, that's what you'll get in the exported worksheet...[/quote]
    To amplify on what Hans said, I suspect the column heading labels on your report are not associated with a text box, so in those cases, Access simply puts in the name of the text box in that column. Note that this export feature was just introduced in SP2 of Office 2007, so it has some issues. If you really want to control what your column headings are, you may want to resort to Automation and create your Excel workbook that way rather than using the export feature.
    Wendell

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='799370' date='22-Oct-2009 14:49']To amplify on what Hans said, I suspect the column heading labels on your report are not associated with a text box, so in those cases, Access simply puts in the name of the text box in that column. Note that this export feature was just introduced in SP2 of Office 2007, so it has some issues. If you really want to control what your column headings are, you may want to resort to Automation and create your Excel workbook that way rather than using the export feature.[/quote]


    How would I use Automation. I am not familiar with that.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See Wendell’s tutorial: Automation 101.

Page 1 of 2 12 LastLast

Posting Permissions

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