' Monitors a spreadsheet file. Reopens it when it changes.
' Monitored file must be on a mapped drive (network paths don't work)
' Best run with cscript. If you've double-clicked look for wscript in Task Manager and kill it
' Rob H 12/1/06
'
' Code cobbled from http://www.microsoft.com/technet/scriptcenter/scripts/storage/files/stfivb23.mspx
' and http://www.microsoft.com/technet/scriptcenter/scripts/office/excel/ofexvb08.mspx

' Following path must have \ and ' escaped
 strFileToMonitor = "p:\\Childrens\\ETV CHILDREN\'S\\CBBC\\Blue Peter Four Nations Challenge\\Technical\\blue peter burndown.xls"
 strFileToOpen = "p:\Childrens\ETV CHILDREN'S\CBBC\Blue Peter Four Nations Challenge\Technical\blue peter burndown.xls"
 strWorkbookName = "blue peter burndown.xls" ' Assumes the workbook's only open once at a time
 strChartName = "Burndown" ' Our desired default sheet

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & _
        strComputer & "\root\cimv2")

Set colMonitoredEvents = objWMIService.ExecNotificationQuery _
    ("SELECT * FROM __InstanceModificationEvent WITHIN 10 WHERE " _
        & "TargetInstance ISA 'CIM_DataFile' and " _
            & "TargetInstance.Name='" & strFileToMonitor & "'")

Wscript.Echo "Monitoring " & strFileToMonitor
Do
	Set objLatestEvent = colMonitoredEvents.NextEvent

	Wscript.Echo "File: " & objLatestEvent.TargetInstance.Name & " changed"
	Wscript.Echo "Modified now: " & objLatestEvent.TargetInstance.LastModified
	Wscript.Echo "Modified before: " & objLatestEvent.PreviousInstance.LastModified

	' In theory we don't need the following conditional, but we find the modified event
	' fires more often than it should
	If objLatestEvent.TargetInstance.LastModified <> objLatestEvent.PreviousInstance.LastModified Then
		openFile
	Else
		Wscript.Echo "Ignoring change"		
	End If
Loop

Sub openFile 
	' Following opens Excel if it already exists, or uses existing window
	' May cause dialog prompts for macros and readonly if it's newly opened
	Set objExcelInstance = GetObject(strFileToOpen)
	Set objExcel = objExcelInstance.Application
	' Save existing window attributes
	Set objWindow = objExcel.Windows(strWorkbookName)
	With objWindow
		coordTop = .Top
		coordLeft = .Left
		coordWidth = .Width
		coordHeight = .Height
	End With
	objExcel.Workbooks(strWorkbookName).Close(False) ' Don't save changes

	' Create the new window
	objExcel.Workbooks.Open strFileToOpen, , True ' Read only
	Set objWindow = objExcel.Windows(strWorkbookName)
	objWindow.Visible = False
	With objWindow
		.Top = coordTop
		.Left = coordLeft
		.Width = coordWidth
		.Height = coordHeight
	End With

	objWindow.Visible = True
	objExcel.Workbooks(strWorkbookName).Charts(strChartName).Activate
	objExcel.Visible = True	' When initially opened, Excel is invisible
End Sub