Replace/update tables in data model without upsetting relationships

[PietC]

Hello everyone. I’m creating a simple management tool for a number of teams in the form of a standalone Excel 2013 workbook for each team leader, working on a standalone PC. The tool comprises three tables, each on a separate worksheet. These tables are added to the data model and used as inputs to a pivot table on the fourth sheet. The pivot table is little more than a means of achieving a quasi-relational database that joins fields together.
The three data tables change every month, being sent out as a workbook containing the three replacement sheets, which always using the same worksheet names as the previous issue.
I must assume that the team leaders have little or no Excel expertise and I’m seeking the simplest way of replacing the tables with the updated versions in such a way that the pivot table doesn’t get wrecked.
I suspect that copy/pasting the new data over the old would achieve the desired result but it seems clumsy. Deleting the old tables and inserting their replacements ruins the relationships.
Am I missing something or is my basic approach wrong?
Many thanks in advance. Piet

 

[ParamRay]

You could store the data in external files (such as a 3 text files) which are then imported into your data model.

Then, when you receive the new data each month, you simply paste it over the contents of your text files and refresh the data model.

This should work as long as the structure of your external files remains consistent (i.e. the same columns each month).

 

[PietC]

gpeacock - thanks for responding, though I'm not sure that this will help me much. The updates are supplied as Excel workbooks and I'm really looking for the simplest way for the (non-IT literate) team leaders to replace the previous sheets with the new ones, without wrecking everything. Thanks anyway.
As ever, Piet

 

[npvrader]

I only see 2 options :

1). Create a copy paste macro to update the tool
2). Write a macro that creates the pivot table and send it embedded with each update

Seems to me that option 1 would be easier

 

[PietC]

npvrader - thanks for your suggstions and I fear I'm going to have to go with option 1. It seems a shame that it's so difficult but I really don't want my users to have to deal with external data or MSQuery!

Thanks again. Piet