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" ss:Protected="1"/>
2) In the worksheet tag we need to put
<Worksheet ss:Name="Sheet1" ss:Protected="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.

--
Regards,
Tom Ogilvy


"ydnam" wrote:

>
> 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" ss:Protected="1"/>
> 2) In the worksheet tag we need to put
> <Worksheet ss:Name="Sheet1" ss:Protected="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.
>
>
> --
> ydnam
> ------------------------------------------------------------------------
> ydnam's Profile: http://www.excelforum.com/member.php...o&userid=33554
> View this thread: http://www.excelforum.com/showthread...hreadid=533363
>
>

[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.


[QUOTE=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.

--
Regards,
Tom Ogilvy

[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.

--
Regards,
Tom Ogilvy


"ydnam" wrote:

>
> 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 Wrote:
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
>
>
> --
> ydnam
> ------------------------------------------------------------------------
> ydnam's Profile: http://www.excelforum.com/member.php...o&userid=33554
> View this thread: http://www.excelforum.com/showthread...hreadid=533363
>
>