Use Data Explorer to Hack into Very Hidden Sheets

This weekend is Easter; a time when ungrateful children run around like weasels trying to find hidden eggs. In the spirit of finding hidden objects, I’ll show you a way to find hidden data.

.

While playing with Data Explorer, I noticed that could see data I shouldn’t be able to see; hidden sheets in Excel workbooks for example.

Here’s what I mean.

.

Let’s say I have a workbook with a sheet containing top secret data.

.

I can go to the Visual Basic Editor (by pressing Alt+F11) and set the Visible property of the sheet to be xlSheetVeryHidden.

.

This property not only hides my top secret sheet, but prevents anyone from manually unhiding it using the user interface.

.

For extra protection, I also set a Protect Workbook password.

This should stop anyone from going into the VBE and resetting the visible property of my top secret sheet.

.

Ok I feel comfortable that my suspect data is protected. I save and close the file.

.

.

Now I open a new workbook, go to the Data Explorer tab, and then choose to import Excel data.

.

After picking my target Excel file, Data Explorer shows me all the data sources in the file.

And…

Whoops…look who’s taking a peak under the kimono!

.

Apparently, Data Explorer ignores the visible property of sheets and shows you everything it reads. This is pretty handy. I’ll definately be using this to get into some of the protected workbooks I deal with.

Incidentally, hidden columns and rows are also made visible through Data Explorer.

.

One last note:

I noticed that encrypted workbooks (workbooks that require a password to even open) fail to open in Data Explorer. As far as I can tell, there is no way to pass credentials in Data Explorer to open an encrypted Excel workbook.

UPDATE: Paul correctly points out that this trick does not work for xls files. Thanks for testing that Paul. This is obviously disappointing, but you can’t win them all.

Well…that’s it.

Happy hacking; and Happy Easter.

7 thoughts on “Use Data Explorer to Hack into Very Hidden Sheets

  1. David Hager

    Nice hack!

    You can also use this technique to detect the presence of VeryHidden macrosheets

  2. sam

    There are problems with having a Data Explorer.
    If you open a file containing macros and close it – It ends up creating a ghost instance of the the Project in the VBE

    If you have a parallel install of 2003 – If you open it – Multiple errors will occur (for every add-in installed) while opening Excel

    Certain tables on a Web page which are visible to the “Web Query” feature of Excel are not visible in the “Import from Web ” feature of the Data Explorer…..Try linking a Cricket score card from Cricinfo.com and DE shows no tables

  3. datapig

    Sam: Hmmm… I’m not seeing the behavior you’re describing. I will say that I’m testing on a computer that only has Excel 2010 installed. I’ll try this on my Excel 2013 computer as well.

  4. AlexJ

    @sam
    I often get ghost instances of files in the VBE, not even using data Explorer. I believe it is sime kind of Excel 2010 bug. (No Excel 2003 installed, no .xls files involved)

  5. Doug Glancy

    His curiosity piqued by the peak beneath the kimono, Mr. Pig peeked.

    In the distance Mt. Fuji rumbled as mountaineers packed their picks, in states ranging from pique to panic.

Leave a Reply

Your email address will not be published. Required fields are marked *