Is It Possible To Run A Code At Set Intervals?

[charllie]

Hi Folks,

I have two workbooks which will be open constantly on two seperate PCs.

Workbook1 will be pasting information at regular intervals to an "Archive" workbook located in G:Drive. This will be done by an operator as and when required throughout a shift.

Workbook2 will be accessing this "Archive" workbook at regular intervals and copying the information into workbook2. At the moment it does this transfer by using a "UpDate" commandbutton located on a Worksheet in Workbook2.
The code for this "UpDate" commandbutton is:

Private Sub UpDateButton_Click()
    Application.ScreenUpdating = False
      '*****************************************************************************
        'This code UPDATES this Workbook information in Shift Manager Screen from
        'the Transfers Sheet located in workbook "HandPack Archive.xls" further
        'located in G:drive
        Dim TRA, SMS As Worksheet
        
        Set SMS = Sheets("Shift Manager Screen") 'This refers to worksheet in this workbook
    
        Workbooks.Open Filename:="G:\Cwmbran-new\Warehouse\lean manu\" & _
                    "HandPack Archive.xls" 'This opens the other workbook
    
        Set TRA = Sheets("Transfers")  'This refers to worksheet in other workbook
        
        With TRA
            SMS.Range("G8") = TRA.Range("B7")   'Completed Jobs
            SMS.Range("D8") = TRA.Range("B9")  'Total Discs Packed
            SMS.Range("F9") = TRA.Range("B5")  ' Current Shift
            SMS.Range("E10") = TRA.Range("B11") 'Jobs In Quarantine
        End With
        
        ActiveWorkbook.Save  'Saves the other workbook
        ActiveWorkbook.Close 'Closes the other workbook
      '*****************************************************************************
     Application.ScreenUpdating = True
End Sub

The above code works fine and although it might be a long winded way around getting information from one sheet to another it is quite stable. By that i mean if we have a powercut or pc crashes we will only loose minimal information.

What i want to know is if it is possible to:

• 1. Activate the "UpDate" commandbutton/code automatically at set intervals instead of an operator doing it manually. This way i could remove or make the button invisible.
  
  2. Activate the "UpDate" commandbutton automatically when the workbook is first opened. This is just as a precaution in case pc crashes etc. So when re-opened it is immediatly back uptodate.

If i do manage to do this then it is worth noting that there is also a workbook3 and 4 which i will also use the code in. The "Archive" workbook is always closed and is only opened when information is either placed into it or copied out of it.

I would really be gratefull for any help or guidance with a code for this problem.

Thanks

 

[Fat Cat]

Hi Mike,

Only spotted this post after sending you the test sheet. The archive sheet sounds like it would work and gets round the shared workbook problem.

Maybe something like this based on andshep's idea;

In MODULE1

Public when As Date
Sub TimedRun()

    Application.OnTime when, "CheckArchive"
    
End Sub
Sub CheckArchive()

   Application.ScreenUpdating = False
      '*****************************************************************************
        'This code UPDATES this Workbook information in Shift Manager Screen from
        'the Transfers Sheet located in workbook "HandPack Archive.xls" further
        'located in G:drive
        Dim TRA, SMS As Worksheet
        
        Set SMS = Sheets("Shift Manager Screen") 'This refers to worksheet in this workbook
    
        Workbooks.Open Filename:="G:\Cwmbran-new\Warehouse\lean manu\" & _
                    "HandPack Archive.xls" 'This opens the other workbook
    
        Set TRA = Sheets("Transfers")  'This refers to worksheet in other workbook
        
        With TRA
            SMS.Range("G8") = TRA.Range("B7")   'Completed Jobs
            SMS.Range("D8") = TRA.Range("B9")  'Total Discs Packed
            SMS.Range("F9") = TRA.Range("B5")  ' Current Shift
            SMS.Range("E10") = TRA.Range("B11") 'Jobs In Quarantine
        End With
        
        ActiveWorkbook.Save  'Saves the other workbook
        ActiveWorkbook.Close 'Closes the other workbook
      '*****************************************************************************
     Application.ScreenUpdating = True

    when = Now() + TimeValue("00:00:30")    'setup the time to the next update, make it longer if 30 secs is too quick
    Call TimedRun                           'recall the TimedRun to shedule the next update

End Sub

and then in THISWORKBOOK code;

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
Application.OnTime when, "CheckArchive", , False      'turn off the next sheduled update
End Sub

Sub Workbook_Open()                         'do this when we first open the workbook
    
    Call CheckArchive                       'check the archive and start the shedule going
End Sub

 

[BrianB]

There is some basic code here :-

http://www.mrexcel.com/board2/viewtopic.php?t=145246&highlight=

 

[charllie]

Hi Guys,

Thanks for your replies.

I had already inputted Marks code before i saw your post Brian. That worked perfectly so i will go with that.

Thank you both for your help, much appreciated.