Crystal Reports Excel export with worksheets

Question

Hi,

Is there a way to convince Crystal Reports to export a page / group / whatever to separate worksheets when exporting to Excel (Data Only)? I'm using the CR that came with VS2008 (version 10.5)

Thanks.

Answer 1

According to the documentation you cannot export a report directly to multiple worksheets in a single Excel workbook.

When the limit of 65536 rows in Excel is reached though, the exporter does create a new worksheet, but you are not in control :)

update
To create your own Excel merger:

PRE: Make sure you have the Office (Excel) SDK libraries installed.
PRE: Place the files that need to be merged in a single directory.

In a VS2008 solution:

• Create a new empty Excel Workbook (variable: objNewWorkbook)
• Loop through the files in the directory (where you placed the Excel files) and for each item:
  1. Load the file as a Excel Workbook (variable: objWorkbookLoop)
  2. Create a new Worksheet in objNewWorkbook (optionally: with the filename of objWorkbookLoop) (variable: objNewWorksheetLoop)
  3. Copy the data from (probably sheet1 in) objWorkbookLoop to objNewWorksheetLoop
• Finally save objNewWorkbook to a file.