Excel 2010: Replace a table by an external connection without impacting other sheets

Question

I have a big workbook with a lot of data. In one sheet I have a table. This table is used for many Pivot tables used in other sheets, which are also used in other sheets.

The data for the table was copied manually regularly. I now have a external connection to import automatically the data but I'd like to avoid impacting other sheets.

How can I use my external connection without losing my table?

Thank you for your help

Answer 1

This might be a helpful workaround. I have successfully done this but it may require a bit of rework and not be the exact answer you expect. (I am using Excel 2010).

The reason for a workaround is I assume this is for a regular table (not a pivot table) as the source table. Unfortunately, I don't believe the switching connection options work for regular tables in Excel 2010.

1. Recreate a new table just below the other in the same worksheet by going to the Data tab > Get External Data > Existing Connections

2. Select your data connection (assuming it has been created) then select how you want to view the data. ie: Table

3. Place in your existing worksheet. Use your old table as a reference to reconnect all the connections to your other sheets, then delete the old table.

At this point, I find it easier to rebuild/copy those existing links to the new table with external data.

The good news: If your source table is a pivot table or pivot chart with an existing connection, you can easily change the table to have an external data connection (and switch between data connections).

1. For a pivot table with an existing connection you wish to change, select a cell in the table. In the ribbon above in 'PivotTable Tools' > Options tab > Change Data Source, you will be able to select this and reconnect to another source under 'Choose connection'.

2. For a pivot table with static data to introduce a new connection, I would suggest a similar workaround above.

I hope this works for you.

Answer 2

I'm going through this too. I've done some testing, and this is the approach I am going to take.

1. Save copy of file (just in case!)
2. Create a new sheet, and create link to your database, and have it entered as a table on this new sheet
3. Make sure your old table (manually entered) matches the same layout as the table with the data you are connecting to (same column order and column names)
4. For each pivot table, change the source to the new table (it should keep all of your setup the same if the columns are labeled the same)
5. Do a find and replace to change all formula referencing to the new table name.
6. Change any code in VBA that references the old tables

This should result in the least issues while making the changes. Just don't forget the first step!