Definite mind-twister for an Excel MVP

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/de...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

Why not have your asp.net code start by opening a workbook that contains the
required code?

--
Charles Williams
Decision Models

"Joe Schmoe" <[email protected]> wrote in message
news:[email protected]...
>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/de...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
>

Wanted to do that, however, am first generating the spreadsheet as Excel-
formatted XML, then opening the XML with the Excel object, and saving it as
an .XLS.

Initially tried building the spreadsheet with a 'template' XLS file with the
basics already in it, however, with all the formulas being plugged into this
spreadsheet (Several thousand in each worksheet, and several worksheets in
the workbook), using Interop calls to build the worksheet became
prohibitively slow (Nearly 20 minutes for the most complex ones), so
switched to XML format which puts the generation time down to less than a
minute. However, doing it this way makes it so Macros cannot be embedded in
it. (Excel XML doesn't allow that)

Did try combining the new workbook with an existing 'template' workbook that
contains the code, but then I run into the problem with the "Programmatic
access to Visual Basic Project is not trusted" error popping up.

Thanks for your suggestion however, got any others?

"Charles Williams" <[email protected]> wrote in message
news:%[email protected]...
> Why not have your asp.net code start by opening a workbook that contains
> the required code?
>
> --
> Charles Williams
> Decision Models
>
> "Joe Schmoe" <[email protected]> wrote in message
> news:[email protected]...
>>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/de...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
>>
>
>

OK, my approach was wrong. I was trying to copy the template workbook (The
one with the Macro code already embedded) INTO the generated spreadsheet.
That gave me the "Programmatic access to Visual Basic Project is not
trusted" problem.

The solution is to copy the generated spreadsheet into the template
workbook, then SaveAs the template to the desired filename.

Works just fine now, other than the fact that I cannot delete the worksheet
that is part of the template workbook. Had two other people here having a
great time trying to figure out how to delete it (Using tested code from
other apps to delete worksheets, so we know it works normally), finally gave
up and just set the sheet so that Visible=False. Very strange.

Thanks!

"Joe Schmoe" <[email protected]> wrote in message
news:TJhTe.20328$tT.15022@okepread02...
> Wanted to do that, however, am first generating the spreadsheet as Excel-
> formatted XML, then opening the XML with the Excel object, and saving it
> as an .XLS.
>
> Initially tried building the spreadsheet with a 'template' XLS file with
> the basics already in it, however, with all the formulas being plugged
> into this spreadsheet (Several thousand in each worksheet, and several
> worksheets in the workbook), using Interop calls to build the worksheet
> became prohibitively slow (Nearly 20 minutes for the most complex ones),
> so switched to XML format which puts the generation time down to less than
> a minute. However, doing it this way makes it so Macros cannot be
> embedded in it. (Excel XML doesn't allow that)
>
> Did try combining the new workbook with an existing 'template' workbook
> that contains the code, but then I run into the problem with the
> "Programmatic access to Visual Basic Project is not trusted" error popping
> up.
>
> Thanks for your suggestion however, got any others?
>
> "Charles Williams" <[email protected]> wrote in message
> news:%[email protected]...
>> Why not have your asp.net code start by opening a workbook that contains
>> the required code?
>>
>> --
>> Charles Williams
>> Decision Models
>>
>> "Joe Schmoe" <[email protected]> wrote in message
>> news:[email protected]...
>>>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/de...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
>>>
>>
>>
>
>