Code to close and reopen Excel after a set time- Is it feasible ?

[Jaafar Tribak]

I'm enquiring about this just out of intellectual curiosity . Any thoughts ?

Regards.

 

[crimson_b1ade]

it's possible but not practical unless you are trying to play a joke on someone. maybe this...it doesn't close the app but just hides it for a few seconds:

Sub AngryInch()

Application.OnTime Now + TimeValue("00:00:30"), "WakeUp"

Application.OnTime Now + TimeValue("00:00:10"), "ShutOff"

End Sub


Private Sub ShutOff()

Application.Visible = False

End Sub

Private Sub WakeUp()

Application.Visible = True

End Sub

 

[Jaafar Tribak]

Thanks crimson_b1ade.

Indeed,Although not impossible, it's difficult to imagine a real world scenario where this would be required.

As i said , the main purpose of this is trying to push the bounderies of what one can do with excel/vba and learn something new

Regards.

 

[xenou]

I might consider using the open_event to open a little .vbs file to keep track of the time as well as the name of the Excel file just opened...It seems you would need to have the code running outside of excel in any case, since the app will shutdown. I'm assuming in a vbs script you could use a Timer object of some kind.

 

[Jaafar Tribak]

I might consider using the open_event to open a little .vbs file to keep track of the time as well as the name of the Excel file just opened...It seems you would need to have the code running outside of excel in any case, since the app will shutdown. I'm assuming in a vbs script you could use a Timer object of some kind.

Thanks Alexander.

Yes, that's what i had in mind. I guess that will need a windows timer. Unfortunatly, VBS doesn't support API declarations.

I am trying to solve this by creating a new instance of excel and reopen the workbook from there. However, i don't want to use the VBE of the seconde instance to inject the code as that will only work depending on the Macro security settings of the user. I want to make this to be as robust as possible.

Regards.

 

[tusharm]

In VBS you have access to WScript.Sleep.

If you use a 2nd instance of Excel, I don't know why you need to modify code. I must be missing something but it appears to me that you already have a workbook with code. So what's to modify?

Thanks Alexander.

Yes, that's what i had in mind. I guess that will need a windows timer. Unfortunatly, VBS doesn't support API declarations.

I am trying to solve this by creating a new instance of excel and reopen the workbook from there. However, i don't want to use the VBE of the seconde instance to inject the code as that will only work depending on the Macro security settings of the user. I want to make this to be as robust as possible.

Regards.

 

[Jaafar Tribak]

In VBS you have access to WScript.Sleep.

If you use a 2nd instance of Excel, I don't know why you need to modify code. I must be missing something but it appears to me that you already have a workbook with code. So what's to modify?

Thanks Tushar.

I am not well versed with VBS and i am not sure the Sleep command would do what i need anyway.

As for needing to modify the code, what i meant was that the code to reopen excel must run from an out of process routine so i must find a way to write and run the routine on the fly before i actually close excel. Sorry, I hope i am not confusing this further.

Regards.

 

[ZVI]

Hi Jaafar,

Before quitting an Excel application you can charge the schedule task by calling the AT.EXE or SCHTASKS.EXE operating system command from VBA Shell function.

Example of such command: AT 15:30 /INTERACTIVE "C:\LoadExcel.vbs"
Where the content of VBS file could be like this:

<font face=Courier New>
With CreateObject("Excel.Application")
  .Visible = True
  .Workbooks.Open ("C:\Test.xls")
  .Application.Run "MyMacro"
End With</FONT>

Below is the full VBA code for auto creating of such VBS file and charging the schedule task to reload workbook itself in a new instance of Excel application.

<font face=Courier New>
' ZVI:2009-07-05 http://www.mrexcel.com/forum/showthread.php?t=400499
' Scheduled reloading of itself
Sub ReloadExcel()
  
  Dim xlFileName$, vbsFileName$, vbsText$, FileNo%, Wb
  
  ' Define reloading files
  xlFileName = ThisWorkbook.FullName
  vbsFileName = Replace(LCase(xlFileName), ".xls", ".vbs")
  
  ' Build the text of VB script
  vbsText = "With CreateObject(""Excel.Application"")" & vbLf _
          & ".Visible = True" & vbLf _
          & ".Workbooks.Open (""" & xlFileName & """)" & vbLf _
          & ".Application.Run ""MyMacro""" & vbLf _
          & "End With"
  
  ' Create VBS file
  On Error Resume Next
  Kill vbsFileName
  FileNo = FreeFile
  Open vbsFileName For Binary Access Write As #FileNo
  Put #FileNo, , vbsText
  Close #FileNo
  
  ' Charge the scheduled task
  Shell "AT " & Format(Now + TimeSerial(0, 1, 0), "hh:mm") & " /INTERACTIVE """ & vbsFileName & """"
  
  ' Close all workbooks and quit
  'For Each Wb In Application.Workbooks: Wb.Close:  Next
  'Application.Quit
  
End Sub

' Macro for calling from VBS
' Pay attention on SYSTEM UserName used by VBS
Sub MyMacro()
  MsgBox "Hi from " & Application.UserName & "!"
End Sub</FONT>

Regards,
Vladimir

 

[Jaafar Tribak]

Vladimir. Thanks very much for the code.

Shelling the taskscheduler is definitly an interesting idea however this assumes scripting as well as the task scheduler are not disabled by the administrator.

I tweaked & run your code at home but couldn't make it work either.- the script file was created but excel didn't reload.- i'll look into it again tonight.

I've never shelled/programmed the win taskscheduler before maybe i should take this opportunity to learn more about its switches, etc ...

Again, thanks for your help.

Regards.

 

[ZVI]

Yes, it’s true - the scripting and scheduling service should not be blocked.

By default the Schedule service is configured to start manually. To use the At.exe command to schedule you must configure the Schedule service to start automatically. After you complete this task, the schedule service starts automatically every time you start the computer.

You can find this and more info in the links:
HOW TO: Use the At.exe Command to Schedule a Backup in Windows NT

"Windows XP includes powerful commandline admin utilities including schtasks which replaces Windows NT and Windows 2000 commandline scheduler AT.exe. Schtasks allows an administrator to create, delete, query, change, run and end scheduled tasks on a local or remote system"
XP schtasks commandline utility replaces AT.exe
Schtasks

To check if task is charged: Desktop > Start > Run > CMD > OK > AT
Use command: AT /? to see the help