Insert Excel Sheet in Crystal Report

Good day, Experts.

I have been experimenting with ways to insert an Excel sheet in a Crystal Report ver. 11
My goal is to insert an Excel sheet on my report that lives in a folder on the share drive at my workplace, and then schedule the Crystal Report and have the data in the Excel sheet on the report reflect the data currently in that Excel sheet on the share drive.  (In other words, link the Excel sheet on the report to the source Excel sheet.)

I have THREE questions:

1 - Can you provide a list of steps for inserting the Excel sheet (OLE object?) linked to the original file where the Excel sheet lives so that any updates to the original will be reflected on the Crystal Report?

2 - If a user edits the Excel sheet on the Crystal Report, will that edit also be reflected on the original Excel sheet on the share drive where it lives?  My goal is to DISALLOW any changes being made to the Excel sheet on the Crystal Report or the original.

3 - Is there a way to resize the Excel sheet on the Crystal Report so that the whole sheet is visible without having to double click and open the Excel program?

Thank you for any guidance with this project.
Who is Participating?
mlmccConnect With a Mentor Commented:
1.  Add an Excel file
BROWSE to the file
Select the file
Click OPEN
ENsure the LINK option is checked
Click OK
Place it on the report where you want it to go.
    Remember a page header or footer cannot be larger than the page

Updates will be reflected when you open the report.  They will not be reflected when you refresh the report.

2.  The user cannot edit the Excel file in the report.
He can double click it and open it in Excel and make changes.  These changes will be reflected in the report.
He will be able to save the changes to the spreadsheet file
There does not seem to be a way to protect the sheet so the user cannot click on it

3. The full sheet will come in when you insert it.  It will expand horizontally and go to the next page as required

Could the Excel file be used as input to a subreport that just reproduces it?

ChoppAuthor Commented:
Thank you, mlmcc!  I will make a test report using the instructions you provideded in your first comment.

Can you elaborate on the idea in your second comment?

Respectfully, Chopp
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

You can simply make a report using the Excel file as the data source
Your column headings will be the field names
Each row will be a record so you just need to drop the fields on the report in the order they are in the spreadsheet

You may have to get creative if the spreadsheet is wider than the report

ChoppAuthor Commented:
Yes, I did try that first.  Unfortunately, the original Excel file has some amount of fancy formatting, with groups, subtotals, etc.  When I added the sheets into the data model of the Crystal Report, I didn't see a reliable or logical way to link the sheets or make sense of the data.
ChoppAuthor Commented:
I placed two OLE objects on a Crystal Report - one as an icon, and one directly.
When I exported the report to a PDF, I was not able to open either OLE object in the Excel program.  Do OLE objects open in their source program from a PDF?
I don't know how OLE objects work when exported.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.