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.
~Chopp
ChoppAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
1.  Add an Excel file
Click INSERT --> OLE OBJECT
Click the OPTION - CREATE FROM 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

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
Could the Excel file be used as input to a subreport that just reproduces it?

mlmcc
0
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
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

mlmccCommented:
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

mlmcc
0
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.
0
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?
0
mlmccCommented:
I don't know how OLE objects work when exported.

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.