Update timeframe for linked data.
"Bill Manville" wrote in message
...
J Marro wrote:
It appears that EditLinksUpdate Now cannot be executed on a Protected
sheet? When the sheet is Protected Update Now is greyed out.
Correct.
So you run a macro:
Sub UpdateMe()
ActiveSheet.Protect UserInterfaceOnly:=True
ActiveWorkbook.UpdateLink ActiveWorkbook.LinkSources(xlExcelLinks)(1)
End Sub
An OnTime macro
cannot be executed on a Protected sheet - I get an error saying as much.
The above can be executed as an OnTime macro.
Dim NextTime As Date
Sub Auto_Open()
NextTime = Now+TimeValue("00:01:00")
Application.OnTime NextTime, "UpdateMe"
End Sub
Sub Auto_Close()
Application.OnTime NextTime, "UpdateMe", schedule:=False
End Sub
Sub UpdateMe()
ActiveSheet.Protect UserInterfaceOnly:=True
ActiveWorkbook.UpdateLink ActiveWorkbook.LinkSources(xlExcelLinks)(1)
NextTime = Now+TimeValue("00:01:00")
Application.OnTime NextTime, "UpdateMe"
End Sub
is there
any reason I cannot run a macro that would Un-share the file, Unprotect
the
file, Run EditLinksUpdate Now, reprotect the file and re-share?
I didn't think that the viewer file was a shared workbook.
It doesn't need to be, and there is no value in it being so. Just make it
a
read-only workbook of which each user opens a copy.
You can't unshare a workbook if other users have it open, can you?
I do not encourage the use of shared workbooks.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
I think I about have this thing beat.
You can unshare a workbook while it is open - there are just a couple of
warnings about the impact on other users.
I don't know why I was thinking it needed to be shared - I guess because
that is the original route I was going and got it stuck in my head.
Is the Auto_Close setting necessary in Excel 97? I recall reading that 97
resets on close and does not need such routines to put settings back to
default. Plus I get an error message with that Sub in the workbook. I can't
recall the exact message but I'll note it tomorrow when I get back to work.
|