Force Remote Refresh on Excel Pivot Table for Connection Type: Excel File

Question

our company aims to build a dashboard via a Pivot Table while putting the data behind on a Sharepoint that could be periodically replaced by the Superusers. Hence the idea is that the EndUser gets a fron- end Excel with the nice graphics and then hits "Refresh All" periodically which would then fetch the latest data from the Sharepoint.

Hence I created my Pivot Table (not Power Pivot), pointed it to the remote Excel file and hence have defined:

Connection Type: Excel File

Connection File: https://oursharepoint.internal.com/90 Internal/Test/mydatafile.xlsx

Connection String:Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\USERNAME\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO\CAEE1002.xlsx;Mode=Read;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False

Command Type: Table
Command Text: Master_01__Table_from_Export_

That works so far and the data show as expected. When I now replace the file on the Sharepoint with one that contains more data for the new months, I can hit "Refresh" and "Refresh all" as many times as i want... Excel is not pulling the latest data.

I have the impression that Excel keeps reading the data from here:

C:\Users\USERNAME\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO\CAEE1002.xlsx

When i manually open that local file, then the new data is not inside.

Do you have any idea where the problem lies here? Is my entire setup wrong or is there a way to force Excel to read the latest data from the sharepoint instead of relying on the local file?

Thanks for your assistance! Best regards, Marvin

XinXed XinXed · Accepted Answer · 2015-08-20T10:33:42

Of course you manage to go ahead yourself AFTER 35hs of prior research but only about 10 mins after you post on a support forum.

My solution at the moment would be to rely on MS Query via Data -> From Other Data Sources -> MS Query --> Excel Files --> Connect to Sharepoint via mapped Network drive to http:// target.

Then: Edit the connection string under connection properties:

From:

DSN=Excel Files;DBQ=X:\90 Internal\Test\binary\myDatafile.xlsb ;DefaultDir=X:\90 Internal\Test\binary;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

To:

DSN=Excel Files;DBQ=//oursharepoint.internal.com/sites/hr/rsc/shared documents/90 internal/test/binary/myDatafile.xlsb;DefaultDir=//oursharepoint.internal.com/sites/hr/rsc/shared documents/90 internal/test/binary;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

and Refresh works!

Downside: MS Query is a little slow... :-/

edit - one also needs to udpate the SQL Statement and make sure to use "\" and "[":

SELECT FILENAME.fieldname FROM [\oursharepoint.internal.com\sites\HR\RSC\Shared Documents\90 Internal\Test\binary\myDatafile.xlsb].FILENAME FILENAME

Force Remote Refresh on Excel Pivot Table for Connection Type: Excel File

1 Answers