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

[Former Member]

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

[former_member292966 · Accepted solution]

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

[Former Member]

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

[Former Member]

Thanks Brian and Sudhakar. And I'll look at those KB articles.

[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.

[Former Member]

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

[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.