Capture output value shell command in VBA

Option Explicit

'Constants
Private Const WAIT_INFINITE         As Long = (-1&)
Private Const STARTF_USESHOWWINDOW  As Long = &H1
Private Const STARTF_USESTDHANDLES  As Long = &H100
Private Const SW_HIDE               As Integer = 0& '?  LONG

#If Win64 Then
    'For 64 bit Excel.
    'Contains the security descriptor for an object and specifies whether the handle retrieved by specifying this structure is inheritable.
    Private Type SECURITY_ATTRIBUTES
        nLength                 As Long
        lpSecurityDescriptor    As LongPtr
        bInheritHandle          As Long
    End Type

    'Specifies the window station, desktop, standard handles, and appearance of the main window for a process at creation time.
    Private Type STARTUPINFO
        cb                      As Long
        lpReserved              As String
        lpDesktop               As String
        lpTitle                 As String
        dwX                     As Long
        dwY                     As Long
        dwXSize                 As Long
        dwYSize                 As Long
        dwXCountChars           As Long
        dwYCountChars           As Long
        dwFillAttribute         As Long
        dwFlags                 As Long
        wShowWindow             As Integer
        cbReserved2             As Integer
        lpReserved2             As Byte
        hStdInput               As LongPtr
        hStdOutput              As LongPtr
        hStdError               As LongPtr
    End Type


    'Contains information about a newly created process and its primary thread.
    Private Type PROCESS_INFORMATION
        hProcess        As LongPtr
        hThread         As LongPtr
        dwProcessId     As Long
        dwThreadId      As Long
    End Type





    'Creates a new process and its primary thread. The new process runs in the security context of the calling process.
    Private Declare PtrSafe Function CreateProcess Lib "kernel32" Alias "CreateProcessA" (ByVal lpApplicationName As String, _
                                                                                            ByVal lpCommandLine As String, _
                                                                                            lpProcessAttributes As Any, _
                                                                                            lpThreadAttributes As Any, _
                                                                                            ByVal bInheritHandles As Long, _
                                                                                            ByVal dwCreationFlags As Long, _
                                                                                            lpEnvironment As Any, _
                                                                                            ByVal lpCurrentDriectory As String, _
                                                                                            lpStartupInfo As STARTUPINFO, _
                                                                                            lpProcessInformation As PROCESS_INFORMATION) As Long

    'Creates an anonymous pipe, and returns handles to the read and write ends of the pipe.
    Private Declare PtrSafe Function CreatePipe Lib "kernel32" (phReadPipe As LongPtr, _
                                                                phWritePipe As LongPtr, _
                                                                lpPipeAttributes As Any, _
                                                                ByVal nSize As Long) As Long

    'Reads data from the specified file or input/output (I/O) device. Reads occur at the position specified by the file pointer if supported by the device.
    Private Declare PtrSafe Function ReadFile Lib "kernel32" (ByVal hFile As LongPtr, _
                                                             lpBuffer As Any, _
                                                             ByVal nNumberOfBytesToRead As Long, _
                                                             lpNumberOfBytesRead As Long, _
                                                             lpOverlapped As Any) As Long

    'Closes an open object handle.
    Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long

    'Waitingfor a single object to terminite
    Private Declare PtrSafe Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As LongPtr, ByVal dwMilliseconds As Long) As Long

    'Get process running result.
    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As LongPtr, lpExitCode As Long) As Long

    'I don't know what this for.vRetrieves the contents of the STARTUPINFO structure that was specified when the calling process was created. Not called
    'Private Declare PtrSafe Sub GetStartupInfo Lib "kernel32" Alias "GetStartupInfoA" (ByVal lpStartupInfo As LongPtr)

    'Get file size
    Private Declare PtrSafe Function GetFileSize Lib "kernel32" (ByVal hFile As LongPtr, lpFileSizeHigh As Long) As Long
#Else
    'For 32 bit Excel.
    'Contains the security descriptor for an object and specifies whether the handle retrieved by specifying this structure is inheritable.
    Private Type SECURITY_ATTRIBUTES
        nLength                 As Long
        lpSecurityDescriptor    As Long
        bInheritHandle          As Long
    End Type

    'Specifies the window station, desktop, standard handles, and appearance of the main window for a process at creation time.
    Private Type STARTUPINFO
        cb                      As Long
        lpReserved              As String
        lpDesktop               As String
        lpTitle                 As String
        dwX                     As Long
        dwY                     As Long
        dwXSize                 As Long
        dwYSize                 As Long
        dwXCountChars           As Long
        dwYCountChars           As Long
        dwFillAttribute         As Long
        dwFlags                 As Long
        wShowWindow             As Integer
        cbReserved2             As Integer
        lpReserved2             As Byte
        hStdInput               As Long
        hStdOutput              As Long
        hStdError               As Long
    End Type


    'Contains information about a newly created process and its primary thread.
    Private Type PROCESS_INFORMATION
        hProcess        As Long
        hThread         As Long
        dwProcessId     As Long
        dwThreadId      As Long
    End Type





    'Creates a new process and its primary thread. The new process runs in the security context of the calling process.
    Private Declare PtrSafe Function CreateProcess Lib "kernel32" Alias "CreateProcessA" (ByVal lpApplicationName As String, _
                                                                                            ByVal lpCommandLine As String, _
                                                                                            lpProcessAttributes As Any, _
                                                                                            lpThreadAttributes As Any, _
                                                                                            ByVal bInheritHandles As Long, _
                                                                                            ByVal dwCreationFlags As Long, _
                                                                                            lpEnvironment As Any, _
                                                                                            ByVal lpCurrentDriectory As String, _
                                                                                            lpStartupInfo As STARTUPINFO, _
                                                                                            lpProcessInformation As PROCESS_INFORMATION) As Long

    'Creates an anonymous pipe, and returns handles to the read and write ends of the pipe.
    Private Declare PtrSafe Function CreatePipe Lib "kernel32" (phReadPipe As Long, _
                                                                phWritePipe As Long, _
                                                                lpPipeAttributes As Any, _
                                                                ByVal nSize As Long) As Long

    'Reads data from the specified file or input/output (I/O) device. Reads occur at the position specified by the file pointer if supported by the device.
    Private Declare PtrSafe Function ReadFile Lib "kernel32" (ByVal hFile As Long, _
                                                             lpBuffer As Any, _
                                                             ByVal nNumberOfBytesToRead As Long, _
                                                             lpNumberOfBytesRead As Long, _
                                                             lpOverlapped As Any) As Long

    'Closes an open object handle.
    Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

    'Waitingfor a single object to terminite
    Private Declare PtrSafe Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, _
                                                                            ByVal dwMilliseconds As Long) As Long

    'Get process running result.
    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, _
                                                                            lpExitCode As Long) As Long

    'I don't know what this for.vRetrieves the contents of the STARTUPINFO structure that was specified when the calling process was created. Not called
    'Private Declare PtrSafe Sub GetStartupInfo Lib "kernel32" Alias "GetStartupInfoA" (ByVal lpStartupInfo As Long)

    'Get file size
    Private Declare PtrSafe Function GetFileSize Lib "kernel32" (ByVal hFile As Long, _
                                                                    lpFileSizeHigh As Long) As Long
#End If

Public Function RunDosCmd2(szBinaryPath As String, szCommandLn As String) As String

'Declaring the necessary variables (different for 32 or 64 bit Excel).
#If Win64 Then
    Dim hRead                       As LongPtr
    Dim hWrite                      As LongPtr
#Else
    Dim hRead                       As Long
    Dim hWrite                      As Long
#End If

Dim tSA_CreatePipe              As SECURITY_ATTRIBUTES
Dim tSA_CreateProcessPrc        As SECURITY_ATTRIBUTES
Dim tSA_CreateProcessThrd       As SECURITY_ATTRIBUTES
Dim tSA_CreateProcessPrcInfo    As PROCESS_INFORMATION
Dim tStartupInfo                As STARTUPINFO
Dim bRead                       As Long
Dim abytBuff()                  As Byte
Dim lngResult                   As Long
Dim szFullCommand               As String
Dim lngExitCode                 As Long
Dim lngSizeOf                   As Long
Dim Result                      As String

'Set the pipe security attributes.
tSA_CreatePipe.nLength = Len(tSA_CreatePipe)
tSA_CreatePipe.lpSecurityDescriptor = 0&
tSA_CreatePipe.bInheritHandle = True

'Set the process and thread security attributes
tSA_CreateProcessPrc.nLength = Len(tSA_CreateProcessPrc)
tSA_CreateProcessThrd.nLength = Len(tSA_CreateProcessThrd)

'Create the pipe.
If (CreatePipe(hRead, hWrite, tSA_CreatePipe, 0&) <> 0&) Then

    'Set the startup information.
    With tStartupInfo
        .cb = LenB(tStartupInfo)    'lenb? why not len, string may contain multibyte characters?
        .hStdOutput = hWrite
        .hStdError = hWrite
        .dwFlags = STARTF_USESHOWWINDOW Or STARTF_USESTDHANDLES 'The CreateProcess function wShowWindow member contains additional information. The hStdInput, hStdOutput, and hStdError members contain additional information.
        .wShowWindow = SW_HIDE  'Do not show windows
        '.lpReserved = vbNullString  'Reserved; must be NULL.
        '.cbReserved2 = 0    'Reserved for use by the C Run-time; must be zero.
        '.lpReserved2 = 0&   'Reserved for use by the C Run-time; must be NULL. https://learn.microsoft.com/en-us/windows/win32/api/processthreadsapi/ns-processthreadsapi-startupinfoa
    End With

    'Make the full command string using binary path and parameters, binary path can also include parameters(means parameters can be intergred into binarypath)
    szFullCommand = """" & szBinaryPath & """" & " " & szCommandLn

    'Create the process and run the console application.
    lngResult = CreateProcess(vbNullString, szFullCommand, tSA_CreateProcessPrc, tSA_CreateProcessThrd, 1, 0&, ByVal 0&, vbNullString, tStartupInfo, tSA_CreateProcessPrcInfo)
    If (lngResult <> 0&) Then   'If create proess sucess

        'Wainting for the process to end for infinite time.
        lngResult = WaitForSingleObject(tSA_CreateProcessPrcInfo.hProcess, WAIT_INFINITE)

        'Get command line output file size (in memeory).
        lngSizeOf = GetFileSize(hRead, 0&)
        If (lngSizeOf > 0) Then
            ReDim abytBuff(lngSizeOf - 1)

            'Retrieve output from pipe.
            If ReadFile(hRead, abytBuff(0), UBound(abytBuff) + 1, bRead, ByVal 0&) Then
                If bRead > 0 Then
                    ReDim Preserve abytBuff(bRead - 1)
                    Result = StrConv(abytBuff, vbUnicode)
                End If
            End If
        End If

        'Get process exit code, if exitcode=0, means suceed without error
        Call GetExitCodeProcess(tSA_CreateProcessPrcInfo.hProcess, lngExitCode)

        'Close handle to the thread
        CloseHandle tSA_CreateProcessPrcInfo.hThread

        'Close handle to the process
        CloseHandle tSA_CreateProcessPrcInfo.hProcess

        'If process ended with error number
        If (lngExitCode <> 0&) Then Err.Raise vbObject + 1235&, "GetExitCodeProcess", "Non-zero Application exist code"

        'Close pipe handles
        CloseHandle hWrite
        CloseHandle hRead

    'If create process failed
    Else
        Err.Raise vbObject + 1236&, "CreateProcess", "CreateProcess Failed, Code: " & Err.LastDllError
    End If
End If

'Return the output.
RunDosCmd2 = Result

End Function