Excel/PLC S7-300 connection lost during macro execution

[PierrePiot]

Hi Everyone,



I am currently trying to collect data from Siemens PLc S7-300 with Excel using Libnodave.dll

With the exemple of program from the web it has been easy to collect some datas.

But now I have to collect several times the same data, in order to have a graphic.



Most of the time (if not everytime) the first datas are collected correctly, and after a few seconds Excel freeze.



So I have tried to check the connection into every loop, and indeed after some loops (aleatory) I lose the connection.

After trying to restart the macro readFromPLC() I obtain the message "No route PLc, check connexion and settings"



Only way to establish the connexion again is to reboot Excel.

Is there any possibility to establish the connexion again without rebooting Excel?



If someone as an advice I would very helpfull,

Thanks in advance,



Here under the code:


_________________________________________________________________________________________________________________________

Private Function initialize(ByRef ph As Long, ByRef di As Long, ByRef dc As Long)

ph = 0
di = 0
dc = 0
Dim time2 As Long
initialize = -1
res = -1
peer$ = ActiveWorkbook.Worksheets("VarTab").Cells(2, 9)
ph = openSocket(102, peer$) ' for ISO over TCP
If (ph > 0) Then
     di = daveNewInterface(ph, ph, "IF1", 0, daveProtoISOTCP, daveSpeed500k)
     res = daveInitAdapter(di)
     If res = 0 Then
          MpiPpi = Cells(6, 5)
          dc = daveNewConnection(di, MpiPpi, ActiveWorkbook.Worksheets("VarTab").Cells(3, 9), ActiveWorkbook.Worksheets("VarTab").Cells(4, 9))
          res = daveConnectPLC(dc)
          If res = 0 Then
               initialize = 0
          End If
     End If
End If
End Function
_________________________________________________________________________________________________________________________
Private Sub cleanUp(ByRef ph As Long, ByRef di As Long, ByRef dc As Long)
If dc <> 0 Then
     res = daveDisconnectPLC(dc)
     Call daveFree(dc)
     dc = 0
End If
If di <> 0 Then
     res = daveDisconnectAdapter(di)
     Call daveFree(di)
     di = 0
End If
If ph <> 0 Then
     res = closePort(ph)
     res = closeSocket(ph)
     ph = 0
End If
End Sub
_________________________________________________________________________________________________________________________
Sub readFromPLC()
Dim ph As Long, di As Long, dc As Long, iRow As Integer, dbnum As String, addrOffset As String, addrBit As String, Sample As Integer
Dim TagName As String, TagAdress As String, TagType_array() As String

Sample = ActiveWorkbook.Worksheets("VarTab").Cells(14, 9).value + 2
res = initialize(ph, di, dc)
If res = 0 Then

     For iRow = 3 To Sample
     Faulty = False

     If iRow > 3 Then
          res = initialize(ph, di, dc)
     End If
     If res = 0 Then
          TagAdress = ActiveWorkbook.Worksheets("VarTab").Cells(3, 3).value
          TagType_array = Split(ActiveWorkbook.Worksheets("VarTab").Cells(3, 3).value, ".")
          dbnum = Replace(TagType_array(0), "DB", "")
          If UBound(TagType_array) > 1 Then
               addrOffset = Replace(TagType_array(1), "DBX", "")
               addrBit = TagType_array(2)
               res2 = daveReadBytes(dc, daveDB, dbnum, addrOffset, 1, 0)
               If res2 = 0 Then
                    Dim bfbyte As Byte
                    Dim bitStat As Integer
                    Dim bitPos As Byte
                    bitPos = CByte(addrBit)
                    bfbyte = daveGetU8(dc)
                    bitStat = bfbyte And 2 ^ bitPos

                    If bitStat > 0 Then
                         ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4) = True
                    Else
                         ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4) = False
                    End If
               Else
                    ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4) = "#####"
                    ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4).Interior.Color = RGB(255, 0, 0)
               End If
          Else

               If InStr(TagType_array(1), "DBD") > 0 Then
                    addrOffset = Replace(TagType_array(1), "DBD", "")
                    res2 = daveReadBytes(dc, daveDB, dbnum, addrOffset, 4, 0)
                    If res2 = 0 Then
                         ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4) = daveGetFloat(dc)
                    Else
                         ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4) = "#####"
                         ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4).Interior.Color = RGB(255, 0, 0)
                    End If
               ElseIf InStr(TagType_array(1), "DBW") > 0 Then
                    addrOffset = Replace(TagType_array(1), "DBW", "")
                    res2 = daveReadBytes(dc, daveDB, dbnum, addrOffset, 2, 0)
                    If res2 = 0 Then
                         ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4) = daveGetU16(dc)
                    Else
                         ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4) = "#####"
                         ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4).Interior.Color = RGB(255, 0, 0)
                    End If
                    ElseIf InStr(TagType_array(1), "DBB") > 0 Then
                    addrOffset = Replace(TagType_array(1), "DBB", "")
                    res2 = daveReadBytes(dc, daveDB, dbnum, addrOffset, 1, 0)
                    If res2 = 0 Then
                         ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4) = daveGetU8(dc)
                    Else
                         ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4) = "#####"
                         ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4).Interior.Color = RGB(255, 0, 0)
                    End If
               End If
          End If
     Else
          Faulty = True
          GoTo Fault
     End If

Fault: Call cleanUp(ph, di, dc)
     If Faulty = True Then
     ActiveWorkbook.Worksheets("VarTab").Cells(iRow, 4) = "FAULT"
     End If
     
     Next
Else
     MsgBox "No route PLC, check connection and settings"
     Call cleanUp(ph, di, dc)
End If

End Sub

 

[Bratt]

You can start by checking in the PLC if the connections closes properly?
If you look at module information in the PLC Ctrl+D there is a tab called communication. Assign you macro to a button and every time you press the button either the PG or OP communication counter should increase by one during the time you communicate and the decrease again as soon as the connection is shut down.

You can also use netstat on your computer to make sure that the connection closes properly.

If that is not the problem i think you should could consider a rewrite. Only call the initialize routine once when you open the spreadsheet and the clean up once when you close the spreadsheet. Use only connect and disconnect between calls.