Hidden data in Excel, extracting data originally linked to an MDB file.

Question

I received an Excel file that references some data from a MDB file. When I open the file, I am only able to see summary based on dropdowns and pivot tables. The data from the MDB file appears to have been embedded in to the XLS file itself. If I look at the file in Notepad++ I can see the information I'm looking for (albeit mixed in with the binary data of the xls file.)

I'm curious if anyone knows of a way to extract this type embedded data out of an XSL file. The original MDB file is long gone.

Answer 1

Only thing I can think of: have you checked to see if there are any hidden worksheets in the file? Right-click any worksheet tab at the bottom of the workbook and select Unhide... From there, you can see if there is a hidden sheet containing the raw data.

Answer 2

If the dropdowns are really pivot page fields, then you probably have pivot tables based on external data. To check, select a cell that's in a pivot table. Open the VBE (Alt+F11). Go to the Immediate Window (Ctl+G) and type

?ActiveCell.PivotTable.PivotCache.CommandText
?ActiveCell.PivotTable.PivotCache.Connection

and that will give you the information about where the data is. The data you see in Notepad is the data stored in the pivot cache. If you can't get to the mdb, you won't be able to refresh, but the previously fetched data will still be in the cache.

Answer 3

Remove all filters and double click in a cell containing a Grand Total figure: Excel will drill in the data in the PivotCache and will open a new worksheet with all the rows summed up in that figure.