Capture output value shell command in VBA-2

Option Explicit

'Declaring the necessary API functions and types based on Excel version.
#If Win64 Then

    'For 64 bit Excel.
    'Creates an anonymous pipe, and returns handles to the read and write ends of the pipe.
    Public 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.
    Public 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

    'Creates a new process and its primary thread. The new process runs in the security context of the calling process.
    Public 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

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

    'Contains the security descriptor for an object and specifies whether the handle retrieved by specifying this structure is inheritable.
    Public 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.
    Public 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 LongPtr
        hStdInput           As LongPtr
        hStdOutput          As LongPtr
        hStdError           As LongPtr
    End Type

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

#Else

    'For 32 bit Excel.
    Public Declare Function CreatePipe Lib "kernel32" (phReadPipe As Long, _
                                                       phWritePipe As Long, _
                                                       lpPipeAttributes As Any, _
                                                       ByVal nSize As Long) As Long

    Public Declare Function ReadFile Lib "kernel32" (ByVal hFile As Long, _
                                                     lpBuffer As Any, _
                                                     ByVal nNumberOfBytesToRead As Long, _
                                                     lpNumberOfBytesRead As Long, _
                                                     lpOverlapped As Any) As Long

    Public Declare 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

    Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

    'Types.
    Public Type SECURITY_ATTRIBUTES
        nLength                                As Long
        lpSecurityDescriptor                   As Long
        bInheritHandle                         As Long
    End Type

    Public 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 Long
        hStdInput                              As Long
        hStdOutput                             As Long
        hStdError                              As Long
    End Type

    Public Type PROCESS_INFORMATION
        hProcess                               As Long
        hThread                                As Long
        dwProcessId                            As Long
        dwThreadId                             As Long
    End Type

#End If

'Contants.
Public Const STARTF_USESHOWWINDOW  As Long = &H1
Public Const STARTF_USESTDHANDLES  As Long = &H100
Public Const SW_HIDE               As Integer = 0
Public Const BUFSIZE               As Long = 1024 * 10

Public Function ExecuteAndCapture(ByVal CommandLine As String, Optional ByVal StartInFolder As String = vbNullString) As String

    '------------------------------------------------------------------------------------
    'Runs a console application (with a hidden window) and returns its output as string.
    'First it creates a pipe and executes the console application, telling it
    'to send the output and (any) error information to the pipe.
    'Then, it reads from the pipe until there is no output left to read.

    'Written By:    Christos Samaras
    'Date:          17/09/2017
    'Last Updated:  26/01/2020
    'E-mail:        xristos.samaras@gmail.com
    'Site:          https://www.myengineeringworld.net
    '------------------------------------------------------------------------------------

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

    'Declaring the rest variables.
    Dim sa                  As SECURITY_ATTRIBUTES
    Dim si                  As STARTUPINFO
    Dim pi                  As PROCESS_INFORMATION
    Dim baOutput(BUFSIZE)   As Byte
    Dim sOutput             As String
    Dim sTemp               As String
    Dim lBytesRead          As Long

    'Set the security attributes.
    With sa
        .nLength = Len(sa)
        .bInheritHandle = 1
    End With

    'Create the pipe.
    If CreatePipe(hPipeRead, hPipeWrite, sa, 0) = 0 Then
        Exit Function
    End If

    'Set the startup information.
    With si
        .cb = Len(si)
        .dwFlags = STARTF_USESHOWWINDOW Or STARTF_USESTDHANDLES
        .wShowWindow = SW_HIDE
        .hStdOutput = hPipeWrite
        .hStdError = hPipeWrite
    End With

    'Create the process and run the console application.
    If CreateProcess(vbNullString, CommandLine, ByVal 0&, ByVal 0&, 1, 0&, ByVal 0&, StartInFolder, si, pi) Then

        Call CloseHandle(hPipeWrite)
        Call CloseHandle(pi.hThread)
        hPipeWrite = 0

        Do
            'Wait.
            DoEvents

            'If all the information is read from the pipe, then exit.
            If ReadFile(hPipeRead, baOutput(0), BUFSIZE, lBytesRead, ByVal 0&) = 0 Then
                Exit Do
            End If

            'Pass the inforrmation to a variable.
            sOutput = Left$(StrConv(baOutput(), vbUnicode), lBytesRead)
            If sOutput <> vbNullString Then sTemp = sTemp & sOutput

        Loop

        'Close the handle to the process.
        Call CloseHandle(pi.hProcess)

    End If

    'Close the handle to the pipe.
    Call CloseHandle(hPipeRead)
    Call CloseHandle(hPipeWrite)

    'Return the output.
    ExecuteAndCapture = sTemp

End Function