How to capture the output of command window using VBS and notify user with a played sound?

Question

I have successfully managed to use VBS to open cmd for writing data to a database and pressing Enter key twice.

Here is my code:

Set wshshell = wscript.CreateObject("WScript.Shell") 
Wshshell.run "cmd" 
wscript.sleep 100
wshshell.sendkeys "firebase-import --service_account C:\Users --database_url https://firebaseio.com  --json C:\Users\Zed\testing.json"
wscript.sleep 100
WshShell.SendKeys "{ENTER}"
WshShell.SendKeys "{ENTER}"

Here is the console output:

firebase-import --service_account C:\Users\Zed\private-firebase-adminsdk-private --path /Jobs/Test --database_url https://private.firebaseio.com  --json C:\Users\Zed\testing.json
All data at https://private.firebaseio.com/Jobs/Test will be overwritten.
Press <enter> to proceed, Ctrl-C to abort.

Reading C:\Users\Zed\testing.json... (may take a minute)
Preparing JSON for import... (may take a minute)
Importing [==================================================] 100% (1/1)

Import completed.

I have also managed to play a sound using VBS. Here is the code:

Dim oPlayer
Set oPlayer = CreateObject("WMPlayer.OCX")

' Play audio
oPlayer.URL = "C:\Users\Zed\notify.mp3"
oPlayer.controls.play 
While oPlayer.playState <> 1 ' 1 = Stopped
  WScript.Sleep 100
Wend

' Release the audio file
oPlayer.close

Now, how to combine these two, so the sound only plays when Import completed message is shown in the console window?

If you would have read the file [README.md](https://github.com/FirebaseExtended/firebase-import) for `firebase-import` carefully displayed also on homepage of this utility, you would have saved yourself a lot of time after reading in __Usage__ section about: `--force Don't prompt before overwriting data.` It would be also good to read [Copy text from a Windows CMD window to clipboard](https://stackoverflow.com/questions/11543578/) because of copying output text as text would have saved you again time and you would have made most like __all__ strings private which you do not want public. — Mofi, Nov 19 '20 at 18:02
Open a [command prompt](https://www.howtogeek.com/235101/), run `where firebase-import` and look on the full qualified file name. I haven't installed this utility. But if the output file name ends with `.exe`, the execution of `cmd.exe` would not be needed at all as the executable can be executed directly from within the Visual Basic script. You could use the [.run](https://ss64.com/vb/run.html) method to run `cmd.exe` or `firebase-import` (on being an executable) with `bWaitOnReturn` set to `TRUE`. The [.exec](https://ss64.com/vb/exec.html) method would give you the option to capture output. — Mofi, Nov 19 '20 at 18:12

Étienne Laneville · Accepted Answer · 2020-11-19T21:34:55.953

As Mofi pointed out in the comments, you can disable the All data at https://private.firebaseio.com/Jobs/Test will be overwritten. prompt which gets you away from sending keystrokes. You can simply use the Windows Script Host Run method:

Dim objWshShell
Dim sCommand

sCommand = "firebase-import --service_account C:\Users --database_url https://firebaseio.com  --json C:\Users\Zed\testing.json"

Set objWshShell = WScript.CreateObject("WScript.Shell")
objWshShell.Run sCommand, 1, True

You can also wrap your code that plays a sound into a Sub:

Sub PlaySound(p_sURL)

    Dim oPlayer
    Set oPlayer = CreateObject("WMPlayer.OCX")
    
    ' Play audio
    oPlayer.URL = p_sURL
    oPlayer.Controls.play
    While oPlayer.playState <> 1 ' 1 = Stopped
      WScript.Sleep 100
    Wend
    
    ' Release the audio file
    oPlayer.Close

End Sub

You can call this subroutine after objWshShell.Run sCommand, 1, true:

objWshShell.Run sCommand, 1, true
PlaySound ("C:\Users\Zed\notify.mp3")

EDIT

In order to determine the outcome of the import process, you can redirect the output to a text file:

sCommand = "cmd /c firebase-import --service_account C:\Users --database_url https://firebaseio.com  --json C:\Users\Zed\testing.json > C:\temp\output.txt"

You can then read the file and look for "Import Completed":

Sub CheckOutput()
    Dim sOutput
    Dim objFSO, objFileReader

    Set objFSO  = CreateObject("Scripting.FileSystemObject")
    Set objFileReader = objFSO.OpenTextFile("c:\temp\output.txt", 1)
    sOutput = objFileReader.ReadAll
    objFileReader.Close

    If InStr(sOutput, "Import Completed") > 0 Then
        PlaySound ("C:\Users\Zed\notify.mp3")
    Else
        PlaySound ("C:\Users\Zed\error.mp3")
    End If

End Sub

You can use this sub after running the command:

objWshShell.Run sCommand, 1, true
CheckOutput

How to capture the output of command window using VBS and notify user with a played sound?

1 Answers1