Log In
Register
Home
July 7, 2016
Offline
I created a Pivot Table with an external connection to another Excel file and it's Excel table. It works just the same as a normal Pivot Table with an Excel table in the same file. However, I'm finding that it's unstable. When the source file is not open, the Pivot Table won't refresh. If the source file is not open and the Pivot Table is set to automatically refresh, it may cause Excel to crash.
I have the source table in a separate file because it's a million rows and very large. The Pivot summarizes this to a few thousand rows, greatly improving performance and response time. Is there something else I should try? Or perhaps another approach?
Paul
See Answer
July 16, 2010
Offline
Hi Paul,
Use Power Query to get the data from the external file and load it to the Pivot Table as a connection only. Power Query is the opposite in that it will require the file to be closed in order to refresh the data connection.
Mynda
Answers Post
July 7, 2016
Offline
Thanks for this.
I did run into some problems with the source file Excel table, however. I opened a new file and loaded the data with an external connection. Then I created a Pivot Table, but one of the fields in the source table relies on links to another file. The Pivot Table loaded it as zero, the default value for that field. I figured maybe the links can't be updated when using that field in a Pivot Table with external connection. Another odd thing is that the source file has many Excel tables, yet creating the Pivot with External Connection Only showed the worksheet tabs when preparing for import, not the table names.
As a test, I copied and pasted the source values into a new file and created a table from it. I imported it into another new file using Connection Only, and was able to successfully create the Pivot. The table name displayed in the import wizard. As a further test, I added a table in a new tab in the source file, and there again, no problem.
It doesn't seem like the original issue is caused by either links or other tables in the source file, but maybe there's a quantity limit. I looked on your site for rules governing the import of data into a file using Connection Only, but I didn't find one. Are there rules or guidelines?
Paul
July 7, 2016
Offline
I wanted to follow up on this for the benefit of others.
It turns out that the issue was caused by using the .xlsb (binary) file format. I use this format for large files because it both reduces file size considerably and allows VBA. Creating a Pivot Table with external connection requires that the source (if Excel) file be in standard .xlsx format. I don't know why that should matter, but it does.
Paul
VIP
Trusted Members
December 7, 2016
Offline
Hi Paul,
Thanks for the update!
July 7, 2016
Offline
There is an unexpected complication: I have many Pivot Tables in my Excel model, but only two tables in source file from which they all derive. I created three Pivot Tables successfully, but creating the fourth one did not allow the file to be saved ("Errors were detected"). I created a new connection for each Pivot Table. Is that a problem? Also, when using Get Data>Load To>Connection Only, I kept getting messages to the effect, "There is a problem with the Data model. Please try restarting Excel." When I ignored it, it created the connection anyway.
There's a problem in here somewhere. Does anyone have any ideas about what went wrong?
Paul
July 7, 2016
Offline
I tried to duplicate this situation in a different blank workbook. I created a bunch of Pivot Tables with the same external connections to the same source data as before--and had no issues at all. If the blank workbook has no problems creating Pivot Tables from external connections, the problem must be the Excel model into which I'm attempting to create these same Pivot Tables.
Any ideas about how to fix this?
July 7, 2016
Offline
After much experimentation, I found a solution, and I'm posting this to help others.
When you have multiple Pivot Tables with external connections in a single file, each Pivot Table must have its own connection. So, if you have a Pivot Table that relies on the same external table for its data as another Pivot Table, each Pivot Table must use its own connection. You might end up with many connections to the same external data source, but that's the way it is.
I believe the same rule applies if you are creating a table or any other report from the external data. No sharing is allowed or you'll run into problems.
1 Guest(s)
