Showing VBA macro output in a VBS script

Question

I'm running a VBA macro through a VBS Script:

scriptDir = CreateObject("Scripting.FileSystemObject").GetParentFolderName(WScript.ScriptFullName)
Set excel = CreateObject("Excel.Application")
excel.Workbooks.Open scriptDir & "\\OutputWriter.xlsm"
excel.Application.Run "OutputWriter.xlsm!write_output"
excel.Application.Quit

How can I show output generated in the write_output macro in the VBS script? Debug.Print statements are not being shown. Right now I only get a Microsoft copyright prompt.

> CScript .\startwriter.vbs
Microsoft (R) Windows Script Host Version 10.0
Copyright (C) Microsoft Corporation. All rights reserved.

Note that I want 'live' progress feedback in command line on what the macro is doing, so writing to a file to open later or using user forms is not possible.

Answer 1

Here is one approach. (edited to use a Collection instead of a Cell reference)

First, you need to return control to vbscript immediately, so in VBA kick off the main process using Application.OnTime

Second, the launching VBA method returns a reference to a Collection object to the calling vbscript. The VBA process logs to the collection, and the vbscript can read from that Collection and show any new content.

VBscript:

Dim excel, t, col, n, i, numLines, txt

Set excel = GetObject(,"Excel.Application")
set col = excel.Run("tmp.xlsm!StartProcess") 'returns a Range reference

t = Now()
n = 0
do 
    numLines = col.Count
    if numLines > n then          'check for any new lines
        For i = n+1 to numLines   'list each new line
            txt = col(i)
            WScript.Echo txt
            If txt = "Complete" Then Exit Do ' "done" flag sent.... 
        Next 
        n = numLines              'tag last line shown
    End if
    WScript.Sleep 500             'half-second pause
    
    If Now() - t > (1/24/60/60) * 60 then exit do 'escape after a minute just in case...
loop 

WScript.Echo "VBscript complete"

VBA (regular module):

Option Explicit

Dim col As Collection

Function StartProcess() As Object
    'need to run this asynchronously so we can return to the vbscript immediately
    Application.OnTime EarliestTime:=Now, Procedure:="Process", Schedule:=True
    
    Set col = New Collection
    
    Set StartProcess = col 'return collection reference for logging
End Function

'simulate a long-running process
Sub Process()
    Dim i As Long
    For i = 1 To 10
        Debug.Print "Step " & i
        LogOutput "Step " & i
        Application.Wait Now + TimeSerial(0, 0, 1)
    Next i
    LogOutput "Complete"
End Sub

Sub LogOutput(content As String)
    col.Add content
    DoEvents '<<<< important
End Sub

Answer 2

The following ended up working:

• Create a barebone Console object able to collect written output and sending it to the VBS StdOut stream

• Send this object to the macro

• Have the macro use this object to log

VBS:

Class Console
    Public Sub WriteOut(ByVal line)
        If InStr(1, LCase(WScript.FullName), "cscript.exe", vbTextCompare) > 0 Then
            WScript.StdOut.WriteLine line
        Else
            WScript.Echo line
        End If
    End Sub
End Class

scriptDir = CreateObject("Scripting.FileSystemObject").GetParentFolderName(WScript.ScriptFullName)
Set excel = CreateObject("Excel.Application")
set wb = excel.Workbooks.Open(scriptDir & "\\OutputWriter.xlsm")
excel.Application.Run "OutputWriter.xlsm!WriteOutput", New Console
wb.Close False
excel.Application.Quit

VBA:

Public Sub WriteOutput(ByVal Console As Object)
    ...
    Console.WriteOut "status"
    ...
End Sub