Calling or Exiting a prog on time

TiTuS

Board Regular
Joined
Nov 10, 2004
Messages
238
Hi i was just wondering if it would be possible to have excel open or close a program on my computer due to the time on my comp...

This is what i need to do.

Between the hours of 4pm and 12Am (midnight) i want a certain program to stay closed so i would want excel to check maybe every 30mins to try shut down that prog and if it cant find it to shut down then ignore the error. But on the other hand from 12AM till 4pm i want the program to be open... with this side of the equation it would only need to be called at 12 Am to open because at any other stage i could open the prog manually its just so it can pull some data in the middle of the night when im not around.

If anyone could give any ideas on how this could be done it would be really appreciated!

In advance thanks a heap for your time and help,

Titus,

P.s this isnt an urgant request just a application of excel that could make life a bit simplar :)
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi TiTuS

What you have asked for can be done. I tested this with a routine to open calc.exe every other minute with a process check every 20 seconds and my test seemed to work ok.

Open your spreadsheet without enabling the macros (if any), and copy the following code into the 'ThisWorkbook' module (within the Visual Basic editor screen) :
Code:
Option Explicit

Private Sub Workbook_Open()

    Sec = 1800 'i.e. every 30 minutes
    Run "MyTimer"

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Run "StopMyTimer"

End Sub
{Thanks to Erik in this thread for the code}

For my test I changed the 'Sec' value to 20. Copy and paste the following code into a new Module :
Code:
Option Explicit

'Change MyProcess to the programme you want to start and stop
'I used the calc.exe programme for my test
Public Const MyProcess As String = "calc.exe"
'This is a log file to track instances of starting and stopping the process
'Remove the references to the log file, here and within the code, if you don't want this
Public Const MyFile As String = "c:\MyLog.txt"
Public Sec As Integer
Public When As Variant

Sub MyTimer()

When = Now + Sec / 60 / 60 / 24

Application.OnTime When, "MyTimer"

If Now < #4:00:00 PM# Then
    StartMyProcess (MyProcess)
Else
    StopMyProcess (MyProcess)
End If

'Used for testing purposes only (after setting Sec to 20 in the Workboook_Open module)
'If Minute(Now) Mod 2 = 0 Then
'    StopMyProcess (MyProcess)
'Else
'    StartMyProcess (MyProcess)
'End If

End Sub

Private Sub StopMyTimer()
    
    Application.OnTime EarliestTime:=When, Procedure:="MyTimer", schedule:=False

End Sub

Private Sub StartMyProcess(strStartThis As String)

On Error Resume Next

Dim objWMIcimv2 As Object
Dim objList As Object
Dim procID As Long
     
Set objWMIcimv2 = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\.\root\cimv2")
     
Set objList = objWMIcimv2.ExecQuery _
    ("select * from win32_process where name='" & strStartThis & "'")

If objList.Count = 0 Then
    'The process isn't running
    procID = Shell(MyProcess, vbHide)
    Open MyFile For Append As #1
    Write #1, Now & " : Process started ok."
    Close #1
    'MsgBox "opened" 'Used for testing purposes only
Else
    'The process has already been started
    DoEvents
    'MsgBox "already open" 'Used for testing purposes only
End If

Set objWMIcimv2 = Nothing
Set objList = Nothing

End Sub

Private Sub StopMyProcess(strTerminateThis As String)

On Error Resume Next

Dim objWMIcimv2 As Object
Dim objProcess As Object
Dim objList As Object
Dim intError As Integer
     
Set objWMIcimv2 = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\.\root\cimv2")
     
Set objList = objWMIcimv2.ExecQuery _
    ("select * from win32_process where name='" & strTerminateThis & "'")

If objList.Count = 0 Then
    'The process isn't running
    DoEvents
    'MsgBox "already closed" 'Used for testing purposes only
Else
    'The process is running
    Open MyFile For Append As #1
    For Each objProcess In objList
        'Terminate the process and all of its threads.
        intError = objProcess.Terminate
        If intError <> 0 Then
            'Return value is 0 for success. Any other number is an error.
            Write #1, Now & " : Unable to terminate process."
            'MsgBox "error" 'Used for testing purposes only
        Else
            Write #1, Now & " : Process terminated ok."
            'MsgBox "closed" 'Used for testing purposes only
        End If
    Next
    Close #1
    Set objProcess = Nothing
End If

Set objWMIcimv2 = Nothing
Set objList = Nothing

End Sub
Save and close the spreadsheet and re-open it with the macros enabled.

I left in the MsgBox lines that I used for testing purposes. You may want to run a test with something like the calculator first to check it does what you want. For testing purposes I also changed the 'MyTimer' sub code to look like this :
Code:
Sub MyTimer()

When = Now + Sec / 60 / 60 / 24

Application.OnTime When, "MyTimer"

'If Now < #4:00:00 PM# Then
'    StopMyProcess (MyProcess)
'Else
'    StartMyProcess (MyProcess)
'End If

'Used for testing purposes only (after setting Sec to 20 in the Workboook_Open module)
If Minute(Now) Mod 2 = 0 Then
    StopMyProcess (MyProcess)
Else
    StartMyProcess (MyProcess)
End If

End Sub

HTH, Andrew
 

TiTuS

Board Regular
Joined
Nov 10, 2004
Messages
238
Hey there Andrew,


Thankyou so much for your indepth reply! I gave it a bit of a test with calc and then loaded it in with the programme i am wanting it to work with - this also worked sweet, just one thing though...

Is it possible to block it from using the timer within the hour time frame i stated (4pm - midnight) because from what i can tell is at 4 it will close the prog yet at the next timer update it will just open again...

If i have understood this please forgive me but thats what it looks like to me...

Thank you so much though for your great reply - just a bit of modication in the above area needed...

Your Truly,

Titus
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi TiTuS
When I first posted my code I had it the wrong way around (ie starting at 4pm and closing at midnight) but then I realised my mistake and edited my main code. However I forgot to edit the final piece of code at the bottom of my reply - you will notice it is the other way around to the main part of my code. The main code has the times the correct way around and the final snippet of code should look like this :
Code:
Sub MyTimer()

When = Now + Sec / 60 / 60 / 24

Application.OnTime When, "MyTimer"

'If Now < #4:00:00 PM# Then
'    StartMyProcess (MyProcess)
'Else
'    StopMyProcess (MyProcess)
'End If

'Used for testing purposes only (after setting Sec to 20 in the Workboook_Open module)
If Minute(Now) Mod 2 = 0 Then
    StopMyProcess (MyProcess)
Else
    StartMyProcess (MyProcess)
End If

End Sub

Cheers, Andrew

Late edit : once the process has started this will continue to check every half hour and if the process is still running it won't do anything, otherwise it will try to restart the process again (provided it is before 4pm).
 

TiTuS

Board Regular
Joined
Nov 10, 2004
Messages
238

ADVERTISEMENT

Please do forgive me but im still not quite understanding it properly...

Even with the code like that it just ran the code and it is within the time frame - my prog opened...

I set my clock to 4pm and it wouldnt run but as soon as it clicked to 4.01 it did... i need it like what it does at 4pm but from 4pm till midnight including everyminute in between

Again thankyou so so so much for your help its greatly appreciated,

Titus
 

TiTuS

Board Regular
Joined
Nov 10, 2004
Messages
238
Oh wait sorry please forgive me i had misread your edits to the code - ive now modified it - hopefully it should work now! :) so so sorry - been working really long hours and the minds not quite here

will let you know how i go
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432

ADVERTISEMENT

Hi TiTuS
I set the 'Sec' value to 1800 so the code only ran once every half hour. I misread your original post in that the timing wasn't that critical. If you want this to check every minute then change the 'Sec = 1800' part to 'Sec = 60' and it will check every minute.
HTH, Andrew
 

TiTuS

Board Regular
Joined
Nov 10, 2004
Messages
238
Hi there Andrew,

I can seem to get the code to work fine with the test code
'Used for testing purposes only (after setting Sec to 20 in the Workboook_Open module)
If Minute(Now) Mod 2 = 0 Then
StopMyProcess (MyProcess)
Else
StartMyProcess (MyProcess)
End If

End Sub

But as soon as i move to the actual code i get no results either side of the time frame...
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi TiTuS

My mistake - I didn't test it with the actual time of the day and if I write every event to the event log then the 'StartMyProcess' is never triggered, irrespective of the time on the system clock. To fix it, change this part of the code :
Code:
If Now < #4:00:00 PM# Then
    StartMyProcess (MyProcess)
Else
    StopMyProcess (MyProcess)
End If
to this:
Code:
If Hour(Now) < 16 Then
    StartMyProcess (MyProcess)
Else
    StopMyProcess (MyProcess)
End If

Also, I don't think you can force the test by changing the system clock while the spreadsheet is open. The 'When' part of the code tells Excel when to next check it - so if you push your system clock forward an hour, then the code won't do anything for another 23 hours. To force the test you need to close the spreadsheet, change the system time and then re-open the spreadsheet.

Cheers, Andrew
 

TiTuS

Board Regular
Joined
Nov 10, 2004
Messages
238
Hi Andrew,

Thanks for the update,

I gave it a test today and it seemed to go sweet - but once it hit 4PM it just stopped "opening" the program it didnt actually close the program... i had a look at the log and it was as follows (test time was set to 10secs)

"9/28/2006 3:58:26 PM : Process started ok."
"9/28/2006 3:58:36 PM : Process started ok."
"9/28/2006 3:58:46 PM : Process started ok."
"9/28/2006 3:58:56 PM : Process started ok."
"9/28/2006 3:59:06 PM : Process started ok."
"9/28/2006 3:59:16 PM : Process started ok."
"9/28/2006 3:59:26 PM : Process started ok."
"9/28/2006 3:59:36 PM : Process started ok."
"9/28/2006 3:59:46 PM : Process started ok."
"9/28/2006 3:59:56 PM : Process started ok."

thats the last bit leading up to 4 - i left it till 4.02 before opening the log and that is how it read :)

So it works but now just needs to be edited slightly so it actually closes the prog instead of not just "not opening" it

Again thankyou so so much for all your help its is deffinitly appreciated!

Titus
 

Forum statistics

Threads
1,141,679
Messages
5,707,786
Members
421,527
Latest member
Tamiwsw

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