Definite mind-twister for an Excel expert to try and solve
Posted: 9/3/2005 10:33:18 PM
By: Comfortably Anonymous
Times Read: 2,209
0 Dislikes: 0
Topic: Programming: Web Applications
I am working on an ASP.NET application that generates an Excel 2003
spreadsheet on demand for the user, containing sales information, and
also editable cells for the salerep user to enter forecasting information.
To make the spreadsheet easy to use, all cells are Protected except the
cells where the user will enter information.

However, since the information is presented in hierarchical layers, I need
to do grouping/outlining of the layers. That works great, until I Protect
the worksheet right before saving the generated workbook and allowing the
user to download it. When the user downloads it, the groupings show, but
when you try to collapse or expand the groupings, you get an error informing
you that the worksheet is protected instead of collapsing or expanding the
grouping.

I found the EnableOutlining property, but it is not saved with the
worksheet, so when the workbook is re-opened, the outlining is then again
locked. Confirming info at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xlproEnableOutlining1_HV05200924.asp

So the other approach I tried is to put in a Worksheet_Open sub into the
worksheet that will automatically set EnableOutlining to True when opened.
However, I cannot figure out how to add the code in from ASP.NET, as Excel
2003 returns an error "Programmatic access to Visual Basic Project is not
trusted".

How do I go about allowing code to be added while creating a spreadsheet? I
know about going into Excel and checking the "Trust access to visual basic
project" setting, but that doesn't seem to work when trying to generate
the workbook from a web app. I've seen some incomplete information about
modifying the local security policy to make this happen, but no good
details on what I need to allow.

Or preferably, is there some way to just set the ability to
expand/collapse the outline/group information when creating the protected
sheet?

Any information for either approach would be most appreciated! I'm beating
my head against the wall on this one! Feel like I'm stuck in the classic
Chicken and the Egg situation :(
Rating: (You must be logged in to vote)
Discussion View:
Replies:

Definite mind-twister for an Excel expert to try and solve
Posted: 9/3/2005 10:33:18 PM
By: Comfortably Anonymous
Times Read: 2,209
0 Dislikes: 0
Topic: Programming: Web Applications
The trick is to create a 'template' XLS workbook with a blank worksheet that contains the VBA Macro code in it. You then generate the spreadsheet with data (but no code) from VB.NET/ASP.NET, then copy the generated spreadsheet into the template workbook, then SaveAs the template to the desired filename so that the original template is unaffected.
Rating: (You must be logged in to vote)