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 assigned, 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
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
- Set how often the task should be run and click on
- Set when the task should start running and click
- Select the option 'Start a program' and click on
- 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 OK.
- 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.