Excel doc saved as an HTML file - can't unhide worksheet

[Glenn Mulno]

Hi,

Sorry - this is a repost from microsoft.public.excel.programming. I posted
there but maybe that was the wrong group for this question.

Bottom line on top: I created an Excel file and saved it as HTML - and that
file had a hidden worksheet. Upon reopening the file - I can no longer find
the hidden worksheet.

More detail:

I am in the process of creating a dashboard for our group that is Excel
based, but is saved as an HMTL file for ease of everyone viewing.

In this early stage of development - I created just 3 tabs of reports and 1
tab of calculations. All the data is external, so I needed a calculations
tab that made final calculations on certain pieces of data. However - when
I output the results as an HTML file - I did not want the calculations tab
to show. So - I hid the tab just before saving the file as HTML.

While I was tweaking the initial version I hid and unhid the tab several
times with no problem - but the Excel window was still open. I since closed
the file and Excel and now a week later I wanted to make a few more tweaks
to the file. upon reopening the dashboard file - I notices all my graphs
were showing all 0's. I then went to unhide the Calculations tab so I could
investigate what happened and I am not able to unhide the tab any more! The
unhide options in various menus are just grayed out!

I even tried this command in the immediate window but I just get an error
Run time error "9". Subscript out of range.
Worksheets("Calculations").Visible = True

So - that sinking feeling in my gut is telling me that somehow this tab is
now gone and my work from last week is gone. But I was hoping that I could
find a miracle from someone here.

I am using Office/Excel 2007. The filename I am working with is
"TS_Dashboard.HTML".


Thanks in advance for any help!

 

[AltaEgo]

Never had the problem nor attempted to solve it before but since you haven't
had a response...

Possibly you sheet property is set xlVeryHidden. If so, this should unhide
it (plus any other hidden sheets"

Sub uhAllSheets()

For Each s In Sheets
s.Visible = True
Next s
End Sub

Make sure workbook protection is off.

 

[Glenn Mulno]

Thanks Steve. Unfortunately that did not work. I suspect it is gone for
good and that this is an undocumented feature from MS.

In the help it says:
Web Page and Single File Web Page
These Web Page file formats (.htm, .html), Single File Web Page file formats
(.mht, .mhtml) can be used for exporting Excel data. In Office Excel 2007,
worksheet features (such as formulas, charts, PivotTables, and Visual Basic
for Application (VBA) projects) are no longer supported in these file
formats, and they will be lost when you open a file in this file format
again in Excel.

I suspect hidden tabs is another you can probably add to that list. I just
repeated the process again (on a text file) to make sure it was not just
that I deleted it. Its just gone. A weeks worth of work out the door
thanks to Microsoft.