A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Update timeframe for linked data.



 
 
Thread Tools Display Modes
  #1  
Old September 15th, 2003, 09:18 AM
Bill Manville
external usenet poster
 
Posts: n/a
Default Update timeframe for linked data.

On other peoples' computers the links are to the copy of your master
worksheet as saved on disc, not to the sheet as you see it on your
screen.

To get other people to see the latest version you will need to save
your sheet and they will need to explicitly update their sheet using
Edit / Links / Update Link.

You could set up the "viewers" to periodically update their links using
an OnTime macro.

But Excel is really not a multi-user application.

If you stored the information in an Access table you could do better,
but the viewers would again need to requery the table at regular
intervals (e.g. using a Timer event on the form) to see new records as
they were added. The sorting could be done by the query on which the
form is based so you would not need to re-order the records in the
table.

An alternative would be to have the viewers set as an Excel sheet which
has a query to the master table (whether in an Excel file on disc or an
Access table), to retrieve the latest information. In Excel 2000 or
later the query could be set to run automatically at regular intervals.
In Excel 97 you would need to run an OnTime macro to do the update.

You would set it up via Data / Get External Data / New Database Query
(or similar - wording depends on Excel version). Again, the sorting
can be done by the query so you don't need to keep the original table
sorted.

Hope something in there helps!

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Ads
  #2  
Old September 16th, 2003, 05:30 AM
J Marro
external usenet poster
 
Posts: n/a
Default Update timeframe for linked data.

It appears that EditLinksUpdate Now cannot be executed on a Protected
sheet? When the sheet is Protected Update Now is greyed out. An OnTime macro
cannot be executed on a Protected sheet - I get an error saying as much.

If I do not intend for users are to modify the single sheet file, 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? That would
give the latest updated data to each user. The impact of un-sharing a file
seems to be the saving of user data (according to the pop-up warnings)but
they are not updating anything anyway.

If multiple users have the same file open at the same time are they
adversely affected by un-sharing the file?

"Bill Manville" wrote in message
...
On other peoples' computers the links are to the copy of your master
worksheet as saved on disc, not to the sheet as you see it on your
screen.

To get other people to see the latest version you will need to save
your sheet and they will need to explicitly update their sheet using
Edit / Links / Update Link.

You could set up the "viewers" to periodically update their links using
an OnTime macro.

But Excel is really not a multi-user application.

If you stored the information in an Access table you could do better,
but the viewers would again need to requery the table at regular
intervals (e.g. using a Timer event on the form) to see new records as
they were added. The sorting could be done by the query on which the
form is based so you would not need to re-order the records in the
table.

An alternative would be to have the viewers set as an Excel sheet which
has a query to the master table (whether in an Excel file on disc or an
Access table), to retrieve the latest information. In Excel 2000 or
later the query could be set to run automatically at regular intervals.
In Excel 97 you would need to run an OnTime macro to do the update.

You would set it up via Data / Get External Data / New Database Query
(or similar - wording depends on Excel version). Again, the sorting
can be done by the query so you don't need to keep the original table
sorted.

Hope something in there helps!

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



  #3  
Old September 16th, 2003, 08:02 AM
Bill Manville
external usenet poster
 
Posts: n/a
Default Update timeframe for linked data.

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

  #4  
Old September 17th, 2003, 07:20 AM
J Marro
external usenet poster
 
Posts: n/a
Default 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.


  #5  
Old September 17th, 2003, 10:55 AM
Bill Manville
external usenet poster
 
Posts: n/a
Default Update timeframe for linked data.

J Marro wrote:
Is the Auto_Close setting necessary in Excel 97?

Yes.

If you don't cancel the outstanding OnTime and you close the workbook but
don't close Excel, the workbook will be re-opened within the next minute to
run the macro!

I recall reading that 97
resets on close and does not need such routines to put settings back to
default.

That may be true for some settings - but not for most.

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.

If you copied and pasted the code there should be no message (it works OK for
me). Only thing might be if you already had an Auto_Close procedure. In
that case, combine the two.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:56 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.