Creating xls file via xml getting Run-time error '1004'

[ydnam]

My application is creating an xml file which is about the same format
which is used to create excel file. This xml file is read by a VB
application(whose code I do not have). The VB app creates a xls file
and password protects the same.

The change which I wanted to carry out was:
For one of the cells, I would like the contents to be displayed in the
formula bar, even if the user has opened the excel sheet in the read
only mode (without the password)

If I want to try this out in excel I would need to follow the below
steps
a)Right Click on the cell
b)Format Cells ->Protection->Hidden checkbox should be unchecked

As per MS documentation, this can be acheived by making the following
changes in the XML File
1) In the style for that particular cell, we need to put in the
attribute
<Protection x:HideFormula="0" ssrotected="1"/>
2) In the worksheet tag we need to put
<Worksheet ss:Name="Sheet1" ssrotected="1">
instead of
<Worksheet ss:Name="Sheet1">

Now if I give this modified file to the VB app, it throws up the
following error:

Run-time error '1004': Unable to set the FormulaHidden property of the
Range class

What are the changes I would need to do in XML to resolve this issue.
Please note: I cannot use macros or that kind of stuff due to customer
restrictions.

 

[Tom Ogilvy]

I haven't played with XML, but faced with the same problem. I would create a
new workbook and alter a couple of cells to reflect my situation, then export
it as XML. then look to see what Excel has done.
What it did for me is it created a Style ID that defined the cell as
hidden, then for those cells that were hidden, it assigned that style ID.

<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Protection x:HideFormula="1"/>
</Style>
</Styles>

.. . .
<Row>
<Cell ss:Formula="=TRUNC(RAND()*100+1)"><Data
ss:Type="Number">87</Data></Cell>
<Cell ss:StyleID="s21" ss:Formula="=TRUNC(RAND()*100+1)"><Data
ss:Type="Number">6</Data></Cell>
</Row>

the first cell was not hidden, the second cell was hidden.

 

[ydnam]

Yes you are correct. In my problem statement, if you refer to point 1) I
have written that I have already done this in the code. The problem
comes in when the VB application tries to password protect the excel
sheet. I think there should be some XML tag which would solve the
problem. I have not come across one yet. Please let me know if you have
any idea on the same.

 

[Tom Ogilvy]

When I opened the xml file I had recorded, it worked fine for me.

No such error.

Sheet was protected. 3 cells had formulas hidden. 1 cell did not.