Get source data behind a pivot table in a spreadsheet automatically and dynamically.
Does anyone know how to get the source data behind a pivot table from a spreadsheet? The company I work for relies on 3rd party spreadsheets as input data. This data is in a pivot table format. And yes SSIS can unpivot spreadsheets but only where there are no additional filters, once and additional filter level is introduced it seems to get stuck. I have a VBA macro which can do it manually but the idea is to automate the whole process. I have written a
VB.net script but the drill down into the data yields funny results? If anyone can point me to a "simple" way of doing this it would be greatly appreciated?