none
Excel Macro. Enviando comando para a porta serial e recebendo dados.

    Question

  • Ola pessoal, não sei nada de macros para excel.

    Precido de um modelo que faça o seguinte:

    Fazer a conexão com a porta serial.

    Enviar um comando.

    Receber a resposta.

    Depositar os valores recebidos no endereço X.

    Qualquer ajuda será muito apreciada.


    silvio pontes
    Thursday, November 24, 2011 12:30 AM

All replies

  • Prezado(a),
    Estou migrando seu post para o fórum de VBA.
    Por favor, das próximas vezes que tiver alguma dúvida relacionada a esse assunto, poste por lá.
    Obrigado.

    André Alves de Lima
    Microsoft MVP - Client App Dev
    Visite o meu site: http://www.andrealveslima.com.br
    Me siga no Twitter: @andrealveslima
    Thursday, November 24, 2011 9:26 PM
  • Obrigado Andre.



    silvio pontes
    Friday, November 25, 2011 12:24 AM
  • Sem saber nada de macros de Excel, complica um pouco. As rotinas para enviar e receber dados de portas seriais não são triviais.

    Você deverá usar o componente MSCOMM32.ocx.

    O exemplo mais simples que posso imaginar seria algo como mostra nesse link: http://www.pencomdesign.com/support/relay_software/vba_software_example.htm

    Para um exemplo extremamente elaborado, veja: http://dev.emcelettronica.com/serial-port-communication-excel-vba


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br
    Friday, November 25, 2011 5:20 PM
  • Ola amigo, 

    Estou usando o MSCOMM32.ocx e estou tendo problemas.

    Por este motivo estou procurando uma forma de não usar o MSCOMM32.ocx.

    Estou tentando usar o exemplo do link abaixo.

    http://dev.emcelettronica.com/serial-port-communication-excel-vba

    Se puder me ajudar?

    Ate a semana passada não sabia nada de macros.

    No sábado e domingo aprendi muito sobre macros.

    Ja não posso dizer mais que sou leigo em macros para excel.

    Meu foco agora, esta em colocar os códigos de acesso e leitura da porta serial dentro das macros.


    silvio pontes
    Monday, November 28, 2011 8:48 AM
  • Sobre o exemplo do link que você postou, há um link direto para baixar a Pasta de Trabalho, que é: http://dev.emcelettronica.com/files/serial%20port%20communication%20example_0.xls

    Qual é sua dúvida?


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br
    Monday, November 28, 2011 1:49 PM
  • Usando o MSCommMetex, tenho o seguinte código:

    Quero fazer a mesma coisa sem usar o MSCommMetex.

    Option Explicit
    
    Declare Function timeGetTime Lib "winmm.dll" () As Long
    
    '  Variable Input Force
    '  Assigning Shortcut Keys
    '  Measurement start    = Ctrl s
    '  Memory discharge     = Ctrl m
    '  Memory discharge     = Ctrl n
    '  Memory discharge     = Ctrl h
    '
    '  End of measurement   = Ctrl b
    
    Public PosCount As Integer   'Number of measurements
    Public RcvStr As String
    Public cnt1 As Integer
    
    
    
    Public Wei As Double
    
    Public t As Double
    Public tm As Double
    Public tma As Double
    Public flag As Boolean
        
    
    
    Sub Timer1()
    
        Dim x As Integer
        
        
    On Error Resume Next
        
        x = 1
        Wei = UserFormMetex31.TextBox1
        flag = True
    
        Do
            UserFormMetex31.lbl1 = t
            UserFormMetex31.lbl2 = tm
            UserFormMetex31.lbl3 = t - tm
        
            DoEvents
            
            t = timeGetTime
            
            If t - tm >= Wei Then
            
            tm = t
    
     
    If flag = False Then Exit Do
    
        ActiveSheet.Cells(PosCount + 1, 1).Select
        
        'Text input mode, a received character 1, Input buffer clear
        UserFormMetex31.MSCommMetex.InputMode = comInputModeText
        UserFormMetex31.MSCommMetex.InputLen = 1
        UserFormMetex31.MSCommMetex.InBufferCount = 0
        
        If UserFormMetex31.MSCommMetex.PortOpen = False Then
            UserFormMetex31.MSCommMetex.PortOpen = True
        End If
        
        'COM port to output the test character
        UserFormMetex31.MSCommMetex.Output = "T01"
        UserFormMetex31.MSCommMetex.Output = Chr(&HD)
        
        RcvStr = ""
        'Call PutDcommand
            
            UserFormMetex31.lbl1 = t
            UserFormMetex31.lbl2 = tm
            UserFormMetex31.lbl3 = t - tm
            UserFormMetex31.lbl4 = x
            
            x = x + 1
        
            End If
       
            If flag = False Then Exit Do
        Loop
    
    End Sub
    
    Sub StartMeasure()           'Measuring, the macro measurement start
                                 'Ctrl s
                                 
        Static setting As String
        Static setting2 As String
         
    On Error Resume Next
    
        setting2 = [P5]
        setting = [P9]
          
          UserFormMetex31.MSCommMetex.CommPort = setting2
                                     
        PosCount = 1
        ActiveSheet.Cells(PosCount, 2).Value = "Date"
        ActiveSheet.Cells(PosCount, 3).Value = "Time"
        ActiveSheet.Cells(PosCount, 4).Value = "ID"
        ActiveSheet.Cells(PosCount, 5).Value = "Turbidity"
        ActiveSheet.Cells(PosCount, 6).Value = "ID"
        ActiveSheet.Cells(PosCount, 7).Value = "3.0μm~"
        ActiveSheet.Cells(PosCount, 8).Value = "5.0μm~"
        ActiveSheet.Cells(PosCount, 9).Value = "7.0μm~"
        ActiveSheet.Cells(PosCount, 10).Value = "ID"
        ActiveSheet.Cells(PosCount, 11).Value = "Turbidity"
        ActiveSheet.Cells(PosCount, 12).Value = "ID"
        ActiveSheet.Cells(PosCount, 13).Value = "3.0μm~"
        ActiveSheet.Cells(PosCount, 14).Value = "5.0μm~"
        ActiveSheet.Cells(PosCount, 15).Value = "7.0μm~"
      
            ActiveSheet.Cells(PosCount + 1, 1).Select
        
        'Text input mode, a received character 1, Input buffer clear
        UserFormMetex31.MSCommMetex.InputMode = comInputModeText
        UserFormMetex31.MSCommMetex.InputLen = 1
        UserFormMetex31.MSCommMetex.InBufferCount = 0
        
        If UserFormMetex31.MSCommMetex.PortOpen = False Then
            UserFormMetex31.MSCommMetex.PortOpen = True
        End If
        
        If setting = "s" Then
          UserFormMetex31.TextBox1 = 30000
        End If
        
        If setting = "m" Then
          UserFormMetex31.TextBox1 = 60000
        End If
         
        If setting = "n" Then
          UserFormMetex31.TextBox1 = 600000
        End If
        
        If setting = "h" Then
          UserFormMetex31.TextBox1 = 3600000
        End If
                
             Timer1
        tm = timeGetTime
            
    End Sub
    Sub BraekMeasure()    'Macro to end the measurement
                          'Ctrl b
        Dim rc As Integer
         flag = False
         
       'Text input mode, a received character 1, Input buffer clear
        UserFormMetex31.MSCommMetex.InputMode = comInputModeText
        UserFormMetex31.MSCommMetex.InputLen = 1
        UserFormMetex31.MSCommMetex.InBufferCount = 0
        
        If UserFormMetex31.MSCommMetex.PortOpen = False Then
           UserFormMetex31.MSCommMetex.PortOpen = True
        End If
        
        'COM port to output the test character
        UserFormMetex31.MSCommMetex.Output = "run"
        UserFormMetex31.MSCommMetex.Output = Chr(&HD)
        
        RcvStr = ""
        'Call PutDcommand
         
        If UserFormMetex31.MSCommMetex.PortOpen = True Then
           UserFormMetex31.MSCommMetex.PortOpen = False
        End If
        rc = MsgBox("Exit the measurement" _
                    , vbOKOnly, "Measurement break")
     
    End Sub
    
    Public Function SetCellData(data As String)    'Measurement function
        
        Dim monr As String
        Dim hour As String
        Dim id   As String
        Dim id2  As String
        
        Dim dat1 As String
        Dim dat2 As String
        Dim dat3 As String
           
    On Error Resume Next
    
        monr = Date
        hour = Time
        id = Val(Mid(data, 2, 3))
        id2 = Val(Mid(data, 1, 2))
        
        If id = 1 Then
         dat1 = Val(Mid(data, 37, 41)) / 10000
        
        'To enter the cell. Number of measurements, time, DMM data
        ActiveSheet.Cells(PosCount, 2).Value = monr
        ActiveSheet.Cells(PosCount, 3).Value = hour
        ActiveSheet.Cells(PosCount, 4).Value = id
        ActiveSheet.Cells(PosCount, 5).Value = dat1
    
        'The next input position
        'Text input mode, a received character 1, Input buffer clear
        UserFormMetex31.MSCommMetex.InputMode = comInputModeText
        UserFormMetex31.MSCommMetex.InputLen = 1
        UserFormMetex31.MSCommMetex.InBufferCount = 0
        
        If UserFormMetex31.MSCommMetex.PortOpen = False Then
            UserFormMetex31.MSCommMetex.PortOpen = True
        End If
        
        'COM port to output the test character
        UserFormMetex31.MSCommMetex.Output = "id02"
        UserFormMetex31.MSCommMetex.Output = Chr(&HD)
        
        RcvStr = ""
    
        End If
        
        If id2 = 2 Then
        dat1 = Val(Mid(data, 4, 8))
        dat2 = Val(Mid(data, 10, 14))
        dat3 = Val(Mid(data, 16, 20))
        
        'To enter the cell. Number of measurements, time, DMM data
        ActiveSheet.Cells(PosCount, 6).Value = id2
        ActiveSheet.Cells(PosCount, 7).Value = dat1
        ActiveSheet.Cells(PosCount, 8).Value = dat2
        ActiveSheet.Cells(PosCount, 9).Value = dat3
    
        'The next input position
        'Text input mode, a received character 1, Input buffer clear
        UserFormMetex31.MSCommMetex.InputMode = comInputModeText
        UserFormMetex31.MSCommMetex.InputLen = 1
        UserFormMetex31.MSCommMetex.InBufferCount = 0
        
        If UserFormMetex31.MSCommMetex.PortOpen = False Then
            UserFormMetex31.MSCommMetex.PortOpen = True
        End If
        
        'COM port to output the test character
        UserFormMetex31.MSCommMetex.Output = "T03"
        UserFormMetex31.MSCommMetex.Output = Chr(&HD)
        
        RcvStr = ""
    
        End If
           
         If id = 3 Then
         dat1 = Val(Mid(data, 37, 41)) / 10000
        
        'To enter the cell. Number of measurements, time, DMM data
    
        ActiveSheet.Cells(PosCount, 10).Value = id
        ActiveSheet.Cells(PosCount, 11).Value = dat1
    
        'The next input position
        'Text input mode, a received character 1, Input buffer clear
        UserFormMetex31.MSCommMetex.InputMode = comInputModeText
        UserFormMetex31.MSCommMetex.InputLen = 1
        UserFormMetex31.MSCommMetex.InBufferCount = 0
        
        If UserFormMetex31.MSCommMetex.PortOpen = False Then
            UserFormMetex31.MSCommMetex.PortOpen = True
        End If
        
        'COM port to output the test character
        UserFormMetex31.MSCommMetex.Output = "id04"
        UserFormMetex31.MSCommMetex.Output = Chr(&HD)
        
        RcvStr = ""
    
        End If
        
        If id2 = 4 Then
        dat1 = Val(Mid(data, 4, 8))
        dat2 = Val(Mid(data, 10, 14))
        dat3 = Val(Mid(data, 16, 20))
        
        'To enter the cell. Number of measurements, time, DMM data
        ActiveSheet.Cells(PosCount, 12).Value = id2
        ActiveSheet.Cells(PosCount, 13).Value = dat1
        ActiveSheet.Cells(PosCount, 14).Value = dat2
        ActiveSheet.Cells(PosCount, 15).Value = dat3
    
        'The next input position
    
        End If
           
    End Function
    Private Sub Wait(PauseTime)
    'Here, the specified number of seconds to pause program execution
    
        
    On Error Resume Next
    
    Start = Timer ' Set the start time of the interruption
    Do While Timer < Start + PauseTime
    DoEvents ' Pass control to other processes
    Loop
    End Sub
    
    



    silvio pontes
    Tuesday, November 29, 2011 1:43 AM