1

I have a measuring device attached to a Heidenhain ND221 display which can send it´s current value over RS232 serial data. I would like to read that data via excel vba to do some calculations. From the datasheet I know the serial settings (9600,E,7,2) and a terminal like putty displays the data correctly: "-___x.xxxx" (4 spaces between +/- and value)

I used the vba code I found here

#If VBA7 Then ' Excel 2010 or later
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
#Else ' Excel 2007 or earlier
    Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
#End If

Public Sub SerialPort()
    ' open a COM port, transmit a message, gather results, close the port.

    ' open the COM port as file #1
    Debug.Print "Open COM port 3"
    Open "COM3:9600,E,7,2" For Binary Access Read Write As #1

    transmit$ = Chr(2)

    ' transmit a message
    Put #1, , transmit$
    Debug.Print "Message sent."

    ' wait a bit for a response
    Sleep 100

    ' check for received message
    Debug.Print "Look for incoming message."
    On Error Resume Next

    Input #1, receive$
    Debug.Print receive$

    On Error GoTo 0

    ' close the serial port
    Debug.Print "Close COM port."
    Close #1

    Debug.Print "Done."
End Sub

I do receive data but it is missing the positiv or negative sign while still reading the 4 spaces in front. Is it the serial port configuration or the input # statement and how can I fix it? I also tried Get and Line Input with the same result.

Thanks!

Martin
  • 21
  • 4
  • The display is triggered by Ctrl+B or STX = Chr(2) to send its data. That works flawlessly. Debug.Print does not work with transmit$ since it is no printable character. – Martin Sep 02 '20 at 07:34

1 Answers1

1

ok, I found a workaround. I changed

input #1, receive$

to

Dim receive As String
receive = String(11, " ")
Get #1, , receive

The Get # statement did not show any data until I added the two lines before. (I know the data always has exactly 11 digits).

However initialisiing the receive variable as a string did not fix the Input # statement.

Only problem now is that the code freezes if you try to receive data while there is none available. See here. For example if the display is switched off the com port still exists and can be opened. So far I found no way the check if get will fail before doing so. And once it tries it cannot be stopped or timed out.

Martin
  • 21
  • 4