July 03, 2007, 10:31 AM
MariePrint Ready Excel Template
I'm just starting to use Excel Templates and are trying to figure out how to simply use the template feature to get a print ready report. I saw the posting about using a Macro, and were successful in doing that. However, my spreadsheet is being sent outside the company, and they are not too keen on running Macros. I thought I heard someone talking about a different solution at Summit, whereby the data sheet was deleted after the data was populated. One of my reports is just one tab, however, the second one has 2 tabs with 2 different report on them.
One thing I couldn't figure out when using the method with the datasheet (besides how to hide or delete it afterwards) was how to reference the data when it is an unknown number of rows, including subtotal and totals.
I appriciate any help I can get on this.
Thanks, Marie
July 03, 2007, 11:52 AM
Tony AMarie,
This is really an Excel question and you might have more success looking on an Excel forum.
However, and having said that, Excel retains information about the last cell in the work sheet and this is accessible via - Cells.SpecialCells(xlLastCell) - which can be suffixed by Row or Column to enable you to construct your range statement.
Remember that you have to have a minimum of two work sheets within a web archive and therefore for your single sheet output you might like to add a delete for the work sheet that is not required.
Finally remember that you can reproduce most of what you want to do by doing it manually after choosing to record a new macro and then just rob the code produced
T
July 03, 2007, 12:15 PM
MarieTony,
Thanks for your reply. Now, my questing is, how do you delete a worksheet? Any code sample?
Secondly, when you say to rob the Macro code, where would I put that code without actually having the user run a Macro?
Thanks, Marie
July 03, 2007, 02:32 PM
Tony AMarie,
You say that the external users are not too keen on running macros, that doesn't say that they won't
and if they want the output in Excel
and formatted ready to print then it's not an onerous task for them. Show them the macros if they are that concerned. Alternatively get your company to fork out to get a digital signature or what ever it needs. Check out an MS Eggshell forum for info.
As for deletion of a worksheet, try worksheet("name").delete or check the help files for the syntax.
To run the macro without the user having to do anything other than set Eggshell to a medium macro security setting and click enable macros when asked, place the code in the Workbook_Open() function.
T
July 03, 2007, 03:00 PM
MarieThanks again Tony!
Based on your replies, I guess there is no way to do this within the WebFOCUS code. You have to do things in Excel.
Thanks, Marie