ShellandWait problem

[doasidont]

I'm using a 'ShellandWait' routine to launch a connection to a remote server and SFTP some files. The connection and download are working fine. The routine, however, is started automatically as a Workbook Open event within a workbook that is opened as a scheduled task. What I would like to do is offer the user the ability to open the workbook manually without starting the 'ShellandWait' routine. I don't want to use a msgbox because under normal circumstances the workbook is a scheduled task and can't wait for a user response (unless there is a way to automatically answer 'OK' to the msgbox.

Something like offering the user (via the status bar) the opportunity to hit the 'ESC' key during a preset wait period (e.gf. 10 secs) before the 'ShellandWait' is executed would be fine, unless there is a better way.

I've tried different code but can't capture pressing the 'ESC' key without interrupting the code and causing a non-graceful abort. Is there a way to capture it so that I can control how the routine exits?

Thanks.

[Spoo]

Doas

I'm not familiar with scheduled tasks in Excel, but let
me give it a shot...

Questions:

• Does the user "normally" manually launch the WB?
• What language is the ShellandWait routine -- VBA, VB6, or something else?
• What would you use for coding your solution -- VBA, VB6, or something else?

Possible solution:

When the user "manually" opens the WB, present a msgbox
with two buttons - Yes, No.

Something like...

This WorkBook will enter ShellandWait mode in 10 seconds.
Do you want to interrupt?

Yes - prevent ShellandWait mode
No - allow ShellandWait mode to proceed normally

Remaining issues:

• Is that different from what you proposed? -- seems to be
• This seems to mean that user will encounter this msgbox every time -- is that good or bad?

Spoo

[doasidont]

Thanks for quick reply.

The scheduled task is actually a Windows scheduled task; i.e., not specific to Excel. Any job can be started using DOS batch commands in a script.

The workbook is not normally started by any user. Windows starts it at a scheduled time daily.

The ShellandWait is a VBA routine. The DOS batch command script is passed as a parameter. Here's code:

Public Function ShellAndWait(ByVal batFile As String)
    '
    ' Shells a new process and waits for it to complete.
    ' Calling application is totally non-responsive while
    ' new process executes.
    '
    Dim PID As Long
    Dim hProcess As Long
    Dim nRet As Long
    
    '// Unlike other Functions Shell generates an error
    '// instead of returning a 0 so handling the error
    '// = Application NOT started.
    On Error Resume Next
    
    PID = Shell(batFile, vbMinimizedNoFocus)
    
    If Err Then
        '// handle the error here and End
        'MsgBox "Could NOT execute:= " & batFile
        errMsg = "Could NOT execute:= " & batFile
        Exit Function
    End If
    
    On Error GoTo 0
    
    '// SYNCHRONIZE For Windows NT/2000:
    '// Enables using the process handle in any of the wait
    '// functions to wait for the process to terminate.
    '// obviously with NT you need access rights.
    hProcess = OpenProcess(SYNCHRONIZE, False, PID)
    
    '// Just set the dwMilliseconds to INFINITE to initiate a Loop
    nRet = WaitForSingleObject(hProcess, INFINITE)
    
    Do
        GetExitCodeProcess hProcess, nRet
        DoEvents
    Loop While nRet = STILL_ACTIVE
    
    CloseHandle hProcess

End Function

I use VBA. As I said, it could simply be something like initiating a wait loop, during which the user could hit ESC or some combination of keys that would exit the routine before the 'ShellandWait' is executed.

The suggestion to use a message box won't work because the program is normally started by Windows, middle of night, and there is no one to respond to the message.

Thanks.

[Spoo]

Doas

Thanks for the tutorial on scheduled tasks ..

As for this..

The suggestion to use a message box won't work because the program is normally started by Windows, middle of night, and there is no one to respond to the message.

.. while I didn't explain very well, the "will enter .. in 10 seconds"
was meant to convey that if no action is taken (ie, neither button
is pressed), then the msgbox would "turn off" and normal ShellandWait
would proceed.

This is, I guess, equivalent to your answer 'OK' idea in your OP.
Natch, some sort of timer would be needed here.

And, as for this..

As I said, it could simply be something like initiating a wait loop, during which the user could hit ESC or some combination of keys that would exit the routine before the 'ShellandWait' is executed.

... more questions:

• this would happen during the day, right?
• the user would have manually launched the WB, right?
• how would this interfere with the DOS batch command
  that would occur later, at night?

Spoo

[doasidont]

It really doesn't matter whether day or night. Point is there is no one present to read and respond to the msgbox message.

There will be times when the user wants to start the WB, but doesn't want to start the 'ShellandWait'. Most of the time, the WB will be started by Windows with no user present.

The DOS batch script is also irrelevant. I just need a way to pause the WB open event and wait for some key board event. If the keyboard event happens, I want to exit the open event procedure before the DOS script is launched. If the keyboard event does not happen, the script is launched.

If there was a way to automatically turn off the msgbox after the wait period, that would also work for me.

Thanks.

[Spoo]

Doas

This is the closest I could come.

On your WorkSheet, add

• a CommandButton control named cbMsgBox
• a Label control named Label1 -- set Visible property to False

Then, add this code

Private Sub cbMsgBox_Click()
    With Label1           ' simulates a MsgBox
        .Visible = True
        .Top = 150        ' change as desired
        .Width = 220      ' change as desired
    End With
    myTimer 15            ' duration in seconds .. change as desired
End Sub
'---------------------------------------
Private Sub Label1_Click()
    Label1.Visible = False
    '  < extra code here >
End Sub
'---------------------------------------
Sub myTimer(nDelay)
    Dim x As Single
    x = Timer + nDelay
    Do While x > Timer
        DoEvents
        z = x - Timer
        msg = "This WorkBook will enter ShellandWait mode" + vbCrLf
        msg = msg + "in " + Format(z, "00") + " seconds" + vbCrLf + vbCrLf
        msg = msg + "Click to STOP"
        Label1.Caption = msg
        DoEvents
    Loop
    Label1.Visible = False
    '  < extra code here >
End Sub

Action:

• Click cbMsgBox to start -- to act as "invoke" by your DOS batch mode
• The countdown will "update" every second, counting down to 0
  Actually, it updates every "nanosecond", but the Format function
  trims it to the nearest second. Change to "00.0" to see 1/10th's of
  a second whip by.
• If you do nothing, the Label will disappear in 15 seconds
• If you click the Label, it will disappear immediately.
  >> Note, however, that cbMsgBox remains depressed for the remaining
  time. I imagine some better code in MyTimer could make it "undepress"
  faster.
• <extra code here> would be where you call your current sub

Does this get you started?

Spoo

[doasidont]

Thanks. I'll give this a try.

I found another possible solution which is to use 'Application.EnableCancelKey = xlErrorHandler with an errorHandler routine that gracefully exits before launching the script. Before exiting I reset 'Application.EnableCancelKey = xlInterrupt'.

This will temporarily disable the 'ESC' key. I post a message in status bar that counts down from 10 secs, offering user op to hit ESC while waiting.

But I'm glad you came up with the disappearing msgbox. I can use that.

Thanks for all your help.

[dilettante]

Office applications really really are not intended to be run as unattended tasks. Any number of dialogs might be raised causing such a scheduled task to hang, along with several other kinds of scenarios that can cause trouble.

Considerations for server-side Automation of Office

Note In this context, the term "server-side" also applies to code that is running on a Windows workstation, if the code is running from a Windows workstation other than the interactive station of the user who is logged on. For example, code that is started by Task Scheduler under the SYSTEM account runs in the same environment as "server-side" ASP code or as DCOM code. Therefore, many of the issues that this article describes may occur.

This whole thing (including what the BAT files does) could probably be implemented more safely and concisely as a WSH script, something that is designed to run as a scheduled task using the script host's /B (batch) switch.