Script to Update Excel Chart in SharePoint

A SharePoint Online tutorial by Peter Kalmström

In the 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 page

This script updates the Excel chart:

								Set xl = CreateObject("Excel.Application")

								set wb = xl.WorkBooks.open("https://kalmstromdemo2.sharepoint.com/

								sites/IT/SharedDocuments/ITTicketsSummary.xlsx")

								xl.DisplayAlerts = False

								WScript.Sleep 1000

								wb.RefreshAll

								wb.Save

								wb.Close

								xl.Quit

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

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 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.

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.