Skip to Content
author's profile photo
Former Member

CR2011 - Export to Excel - Each page a separate sheet in the file?

I have a report that I need to export to Excel. However, this report has multiple pages. I export with Page Breaks set to YES and Page Headers for Every Page set to true. The client wants each page to be a separate sheet in a single Excel file. Is there some way that Export function can take care of that? In the end, I'll need to automate this functionality. The number of pages will vary each time the report is run.

HELP!

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    Posted on Mar 01, 2012 at 09:28 PM

    Hi,

    This has been on a wish list for years. Can't be done in Crystal. The only way would be if you have InfoView, generate the report and you can create multiple tabs. When you export the Infoview report the tabs get exported as well.

    I've worked around this but in involves creating an app that runs and exports the report for each tab. The program then opens an Excel file, creates a tab and copies each exported Excel file into a new tab.

    Works but not always convenient.

    Thanks,

    Brian

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Mar 02, 2012 at 01:59 PM

    HI ,

    below is the KB Article that confirms Crystal reports cannot cannot export each page to multiple sheets in excel.

    1260151 - CR XI R2 Export to multiple Excel worksheets

    However you have a work around for your requirement that might help you.

    Please follow below KB Article:

    1216567 - Is it possible to export one report to multiple Excel worksheets?

    Good luck,

    Sudhakar.ch

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Here is code that one can execute in Excel to split a long report in a single Excel worksheet into multiple sheets. In the code below, I have a Page Footer that says, "Run:" (and then had a date/time). At the end of the report, I have a Report Footer that is just "xxx" (as a marker). Anyway, for anyone else that might be interested, here's the code:

      Option Explicit

      Sub SplitData()

      Dim MySheetName As String

      Dim pageincr As Integer

      Dim PageNames(255) As String

      Dim PageNameNDX As Integer

      Dim i As Integer, j As Integer

      Dim corrected As Boolean

      Dim mycount As Integer

      Dim myrow As Integer

      Dim oldrow As Integer

      Dim last_row As Integer

      Dim last_column As Integer

      mycount = 0

      myrow = 0

      PageNameNDX = 0

      'Sheets("Sheet1").Range("D:D").EntireColumn.Delete xlShiftToLeft ' This removes the little "D" column

      Do

      mycount = mycount + 1

      oldrow = myrow + 1

      Sheets("Sheet1").Select

      MySheetName = Sheets("Sheet1").Range("D" & myrow + 2) & _

      Sheets("Sheet1").Range("F" & myrow + 2)

      corrected = False

      If PageNameNDX = 0 Then

      PageNames(PageNameNDX) = MySheetName

      PageNameNDX = 1

      Else

      For i = 0 To PageNameNDX

      If MySheetName = PageNames(i) Then

      GoSub NameCorrect

      End If

      Next i

      PageNameNDX = PageNameNDX + 1

      PageNames(PageNameNDX) = MySheetName

      End If

      Do

      myrow = myrow + 1

      Loop Until Left(Sheets("Sheet1").Range("A" & myrow), 4) = "Run:" Or _

      Left(Sheets("Sheet1").Range("A" & myrow + 1), 3) = "xxx"

      If Left(Sheets("Sheet1").Range("A" & myrow), 4) = "Run:" Then

      Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = Left(MySheetName, 31)

      Sheets("Sheet1").Select

      Rows(oldrow & ":" & myrow - 1).Select

      Selection.Copy

      Sheets(Left(MySheetName, 31)).Select

      With Range("A1")

      .PasteSpecial xlValues

      .PasteSpecial xlPasteFormats

      .PasteSpecial xlPasteColumnWidths

      End With

      ActiveWindow.DisplayGridlines = False

      ActiveSheet.Range("A1").Select

      last_row = Worksheets(Left(MySheetName, 31)).Cells.Find("", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row*

      last_column = Worksheets(Left(MySheetName, 31)).Cells.Find("", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column*

      With ActiveSheet

      .Range(.Columns(last_column + 1), .Columns(.Columns.Count)).EntireColumn.Interior.ColorIndex = xlNone

      .Range(.Rows(last_row + 1), .Rows(.Rows.Count)).EntireRow.Interior.ColorIndex = xlNone

      End With

      End If

      Loop Until Left(Sheets("Sheet1").Range("A" & myrow + 1), 3) = "xxx"

      Sheets("Sheet1").Select

      ActiveSheet.Range("A1").Select

      MsgBox "File Translated successfully", vbOKOnly

      Exit Sub

      NameCorrect:

      pageincr = 2

      StartCheck:

      For j = 0 To PageNameNDX

      If MySheetName & " (page " & pageincr & ")" = PageNames(j) Then

      pageincr = pageincr + 1

      GoTo StartCheck

      End If

      Next j

      MySheetName = MySheetName & " (page " & pageincr & ")"

      corrected = True

      Return

      End Sub

      Note that I have cell background color that needs to be removed past the end of each sheet's data. The code snip below (from the larger example above) takes care of that:

      last_row = Worksheets(Left(MySheetName, 31)).Cells.Find("", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row*

      last_column = Worksheets(Left(MySheetName, 31)).Cells.Find("", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column*

      With ActiveSheet

      .Range(.Columns(last_column + 1), .Columns(.Columns.Count)).EntireColumn.Interior.ColorIndex = xlNone

      .Range(.Rows(last_row + 1), .Rows(.Rows.Count)).EntireRow.Interior.ColorIndex = xlNone

      End With

      Note also that this example names each sheet according to the secondary title line, and since a long section might split up into multiple pages, I have code to add "(Page X)" text to the sheet name if it's a dup. This code is written in VBA.

  • author's profile photo
    Former Member
    Posted on Mar 01, 2012 at 08:34 PM

    Hi,

    You wanted to export each page of your Crystal report to be displayed in Different sheets and I guess this is not possible with Crystal Reports.

    You can raise an enhancement for this at <http://ideas.sap.com>

    Cheers,

    Kiran

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      I cannot wait for 6 months while SAP engineers debate whether or not to include this functionality in a future release. I need a real solution to this, and quickly.

      Thanks.