Is It Possible To Run A Code At Set Intervals?

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
983
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:
Code:
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fat Cat

Active Member
Joined
Nov 5, 2004
Messages
336
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
Code:
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;
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
 

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
983
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,504
Messages
5,832,111
Members
430,111
Latest member
Francis Xavier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top