Script to Update Excel Chart in SharePoint
A SharePoint Online tutorial by Peter Kalmström
previous demo Peter Kalmström, CEO and Systems Designer of kalmstrom.com
Business Solutions, added an Excel chart to a SharePoint list landing page.
The chart in Peter's example shows number of worked hours per assinged, and
in an earlier demo he also explained
how to create that chart.
The Excel chart Peter has added to SharePoint will not be updated automatically
when list data is changed. Instead you have to open Excel and refresh the
chart there. In the demo below Peter shows how to add a scheduled task with
a script that updates the Excel chart automatically.
VBS Script that updates an Excel chart on a SharePoint page
This script updates the Excel chart:
Set xl = CreateObject("Excel.Application")
set wb = xl.WorkBooks.open("https://kalmstromdemo2.sharepoint.com/
xl.DisplayAlerts = False
In the demo below Peter shows how to get the correct URL to use in the script.
Save the password in your browser, so that the script can work automatically.
Create a scheduled task that runs the refresh script
Note that if you have Excel as part of an Office 365 ProPlus license, you
have to log in to Excel once a month to make sure the subscription is updated.
This is not necessary with a regular Office license.
- Open the Task Scheduler.
- Create a basic task, give it a name and click on Next.
- Set how often the task should be run and click on Next.
- Set when the task should start running and click on Next.
- Select the option 'Start a program' and click on Next.
- Browse to the VBS file and click on Next.
- Check the box for ‘Open the Properties Dialog for this task when I click
Finish’ and click on Finish.
- Under the Triggers tab, select the Daily task and click in the Edit...
button. Set the task to be repeated with the interval you prefer and click
- Click OK to the Properties Dialog.
- Select the new task and enable the All Tasks history if you want to have
a log over what is happening.
- Right click on the new task and select Run.