A quick reminder on capturing the output of a shell command in VBA. The function below executes the command then dumps the output strings in an array.

 

Public Function ShellRun(sCmd As String, nArray As Integer) As String

    'Run a shell command, returning the output as a string'

    Dim oShell As Object
    Set oShell = CreateObject("WScript.Shell")

    'run command'
    Dim oExec As Object
    Dim oOutput As Object
    Set oExec = oShell.Exec(sCmd)
    Set oOutput = oExec.StdOut

    'handle the results as they are written to and read from the StdOut object'
    Dim s As String
    Dim sLine As String
    Dim sArray() As String
    i = 0
   
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        ReDim Preserve sArray(i)
        sArray(i) = sLine
        i = i + 1
    Wend

    ShellRun = sArray(nArray)

End Function

 

The sub below shows a basic implementation of the function. In this case we are grabbing some info about the host machine: Hostname, model name and serial number.



Sub test()

MsgBox ShellRun("hostname", 0)
ReDim sArray(0)

MsgBox ShellRun("wmic csproduct get name", 1)
ReDim sArray(0)

MsgBox ShellRun("wmic bios get serialnumber", 1)
ReDim sArray(0)


End Sub