Updating source data for pivot table
Here's a link using wscript which should be able to incorporate: Just too bad it can't return some kind of error code when complete.Since the pivot table is updated from a SQL database thats running lots of other clients data as well if a large query kicked off right before the picot table update it could take 10-15 minutes before the pivot table is ready.If the authentication settings in the workbook are configured to Use the authenticated user’s account or None, the credentials stored in the Secure Store target application ID are used for data refresh.For more information, see the Additional Authentication Considerations section in this topic.Interactive, or manual data refresh in Share Point Server 2013 Excel Services can refresh data models with data from the original data source.Interactive data refresh is available after you configure an Excel Services application by registering an Analysis Services server, running in Share Point mode.(Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.) This tutorial covers how to use Oracle Business Intelligence Answers to create queries, format views, charts, and add user interactivity and dynamic content to enhance the user experience.
The service manages requests to read data in and out of the service application database and trigger data refresh with Excel Services.
How can I get it to wait until it's finished refreshing before it continues on to saving and quiting? You might need to put a wait in the loop, as you're running from VBScript. Currently set to 5 seconds - if this is a batch job, you might make it 30-60 seconds. But as soon as there is a new pivot table with a new connection string the vb script opens the report, saves and closes without refreshing.
Only thing different about this Excel file from other files that still work fine is that this excel file has multiple pivot tables linked to different queries. A bit of an art, you'll have to increase the pause Time to something that is satisfactory. When the workbook has more than one pivot table sources, the Refresh All can't finish before it moves on to the save/quit commands. It also works fine if all the pivot tables are linked to the same source (a SQL view) even if it take 5 minutes and there are 30 pivot tables.
Thanks so much, this is exactly what I needed: to stop spending hours trying to figure out why it didn't work!
Place the cursor over this icon to load and view all the screenshots for this tutorial.
For more information, see Manage Excel Services data model settings (Share Point Server 2013) (