Creating Custom Reports From Excel App.

[TheRobster]

Hi all,

I am working on a spreadsheet application for the water industry that predicts the cost of various types of infrastructure works. The spreadsheet is quite simple to use but it performs a lot of calculations. Each user may want to create a report of their own project and the results they want in their report will probably vary from user to user. The spreadsheet actually has lots and lots of results and graphs and so to make it easy to create custom reports I want to have a 'Create Custom Reports' worksheet. This will basically be a lot of check boxes with which people will select what results/graphs/etc they want to include in the report.

My question is (before I create this worksheet): what is the best way to do this? I was thinking of having the report created in Excel but I thought a better approach would be to have Excel export the required information to Word, where it can be edited and printed like a normal document.

So, I am not asking for advice on how to create this worksheet. The help I need is on whether to have the report in Excel or Word. Which is the best/easiest one to use? If I use Word I will obviously have to export the selected data from Excel into Word. Is this easy? Would it require some VBA programming?

Thanks in advance.
-Rob

[RobDog888]

Just some food for thought:

If you were to place your app in Access instead of Excel, you could use a
form to allow them to choose the options for the report. Then either display
the report in Access or use the 'Analyze With...' menu item to export it to
Excel or Word automatically. This would give you three reporting options.

What do you think?

Also, what version of Office do your users have?

[TheRobster]

Hi RobDogg,

The application is almost complete and I don't want to have to re-do it. Besides, I've never really used Access before whilst I am quite familiar with Excel, so I'm afraid that is the only viable option for me.

I don't know exactly what version of Excel the user audience will have. I am writing it in Excel 2000 so I am going to recommend they have Excel 2000 or above, although I will be testing it in all the versions that I can find and also on as many different PCs as I can to ensure that it works across different platforms.

The way I was thinking of proceeding is this:

Have one worksheet with lots of checkboxes on so that users can select/deselect what they want to appear in the report e.g. results tables, graphs, text, etc.... then have another worksheet on which all these items are present. Then, for the items that are deselected simply hide the rows that contain these items. Then when the report is printed it will only print those items that are still visible (unhidden).

I think this can be made to work. I was also thinking of having an 'Export to MS Word' option, which is simply a button that the user clicks and Excel copies all the visible information, opens Word and pastes it into a new document.

If anyone has any comments about how feasible this is, or any ideas how I might do it better, then your thoughts are appreciated.

Many thanks
-Rob

[TheRobster]

Mmm, okay. I just tried selecting/copying a whole worsheet with both text and graphs on, and then pasting it into Word. It only copies/pastes the text though - not the graphs. This could be a problem...

[RobDog888]

Try inserting an object from the Insert menu in Word. Then browse to your
xl file and it will load the entire workbook.

HTH