Advice Sought: Best way to create a pivot using an external workbook as the data source?

Hi,

I've created some automated daily data extracts from our Cognos reporting system and I'd appreciate some advice on the best way to use them as a basis for pivot reports in other workbooks.

Background:

I've created some scheduled reports in Cognos so that every morning (7am) I am emailed the latest data extracts. Then when I come into the office and open Outlook I have a macro that identifies these data extract emails and copies the attachments to a set folder on the network. Now I would like to create some pivot reports in separate workbooks that feed from these data extract spreadsheets so that people can access their relevant information. My problem is that I'm not sure on what the best approach is to achieve this? I was hoping to simply have a dynamic range that referenced the external workbook so that when the user opened their report the pivot would update (or could be refreshed) and the user would have access to the latest figures. Unless I'm mistaken it's not possible to use dynamic ranges as the data source with external workbooks? I had tried using the OFFSET formula.

What options does that leave me? Using a large defined static range that will always encompass the potential data area? Create a macro enabled workbook that can import/copy across the latest data from the external workbook and then use a dynamic range to create the pivot?

I'm keen to understand my potential options and more importantly what is considered best practice in this instance?

Thanks in advance,

Snook

Presumably that would involve me having to open all the data extract spreadsheets every morning and converting the data to a table? Or could that process be automated?