Results 1 to 13 of 13
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Auto refresh copies of a workbook! (Excel2000>)

    Hi all,

    I have a client that is needing a special request. I need to find out if there is a way of doing this; if anyone has ideas or code or strategies to get excel to do the following:

    PS: The client cannot use access due to circumstances out of their control!

    There are 3 persons in 3 seperate locations in the same building. One person works on a workbook and makes changes to the values in a sheet. Persons 2 and 3 need to view the workbook and be able to see the latest changes. The workbook should automatically refresh within a 3min period, sothat the lastest data is displayed.

    We have come to the point of creating 3 seperate books. The original is the one that person 1 works in. The other two are seperate books that have links to the original. As data is changed in the source the other two books must update automatically. (The person should not have to go EDIT | LINKS | UPDATE!!!)
    Is there a way to build a macro that can automatically update the links after every 3 minutes.??

    Hope this is clear!
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto refresh copies of a workbook! (Excel2000>)

    Sure:

    In a normal module:

    Dim mdNextTime as Double

    Sub ScheduleNext()
    mdNextTime=Now+timevalue("00:03:00")
    Application.Ontime mdNextTime,"UpdateLinks"
    End Sub

    Sub UpdateLinks()
    ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources
    ScheduleNext
    End Sub

    Sub StopThis()
    Application.Ontime mdNextTime,"UpdateLinks", Schedule:=False
    End Sub

    In the thisworkbook module:

    Private Sub Workbook_Open()
    ScheduleNext
    End Sub

    Private Sub Workbook_BeforeClose(Cancel as Boolean)
    StopThis
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto refresh copies of a workbook! (Excel2000>)

    Thanx Pieterse,

    I'll have the client test this code in their workbooks to see if it suits their needs!
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto refresh copies of a workbook! (Excel2000>)

    Hi Pieterse,

    The client has come back to me and said that your code is working great, except for a little bug when the workbook closes. They provided me with the macro which seems to have been changed slightly? With you VBA expertise, could you preview the code and also see if you can spot a problem with regards to the error on closing the WBook.
    Regards,
    Rudi

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto refresh copies of a workbook! (Excel2000>)

    They should change their last sub:

    Application.OnTime EarliestTime:=UpdateTime, Procedure:="UpdateLinks", Schedule:=False

    Application.OnTime EarliestTime:=SaveTime, Procedure:="UpdateLinks", Schedule:=False

    I forgot to type all arguments.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto refresh copies of a workbook! (Excel2000>)

    Cheers,

    I'll have them try it out!
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto refresh copies of a workbook! (Excel2000>)

    Hi Pieterse, could you help me on one more request?

    From a client :
    One last request. It works perfectly but I would like to update the file very 20 seconds and save every 1 minute. This means I put a My_save_sub in the scheduleNext but now it saves every 20 seconds as well. And the save code I included never gets out of the loop
    Regards,
    Rudi

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto refresh copies of a workbook! (Excel2000>)

    Untested:

    Dim mdNextTime As Double
    Dim mdSavetime As Double

    Sub ScheduleNextUpdate()
    mdNextTime = Now + TimeValue("00:00:20")
    Application.OnTime mdNextTime, "UpdateLinks"
    End Sub

    Sub ScheduleNextSave()
    mdSaveTime = Now + TimeValue("00:01:00")
    Application.OnTime mdSaveTime, "My_Save_Sub"
    End Sub

    Sub UpdateLinks()
    ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources
    ScheduleNextUpdate
    End Sub

    Sub StopThis()
    Application.OnTime EarliestTime:=mdNextTime, Procedure:="UpdateLinks", Schedule:=False
    Application.OnTime EarliestTime:=mdSaveTime, Procedure:="My_Save_Sub", Schedule:=False
    End Sub

    Sub My_Save_Sub()
    '.....
    ScheduleNextSave
    End Sub

    To start the process, you'll have to call both ScheduleNext's.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto refresh copies of a workbook! (Excel2000>)

    Thanx, i'll give it a go!

    Could you explain what you mean by saying, >>>"To start the process, you'll have to call both ScheduleNext's."?
    Regards,
    Rudi

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto refresh copies of a workbook! (Excel2000>)

    Jan Karel's reply will no doubt do what was requested, but you (or your users) should ask yourself if it is really desirable to update links every 20 seconds and to save every minute. If the workbook is large, the overhead of doing this is significant.

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto refresh copies of a workbook! (Excel2000>)

    I have never asked the client what is being saved, and how much data it is!! I will take your advice and inform them of your warning. Thanx!
    Regards,
    Rudi

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto refresh copies of a workbook! (Excel2000>)

    If you look at my code you'l see I created two schedule subs:

    ScheduleNextSave
    and
    ScheduleNextUpdate

    Both subs need to be called at workbook_open to start the timed saves and updates.

    Hans is right though, if the file is large, it'll consume a lot of bandwidth on the network.

    Another thing to consider is that you're using Excel at what it is very bad at: sharing data amongst multiple users. Please warn your client that Excel is very unreliable with this and he should seriously consider moving to e.g. Access for such tasks (multi-user editing)!!!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto refresh copies of a workbook! (Excel2000>)

    Many thanx Pieterse. i'm grateful for your help!
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •