Reporting from Excel
Posted by John Newbold on December 13, 2001 8:48 AM
It sounded so simple but it is a nightmare. I have a nifty simulation model written in Excel. I use a VB interface to feed input data into the model and then I run it. So far so good. Then I want to report the results in an attractive, meaningful way: tables, graphs, diagrams, without the user being able to hack around in my program. Not an unreasonable objective. Nightmare. I was advised to use Crystal Reports, linking to Excel via ODBC. Takes forever and a day to refresh - simply not acceptable. In desperation resorted to the free Microsoft Excel Viewer. Made myself a pretty diagram as a separate 'xls' file, with an automatically updated link to my main 'xls' program. Then wrote VB code to launch the diagram file via Excel Viewer. Problem: the data don't refresh.
Frustration setting in big time. All I want to do is construct and manage (print, save) atttractive reports based on my Excel data. How can I do this? Why is it so difficult?
Any help or advice would be invaluable.
Posted by Damon Ostrander on December 13, 2001 11:10 PM
Hi John,
Sorry to hear about your "nightmare." It sounds like you may have made the problem unnecessarily hard by trying to separate the output from your simulation code. Crystal Reports is great for VB, but Excel provides so many of the same capabilities via Pivot Tables, charts, etc., that I wonder if the built-in Excel capabilities would provide the reporting formats you want if you weren't concerned about the security issue. I think the problem with users hacking your code is a red herring because Excel 2000 and beyond provide pretty secure password protection on your code (VBProject) and sheets if you follow reasonable password naming guidelines, such as making your password at least 8 characters, not including any dictionary words, and containing a numeral or two. In addition, if you turn your code into an Excel Add-In it is further protected and separated from the data it provides. Add-ins are easy to create, and provide security in that there is no easy way to turn an add-in back into code, since it is "compiled."
I hope this helps.
Damon
