Application.OnTime TimeToRun, "XMLRefersh"
' or you can give specific workbook and worksheet identification
Private Sub Workbook_Open()
'Call Refresh Data Subroutine when opening file
I am not an expert in VBA , here what I will do
1. Refreshing when opening an Excel file , That can be done by
Updating MSexcel as soon as server gets update , doesn't make sense as data will be available for view only after file gets open , however if your macro is been used for live monitoring and kept open 24 hours then instead of sending a event from server and catching in VBA , I suggest implement polling every day/hour/minute/second ( call refresh at poll interval) depending upon your refresh requirement criticality.
You can use worksheet activate event handler if you have multiple worksheet in a file.