ebrts ebrts - 1 year ago 58
Bash Question

Strange Status Bar Behavior when Invoking a Shell Command Using Excel VBA

I'm using the following subroutine to invoke a shell command as part of a larger process using VBA in excel:

Sub runShellScript(ByVal directory As String, command As String)
Dim terminal
Dim output

Application.StatusBar = "Processing data with python...this can take a while"

Set terminal = CreateObject("WScript.Shell")
terminal.Currentdirectory = directory
output = terminal.Run(command, 0, True)

Set output = Nothing
Set terminal = Nothing
End Sub

Everything works fine, except that I can't get the status bar to behave the way I'd like it to. Currently, it initiates correctly (that is, the status bar changes to "Processing data with python..."), but after a few seconds that message gets cleared, and the status bar reverts to the default "READY" status. Even more strangely, however, if I stop the function at any line, it reverts back to "Processing data with python..."

Clearly this has something to do with the fact that the terminal is independent of my excel instance, but given that my code is otherwise waiting for the shell script to finish, I don't understand why the status bar is being "released".

Any fix, or pointer in the right direction would be much appreciated.

Answer Source

Try following Microsoft's example. They also manipulate the DisplayStatusBar value:

oldStatusBar = Application.DisplayStatusBar 
Application.DisplayStatusBar = True 
Application.StatusBar = "Please be patient..." 
Workbooks.Open filename:="LARGE.XLS" 
Application.StatusBar = False 
Application.DisplayStatusBar = oldStatusBar