Modbus TCP Excel VBA测试

最新推荐文章于 2024-10-21 20:55:11 发布

weixin_45217673

最新推荐文章于 2024-10-21 20:55:11 发布

阅读量3.5k

点赞数 2

本文链接：https://blog.csdn.net/weixin_45217673/article/details/119418381

版权

本文介绍了如何使用Excel VBA通过两种方法实现Modbus TCP协议获取数据。首先尝试了使用OSWINSCK控件，但由于“没有注册类”的错误未能成功。接着，采用Windows API调用ws2_32.dll函数成功实现了通信，测试时读取了一个保持寄存器并得到了返回值。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近正在研究用Excel VBA实现 modbus TCP取数据的功能，先是尝试用OSWINSCK控件的方式，之后又尝试了WindowsAPI尝数的方法。最后试下来用通过调用ws2_32.dll API函数的方法成功了，大体的过程如下：

oswinsck控件方法。
访问 http://ostrosoft.com/oswinsck.aspx 主页，非商业用途的话是免费使用的，有下载zip包的向导。我的测试平台是win10 64bit 企业版 10.0.19042 Build 19042,VBA Retail7.1 1108 ,下载exe文件后运行安装，在安装目录下有个文件夹：C:\Program Files (x86)\OSWINSCK ，里面是各种Demo程序，解压VBA的那个例子试着做了一个，代码也是借鉴了高人的。通过 Tools -> references 添加了 "OstroSoft Winsock Compent"的引用。

Option Explicit
Dim wsTCP As OSWINSCK.Winsock


'This example uses OstroSoft Winsock Component
'http://www.ostrosoft.com/oswinsck.asp

Private Sub CommandButton1_Click() ' Retrieve Data
On Error GoTo ErrHandler
  Dim sServer As String
  Dim nPort As Long
  Dim StartTime
  Dim Test As Excel.Application
'  Dim wsTCP As OSWINSCK.Winsock
  
 
  DoEvents
  nPort = 1100 ' See configuration in Do-More Designer
  ' Set the IP address of the PLC
'  sServer = Sheets("Sheet1").Range("B4").Value '"192.168.1.3"
  sServer = "10.107.242.118"
  RetrieveData = 1
  CommandButton1.BackColor = "&H0000FF00" ' Set colour to Green"

'Check to see if the object has been created. If not set wsTCP.
If SetObject = "" Then
Set wsTCP = CreateObject("OSWINSCK.Winsock")

wsTCP.Protocol = sckTCPProtocol
SetObject = 1
  End If

' Check the state of the TCP connection
'0 sckClosed connection closed
'1 sckOpen open
'2 sckListening listening for incoming connections
'3 sckConnectionPending connection pending
'4 sckResolvingHost resolving remote host name
'5 sckHostResolved remote host name successfully resolved
'6 sckConnecting connecting to remote host
'7 sckConnected connected to remote host
'8 sckClosing Connection Is closing
'9 sckError error occured

' If TCP is not connected, try to connect again.
If wsTCP.State <> 7 Then
    If (wsTCP.State <> sckClosed) Then
      wsTCP.CloseWinsock
    End If
    ' Open the connection
    wsTCP.Connect sServer, nPort
    StartTime = Timer ' Use the timer to determine if a connection cannot be made
    Do While ((Timer < StartTime + 2) And (wsTCP.State <> 7))
        DoEvents
    Loop
    If (wsTCP.State = 7) Then
    Else
       Exit Sub
    End If
End If

' If we are connected then request the information.
If (wsTCP.State = 7) Then
    MbusQuery = Chr(0) + Chr(1) + Chr(0) + Chr(0) + Chr(0) + Chr(

最低0.47元/天解锁文章