locked
RE: I/O redirection from putty to Excel VBA RRS feed

  • Question

  • Hi all

    I am using putty to communicate to a device over the serial line. What I would like to do is to implement this in EXCEL vba.

    I have some experience of vba development but this looks a bit complicated!

    I have been able to launch putty and open the com port through the shell(..) command. I have also found out that I have to redirect the std I/O from putty to vba. This is where I am stuck and what I have found online hasn't worked so far. Any help is appreciated.

    Thanks

    Sunday, August 16, 2015 3:53 AM

All replies

  • The code below call a ping command and retrive the IO to excel(txtSaida control):

    Option Explicit
    
    Private Declare Function CreatePipe Lib "kernel32" ( _
        phReadPipe As Long, _
        phWritePipe As Long, _
        lpPipeAttributes As Any, _
        ByVal nSize As Long) As Long
    
    Private Declare Function ReadFile Lib "kernel32" ( _
        ByVal hFile As Long, _
        ByVal lpBuffer As String, _
        ByVal nNumberOfBytesToRead As Long, _
        lpNumberOfBytesRead As Long, _
        ByVal lpOverlapped As Any) As Long
    
    Private Declare Function CreateProcessA Lib "kernel32" ( _
        ByVal lpApplicationName As Long, _
        ByVal lpCommandLine As String, _
        lpProcessAttributes As Any, _
        lpThreadAttributes As Any, _
        ByVal bInheritHandles As Long, _
        ByVal dwCreationFlags As Long, _
        ByVal lpEnvironment As Long, _
        ByVal lpCurrentDirectory As Long, _
        lpStartupInfo As STARTUPINFO, _
        lpProcessInformation As PROCESS_INFORMATION) As Long
    
    Private Declare Function CloseHandle Lib "kernel32" (ByVal hHandle As Long) As Long
    
    Private Type SECURITY_ATTRIBUTES
        nLength As Long
        lpSecurityDescriptor As Long
        bInheritHandle As Long
    End Type
    
    Private Type STARTUPINFO
        cb As Long
        lpReserved As Long
        lpDesktop As Long
        lpTitle As Long
        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
    
    Private Type PROCESS_INFORMATION
        hProcess As Long
        hThread As Long
        dwProcessID As Long
        dwThreadID As Long
    End Type
    
    Private Const NORMAL_PRIORITY_CLASS = &H20&
    Private Const STARTF_USESTDHANDLES = &H100&
    Private Const STARTF_USESHOWWINDOW = &H1
    
    Private BufferRecebido As String
    
    Public Function ExecutarComando(Optional szComando As String) As String
        
        Dim pi As PROCESS_INFORMATION
        Dim si As STARTUPINFO
        Dim sa As SECURITY_ATTRIBUTES
        
        Dim lRetorno As Long, hPipeLeitura As Long
        Dim hPipeEscrita As Long
        Dim lBytesLidos As Long
        Dim Buffer As String * 256
    
        sa.nLength = Len(sa)
        sa.bInheritHandle = 1&
        sa.lpSecurityDescriptor = 0&
        lRetorno = CreatePipe(hPipeLeitura, hPipeEscrita, sa, 0)
        si.cb = Len(si)
        si.dwFlags = STARTF_USESTDHANDLES Or STARTF_USESHOWWINDOW
        si.hStdOutput = hPipeEscrita
        si.hStdError = hPipeEscrita
        lRetorno = CreateProcessA(0&, szComando, sa, sa, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, si, pi)
        CloseHandle hPipeEscrita
        BufferRecebido = vbNullString
        Do
            lRetorno = ReadFile(hPipeLeitura, Buffer, 256, lBytesLidos, 0&)
            BufferRecebido = BufferRecebido & Left(Buffer, lBytesLidos)
        Loop While lRetorno <> 0
        CloseHandle pi.hProcess: CloseHandle pi.hThread: CloseHandle hPipeLeitura
        ExecutarComando = BufferRecebido
    End Function
    
    Private Sub UserForm_Initialize()
        txtSaida.Text = ExecutarComando("ping 8.8.8.8")
    End Sub

    Adapt as needs.

    • Proposed as answer by André Santo Monday, August 17, 2015 7:33 PM
    Sunday, August 16, 2015 5:26 AM
  • Thanks for your reply Andre. I have tried it with putty and it works, sort of. I can open a serial port but I still can't send data to this port. Any bright ideas?

    Thanks

    Thursday, September 3, 2015 5:18 AM