# Mit Excel Modbus TCP Zählerwert täglich auslesen



## sochn0 (19 April 2021)

Hallo ich habe folgendes Problem, ich muss über Modbus TCP einen Zählerwert täglich in ein Excel Schreiben. Gibt es eine Möglichkeit dies driekt in Excel zu machen oder gibt es da vl schon was fertiges? Für Vorschläge wäre ich sehr dankbar LG Alex


----------



## Oberchefe (19 April 2021)

Darf es was kosten?

http://www.automatedsolutions.com/products/activex/communications/asmbtcp.asp


Evtl. klappt auch Freeware

https://sourceforge.net/projects/mblogic/


----------



## xfred343 (13 Mai 2021)

Mit Excel-VBA kein Problem, ich steuere mittlerweile 7 Beckhoff-Etagenverteiler in meinem Privathaus damit, war ursrpünglich als Prototyp gedacht und bin selbst erstaunt, wie gut das geht.


----------



## Ralle (14 Mai 2021)

xfred343 schrieb:


> Mit Excel-VBA kein Problem, ich steuere mittlerweile 7 Beckhoff-Etagenverteiler in meinem Privathaus damit, war ursrpünglich als Prototyp gedacht und bin selbst erstaunt, wie gut das geht.



Das war jetzt wieder so eine Antwort alá "Weißt du die Uhrzeit?" Antwort: "Ja!"
Darf ruhig etwas ausführlicher sien, also welche Bibliothek nutzt du dafür, vlt.  auch en Stück Code, etc.


----------



## oliver.tonn (14 Mai 2021)

Das sollte mit Hilfe von Windows Sockets 2  (Win Sock) gehen. Näheres kann ich leider nicht dazu beisteuern, aber bei Tante Google sind verschiedene Beiträge zu dem Thema zu finden.
Hier mal ein (vermutlich) hilfreiches Beispiel.
Und noch ein Beispiel.


----------



## xfred343 (14 Mai 2021)

Ja, wenn Interesse besteht, gibts hier gern weitere Infos - die von oliver zitierten Posts waren meine ersten Anfänge, mittlerweile ist alles recht verfeinert und rennt eben hochstabil; anfangs wollte ich sogar eigene angebliche Modbus ActiveX-Controls oder die Ostrosoft Winsock verwenden, alles gar nicht nötig wie sich später herausgestellt hat.
Wichtig ist, die Funktionen asynchron zu starten und mit einem gezielten Timing in einer Art Hauptschleife die Befehle solange wiederholen, bis der asynchrone Prozess fertig ist, 
bei den von mir verwendeten Beckhol KL9100 Modbus-Serverklemmen kann eine IP-Adresse read/write, aber bis zu 2 weitere IPs bzw. genauer Sockets können zusätzlich lesend zugreifen, was recht praktisch ist.


Schlüssel zu dem Ganzen ist die Standard Microsoft und bei Excel vorhandene wsock32.dll - sie hab ich gleich einmal so umgebaut, dass sie auch für die 64 Bit Excel-Variante funktionieren.
Public Declare PtrSafe Function Connect Lib "wsock32.dll" Alias "connect" (ByVal s As Long, addr As sockaddr_in, ByVal namelen As Long) As Long
Public Declare PtrSafe Function htons Lib "wsock32.dll" (ByVal hostshort As Long) As Integer
Public Declare PtrSafe Function inet_addr Lib "wsock32.dll" (ByVal cp As String) As Long
Public Declare PtrSafe Function recv Lib "wsock32.dll" (ByVal s As Long, ByVal buf As Any, ByVal buflen As Long, ByVal flags As Long) As Long
Public Declare PtrSafe Function Send Lib "wsock32.dll" Alias "send" (ByVal s As Long, buf As Any, ByVal buflen As Long, ByVal flags As Long) As Long
Public Declare PtrSafe Function socket Lib "wsock32.dll" (ByVal af As Long, ByVal socktype As Long, ByVal protocol As Long) As Long
Public Declare PtrSafe Function bind Lib "wsock32.dll" (ByVal socket As Long, Name As sockaddr_in, ByVal nameLength As Long) As Long
Public Declare PtrSafe Function ioctlsocket Lib "wsock32.dll" (ByVal s As Long, ByVal cmd As Long, argp As Long) As Long

In einer eigenen Modbus-Klasse (clsModbus) habe ich dann die eigentlichen Befehle eingebaut, die immer wieder die gleiche Schleife aufrufen,
z.B.:

Public Sub s3ReadHoldingRegisters(lngStartAddress, lngBytes)
strCommand = Chr(0) & Chr(bytClient) & Chr(0) & Chr(0) & Chr(0) & Chr(6) & _
                    Chr(bytClient) & Chr(3) & _
                    Chr(Int(lngStartAddress / 256)) & Chr(lngStartAddress Mod 256) & _
                    Chr(0) & Chr(lngBytes)
Call sInternalProcess
End Sub

Besonders lang hab ích beim sInternalProcess getüftelt, bis Timing usw. gepasst hat,
wenn  Interesse besteht: ich könnt einmal ein fertiges VBA-Programm hier hochladen, dass zeigt, wie ich jetzt z.B. bei einem Beckhoff KL9100 die Holding-Register auslesen kann.


----------



## funkey (17 Mai 2021)

xfred343 schrieb:


> wenn  Interesse besteht: ich könnt einmal ein fertiges VBA-Programm hier hochladen, dass zeigt, wie ich jetzt z.B. bei einem Beckhoff KL9100 die Holding-Register auslesen kann.



Würde ich mir gerne einmal anschauen, danke!


----------



## xfred343 (17 Mai 2021)

Ja, gern - hab jetzt von meiner Steuerung ein Exzerpt erstellt - kommt mit reinem Excel und 2 APIs (winsocket und gettickcount) aus!
Abfragezyklus ca. 20-30 mSec, also ziemlich intensiv - man kann testweise das Netzwerkkabel ab- und wieder anstecken, den Beckhoff ausschalten und wieder einschalten, geht alles.. 


Viel Spaß beim Testen (auf eigene Gefahr, nicht dass ihr eine Beckhoff-Steuerung lahmlegt oder versehentlich ausschaltet ;-))


----------



## funkey (18 Mai 2021)

Vielen Dank läuft sehr gut.
Meine Frage wäre hier noch: Kann ich mit VBA oder Excel direkt jeweils zwei Integer als Real-Wert darstellen? Google lässt mich da im Stich. Ich finde dazu gar nichts. In C würde das z.B. mit einem union funktionieren. Hier habe ich keine Ahnung.


----------



## funkey (18 Mai 2021)

Hab's herausgefunden:
https://www.ozgrid.com/forum/index....te-array-containing-floating-point-to-single/

Nun kann ich super in Excel Werte von meinen WMZ und Sentron PAC3200 anzeigen.


----------



## xfred343 (18 Mai 2021)

Ah, interessanter Lösungsansatz - bin grad dabei, meine etwas mühsamen String-Arrays für die Modbus-Steuerung zu entfernen - insofern ist dein Themengebiet mit der UNION von C ganz interessant,
leider gibts zwar auch Pointer in VBA, nur Pointer von normalen Variablen gezielt zu ändern, geht wohl nur über das zitierte Beispiel mit LSET, dafür braucht man dann benutzerdefinierte Datentypen.

Bei der Gelegenheit hier auch gleich ein anderer Lösungsansatz für dein Problem, der ist näher an der Pointer-Lösung und man braucht keine benutzerdefinierten Datentypen und sollte im Massentest auch schneller sein. 
Sie ist ideal geeignet, da meist von den SPS Bytestreams kommen (VBA: Bytearrays), die  kann man hier recht gut parsen..

Was mich jetzt selbst etwas wundert ist, dass ich ohne VarPtrArray auskomme..

Option Explicit
' Declare the memory copying function.
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias _
    "RtlMoveMemory" (ByVal Destination As LongPtr, ByVal _
    Source As LongPtr, ByVal Length As Integer)
'Private Declare PtrSafe Function VarPtrArray Lib "VBE7" Alias _
'    "VarPtr" (ByRef Var() As Any) As LongPtr
Sub sTest()
Dim ab(7) As Byte 'kann beliebiges Bytearray sein
ab(0) = 1
ab(1) = 2
ab(2) = 3
ab(3) = 4
ab(4) = 5
ab(5) = 6
ab(6) = 7
ab(7) = 8
Debug.Print "sngSM 0-3=" & fxByte2Sng(ab)
Debug.Print "sngSM 4-7=" & fxByte2Sng(ab, 4)
End Sub


Function fxByte2Sng(ByRef ab() As Byte, Optional abPosition As Byte = 0) As Single
' Converts the 4-byte, abPosition-based, Big-Endian array in ab to a Single r
Dim i As Byte
Dim r As Single
Dim adr As LongPtr
Dim adrArray As LongPtr
adrArray = VarPtr(ab(abPosition)) 'hier sind einfach auch Offsets z.B. vom 4.-7. Bytes (abPosition=4) möglich
'einfacher wärs wenn die Reihenfolge gleich wäre
'dann spart man sich sogar die Schleife
'Call CopyMemory(adr, adrArray, 4)
adr = VarPtr(r)
For i = 0 To 3 ' Little-Endian Single storage order
    Call CopyMemory(adr + 3 - i, adrArray + i, 1)
Next i
fxByte2Sng = r
End Function


----------



## funkey (18 Mai 2021)

Sieht gut aus*vde*
Nur noch das Byte-Array anstelle des Strings wäre ideal, wie du bereits gesagt hast.


----------



## Heinileini (18 Mai 2021)

funkey schrieb:


> Meine Frage wäre hier noch: Kann ich mit VBA oder Excel direkt jeweils zwei Integer als Real-Wert darstellen?


Hab's mit nur 1 Integer (alias DINT bzw. 'Long') und 1 Real-Wert (alias REAL bzw. Single) und mit userdefined Variablen und 'LSet' probiert.
Es funktioniert (sobald man das, was man meint, auch so geschrieben hat, dass VBA es versteht  ).

Über die kleinen und grossen Indianer habe ich mir keinen Kopf gemacht und bin prompt auf die Schnauze gefallen. Die DoppelWort TypeCasts REAL<->DINT funktionieren natürlich.
Aber bei der Aufteilung der DoppelWorte in 4 EinzelBytes muss man die Reihenfolge der EinzelBytes umkehren!

Ich unterstelle mal, dass Du das Wort "Integer" nur als "MengenAngabe" (= 16 Bits) gemeint hast, sonst müsste ich heftig protestieren. (Oben habe ich das Wort ja auch missbraucht, allerdings im Sinne von Ganzzahl in ZweierKomplementDarstellung mit ohne MengenAngabe.)


```
[COLOR=#00ff00]' VBA-Module(Excel, using WorksheetFunctions 'Dec2Hex' and 'Dec2Bin' for TestPrintout):[/COLOR]

Option Base 0

Type TESTdint
    DINT As Long
End Type

Type TESTreal
    REAL As Single
End Type

Type TESTarby
    ARBY(0 To 3) As Byte
End Type

Type TESTbyte
    BYTE0 As Byte
    BYTE1 As Byte
    BYTE2 As Byte
    BYTE3 As Byte
End Type

Sub testing()[COLOR=#00ff00] ' copy contents of userdefined variables via LSet (e.g. used for TypeCasts)
[/COLOR]
Dim nixR As TESTreal
Dim nixD As TESTdint
Dim nixA As TESTarby
Dim nixB As TESTbyte

nixR.REAL = 4 * Atn(1)[COLOR=#00ff00] ' Pi[/COLOR]

LSet nixD = nixR[COLOR=#00ff00] ' TypeCast REAL_TO_DINT[/COLOR]
Debug.Print
Debug.Print "REALoriginal: " & nixR.REAL
Debug.Print "DINTfromREAL: " & nixD.DINT

LSet nixR = nixD[COLOR=#00ff00] ' TypeCast DINT_TO_REAL[/COLOR]
Debug.Print "REALfromDINT: " & nixR.REAL

LSet nixB = nixR[COLOR=#00ff00] ' TypeCast REAL_TO_4_SEPARATE_BYTES[/COLOR]
Debug.Print "Bytes 0 to 3: " & WorksheetFunction.Dec2Hex(nixB.BYTE3, 2) & WorksheetFunction.Dec2Hex(nixB.BYTE2, 2) & " " & WorksheetFunction.Dec2Hex(nixB.BYTE1, 2) & WorksheetFunction.Dec2Hex(nixB.BYTE0, 2)
Debug.Print "Bytes 0 to 3: " & WorksheetFunction.Dec2Bin(nixB.BYTE3, 8) & " " & WorksheetFunction.Dec2Bin(nixB.BYTE2, 8) & " " & WorksheetFunction.Dec2Bin(nixB.BYTE1, 8) & " " & WorksheetFunction.Dec2Bin(nixB.BYTE0, 8)

LSet nixA = nixR[COLOR=#00ff00] ' TypeCast REAL_TO_4_ITEMS_OF_ARRAY_OF_BYTE[/COLOR]
Debug.Print
Debug.Print "ArrayOfByte : " & WorksheetFunction.Dec2Hex(nixA.ARBY(3), 2) & WorksheetFunction.Dec2Hex(nixA.ARBY(2), 2) & " " & WorksheetFunction.Dec2Hex(nixA.ARBY(1), 2) & WorksheetFunction.Dec2Hex(nixA.ARBY(0), 2)
Debug.Print "ArrayOfByte : " & WorksheetFunction.Dec2Bin(nixA.ARBY(3), 8) & " " & WorksheetFunction.Dec2Bin(nixA.ARBY(2), 8) & " " & WorksheetFunction.Dec2Bin(nixA.ARBY(1), 8) & " " & WorksheetFunction.Dec2Bin(nixA.ARBY(0), 8)

Strg = WorksheetFunction.Dec2Bin(nixA.ARBY(3), 8) & WorksheetFunction.Dec2Bin(nixA.ARBY(2), 8) & WorksheetFunction.Dec2Bin(nixA.ARBY(1), 8) & WorksheetFunction.Dec2Bin(nixA.ARBY(0), 8)
Debug.Print
Debug.Print "              " & ".3....:....2....:....1....:....0"
Debug.Print "String WoSep: " & Strg
Debug.Print
Debug.Print "IEEE-754    : " & ". 3....:.. ..2....:....1....:....0"
Debug.Print "ArrayOfByte : " & Mid(Strg, 1, 1) & " " & Mid(Strg, 2, 8) & " " & Mid(Strg, 10)
Debug.Print "              " & "S EEEEEEEE MMMMMMMMMMMMMMMMMMMMMMM"
Stop
End Sub
```
Sorry für die nix-sagenden Variablen- bzw. Type-Namen, die 'nix' enthalten ... mir war nix besseres eingefallen.  

Hier noch das, was das Progrämmle in den DirektBereich schreibt:

```
REALoriginal: 3,141593
DINTfromREAL: 1078530011
REALfromDINT: 3,141593
Bytes 0 to 3: 4049 0FDB
Bytes 0 to 3: 01000000 01001001 00001111 11011011

ArrayOfByte : 4049 0FDB
ArrayOfByte : 01000000 01001001 00001111 11011011

              .3....:....2....:....1....:....0
String WoSep: 01000000010010010000111111011011

IEEE-754    : . 3....:.. ..2....:....1....:....0
ArrayOfByte : 0 10000000 10010010000111111011011
              S EEEEEEEE MMMMMMMMMMMMMMMMMMMMMMM
```


----------

