Schedule charts in Excel

Every now and then I need to get a spreadsheet opened data refreshed and
then published onto a sharepoint site. Once you have the data set-up in the
spreadsheet. Add the below code either to Private Sub
Workbook_Open()
or in its own subroutine.:

Private Sub Workbook_Open()

‘refresh all data in the
workbook
ActiveWorkbook.RefreshAll

‘have the below sheet be the
first item shown
Sheets(“Code defect trend – all teams”).Select

‘stop any popups being displayed
Application.DisplayAlerts =
False

‘Save the workbook
ActiveWorkbook.Save

‘Publish the workbook to a particular sharepoint/web server

ActiveWorkbook.SaveAs Filename:= _
http://sharepoint/site/Bug_Trend.htm
_
, FileFormat:=xlHtml, ReadOnlyRecommended:=False,
CreateBackup:=False

‘Quite the application

Application.Quit

End Sub

If you do set the macro to run at start and want to edit the file
press the shift key when opening the file, this will stop the macro from
running.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s