How to Implement Modbus TCP Protocol VBA Excel

We are using Modbus TCP protocol, reading ten registers in the PLC, and displaying a bar graph in Excel. e will use Visual Basic for Applications (VBA) to communicate with a PLC. Previously we have used VB6 to communicate Modbus TCP.


Sequence for Modbus TCP on Excel using VBA

The following steps will be done:

  1. Explain the Modbus TCP protocol
  2. Install OstroSoft Winsock Component
    – Winsock API Calls for communication on the network
  3. Develop the Excel and VBA application
    (Microsoft Excel 2010)
  4. Communicate to the PLC and sample code
    (Do-More Simulator)

 

What is Modbus TCP (Ethernet)

The Modbus TCP/IP or Modbus TCP is a protocol for communications over TCP/IP networks. This is done on port 502. Modbus TCP does not require a checksum calculation as lower layers provide checksum protection. You can think of this as a letter being sent, and Ethernet TCP/IP acts like an envelope for the Modbus Commands. I will not go into the details of the communication protocol, but here are some links to references:
Introduction to Modbus TCP/IP
Simply Modbus – Modbus TCP

Winsock Component – Excel VBA Modbus TCP

OstroSoft Winsock Component
OSWINSCK.dll is a wrapper for the Winsock API and helps programmers abstract from the complexity of API calls and focus on application functionality. Works with programming and scripting languages supporting COM.
You must download and install the OstroSoft Winsock Component on your computer.
For use with .NET, Visual Basic 4 or 5, Visual C++, ASP, VBA, VBScript, JavaScript, or any other language, supporting COM:
1. Download oswinsck.exe
Modbus TCP using VBA Excel 001-min

2. Run the downloaded file from Windows Explorer or command-line
Modbus TCP using VBA Excel 002-min
Hit OK
Modbus TCP using VBA Excel 003-min
I use the default directories where the program will be installed. Click the button to install.
Modbus TCP using VBA Excel 004-min
Leave the program group to the default, so I know what the program is after installation. Click continue.
Modbus TCP using VBA Excel 005-min
Click OK
The OstroSoft Winsock Component is now installed.

Start Microsoft Excel

Modbus TCP using VBA Excel 010-min
Select ‘Developer’ along with the top tabs.
Modbus TCP using VBA Excel 020-min

If the Developer tab is not present, then we must turn on the developer tab.
Select File | Options Modbus TCP using VBA Excel 021-minSelect ‘Customize Ribbon’Check the ‘Developer’ under Main Tabs. Modbus TCP using VBA Excel 023-min

Under the Developer menu. Select ‘Visual Basic’
Modbus TCP using VBA Excel 030-minThe Visual Basic Editor window will now be displayed. Ethernet visual basic assistance communication
From the menu – Tools | References
We can now add the OstroSoft Winsock Component to our application.
Select OK
Ethernet visual basic assistance communicationSelect Sheet1(Sheet1). Ethernet visual basic assistance communication

Visual Basic Code – Excel VBA for Modbus TCP

Now put the visual basic code in Sheet1(Sheet1)
Here is the code:

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

Option Explicit

Dim bytesTotal As Long
Dim sPage As String
Dim MbusQuery
Dim returnInfo
Dim wsTCPgdb
Dim WithEvents wsTCP As OSWINSCK.Winsock
Dim SetObject
Dim RetrieveData

Private Sub CommandButton1_Click() ‘ Retrieve Data
On Error GoTo ErrHandler
  Dim sServer As String
  Dim nPort As Long
  Dim StartTime
 
  DoEvents
  nPort = 502 ‘See configuration in Do-More Designer
  ‘Set the IP address of the PLC
  sServer = Sheets(“Sheet1”).Range(“B4″) ‘”192.168.1.3”
  RetrieveData = 1
  CommandButton1.BackColor = “&H0000FF00” ‘Set a color 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 the remote hostname
‘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 occurred

‘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(0) + Chr(0) + Chr(0) + Chr(0) + Chr(6) + Chr(0) + Chr(3) + Chr(0) + Chr(0) + Chr(0) + Chr(20)
    wsTCP.SendData MbusQuery ‘Send out the Modbus Information
    ‘Read the information
    ‘0000:    Transaction Identifier
    ‘0000:    Protocol Identifier
    ‘0006:    Message Length (6 bytes to follow)
    ’00:      The Unit Identifier
    ’03:      The Function Code (read MHR Read Holding Registers)
    ‘0000:    The Data Address of the first register
    ‘0020:    The number of bytes to read
   
    ‘Write the information
    ‘0000:    Transaction Identifier
    ‘0000:    Protocol Identifier
    ‘0009:    Message Length (6 bytes to follow)
    ’01:      The Unit Identifier
    ’16:      The Function Code (read Analog Output Holding Registers)
    ‘0000:    The Data Address of the first register
    ‘0001:    The number of registers to write
    ’02:      The number of data bytes to follow
    ‘0030     The number to put into the register
   
    ‘Note: Addresses are offset by 1
    ‘   Example: MHR1 = Address 0000
    ‘   Example: MHR30 = Address 0029
   
End If
  Exit Sub

ErrHandler:
  MsgBox “Error ” & Err.Number & “:” & Err.Description
End Sub
Private Sub CommandButton2_Click() ‘ Stop the communication
RetrieveData = 0
CommandButton1.BackColor = “&H8000000F”‘ Set the default color
End Sub

Private Sub wsTCP_OnDataArrival(ByVal bytesTotal As Long)
  Dim sBuffer
  Dim i
  Dim MbusByteArray(500)
  Dim h As Integer
  Dim txtSource
wsTCP.GetData sBuffer
  txtSource = txtSource & sBuffer
 
Dim j As Byte
returnInfo = “”
For i = 1 To bytesTotal
    wsTCP.GetData j, vbByte
    MbusByteArray(i) = Asc(Mid(sBuffer, i, 2))
    returnInfo = returnInfo & Asc(Mid(sBuffer, i, 2))
Next
 txtSource = returnInfo
 txtSource = Val(Str((MbusByteArray(10) * 256) + MbusByteArray(11)))
 Sheets(“Sheet1”).Range(“B10”) = Val(Str((MbusByteArray(10) * 256) + MbusByteArray(11)))
 Sheets(“Sheet1”).Range(“B11”) = Val(Str((MbusByteArray(12) * 256) + MbusByteArray(13)))
 Sheets(“Sheet1”).Range(“B12”) = Val(Str((MbusByteArray(14) * 256) + MbusByteArray(15)))
 Sheets(“Sheet1”).Range(“B13”) = Val(Str((MbusByteArray(16) * 256) + MbusByteArray(17)))
 Sheets(“Sheet1”).Range(“B14”) = Val(Str((MbusByteArray(18) * 256) + MbusByteArray(19)))
 Sheets(“Sheet1”).Range(“B15”) = Val(Str((MbusByteArray(20) * 256) + MbusByteArray(21)))
 Sheets(“Sheet1”).Range(“B16”) = Val(Str((MbusByteArray(22) * 256) + MbusByteArray(23)))
 Sheets(“Sheet1”).Range(“B17”) = Val(Str((MbusByteArray(24) * 256) + MbusByteArray(25)))
 Sheets(“Sheet1”).Range(“B18”) = Val(Str((MbusByteArray(26) * 256) + MbusByteArray(27)))
 Sheets(“Sheet1”).Range(“B19”) = Val(Str((MbusByteArray(28) * 256) + MbusByteArray(29)))

DoEvents
‘Determine if we retrieve the data again.
If RetrieveData = 1 Then
    Call CommandButton1_Click
End If
End Sub

Private Sub wsTCP_OnError(ByVal Number As Integer, Description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, CancelDisplay As Boolean)
  MsgBox Number & “: ” & Description
End Sub

Private Sub wsTCP_OnStatusChanged(ByVal Status As String)
  Debug.Print Status
End Sub


Note: The program utilizes the CHR and STR functions to convert the data from binary to ASCII and back.
The highest value of a byte of data is 256. This is why we have to multiply the highest significant byte with 256

Interface:
Go back to Sheet1, and we can now put what we want to see on the worksheet.

Note the following:
IP Address = B4
MHR 1 to 10 values located at B10 to B19
‘Stop Data’ – CommandButton2
‘Retrieve Data’ – CommandButton1

Ethernet visual basic assistance communication

 

Communication with the PLC

Start the Do-More Designer software.
Under the Project Browser, select ‘System Configuration.’
Ethernet visual basic assistance communication
Make a note of the IP address. If you are running the simulator, then this is automatically filled in.
Modbus TCP using VBA Excel 126-min
Ensure that the Enable Modbus/TCP Server is checked. Also, make sure that the TCP Port Number is 502.
Modbus TCP using VBA Excel 127-min

The sample PLC program will write values from 0 to 4000. These values will be put in MHR 1 to MHR 10.

Here is the first couple of rungs of the PLC program. It will use clock bit flags to increment the MHR 1 channel. When it reaches a value above 4000, a move instruction will put a 0 back into MHR 1.
If input X0 turns on, the value in XW0 will be moved into MHR1, and the previous clock bit will not be in effect. Discounts will be between 0 and 4096. (12-bit resolution)
Ethernet visual basic assistance communication

This is repeated with different internal clock bit flags up to MHR10.

Running the program will produce the following:
Ethernet visual basic assistance communication

As you can see, the Modbus TCP protocol is easy to implement with visual basic for applications.
Download the PLC program and Excel file.

Additional Information:
Excel – Conditional Movement of Data

Watch on YouTube: How to Implement Modbus TCP Protocol using VBA with Excel

How to Implement Modbus TCP Protoco...
How to Implement Modbus TCP Protocol using VBA with Excel

If you have any questions or need further information, please get in touch with me.
Thank you,
Garry



If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLCs are not challenging to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimals, Hexadecimal, ASCII, and Floating Points.

To get this free article, subscribe to my free email newsletter.


Use the information to inform other people how numbering systems work. Sign up now.

The ‘Robust Data Logging for Free’ eBook is also available as a free download. The link is included when you subscribe to ACC Automation.


72 thoughts on “How to Implement Modbus TCP Protocol VBA Excel”

    • I am not sure of the question that you are asking. What Modbus device are you connected to? (What are the settings of that device?)
      What function code/s are you using?
      Regards,
      Garry

      Reply
      • Hi Luca,
        In the Modbus protocol we specify the address and the number of registers to read. 32 bit addresses will be twice as long as 16. We would just specify the starting address and twice the amount of registers to read. Take the result and join them together.

            ‘ Read the information
            ‘0000:    Transaction Identifier
            ‘0000:    Protocol Identifier
            ‘0006:    Message Length (6 bytes to follow)
            ’00:      The Unit Identifier
            ’03:      The Function Code (read MHR Read Holding Registers)
            ‘0000:    The Data Address of the first register
            ‘0002:    The number of registers to read

        Regards,
        Garry

        Reply
  1. Great tutorial!

    Reading the addresses with the Excel file works perfect. I do however get the error “10053: Software caused connection abort.” after some time when i click the Stop Data button.

    Does the oswin socket have a timer that closes the connection when it is not used for some time? (meaning I should close the handler when i stop the data acc.)

    Reply
    • Hi Christian,
      You are correct. The oswinsck must be closed. I did forget to include this in the sample program.
      I would enter another timer that gets activated when the connection is closed.
      If (wsTCP.State <> sckClosed) Then
      wsTCP.CloseWinsock
      End If
      Thanks Christian,
      Garry

      Reply
      • Garry,
        I am getting “Compile error: Variable not defined”. In the code, “” is highlighted withing the line:
        returninfo = “”

        I am aslwo using Windows 7, Excel 2010.

        Any thoughts?

        Thank you,
        Justin

        Reply
          • Here are those few lines (copy and pasted):

            Dim j As Byte
            returninfo = “”
            For i = 1 To bytesTotal
            wsTCP.GetData j, vbByte
            MbusByteArray(i) = Asc(Mid(sBuffer, i, 2))
            returninfo = returninfo & Asc(Mid(sBuffer, i, 2))

          • Hi Justin,
            Here are the exact same lines copied and pasted below. Note the spelling of returnInfo not returninfo. If you keep the returninfo spelling then go back and change the following line of code:
            Dim returnInfo change to Dim returninfor

            returnInfo = “”
            For i = 1 To bytesTotal
            wsTCP.GetData j, vbByte
            MbusByteArray(i) = Asc(Mid(sBuffer, i, 2))
            returnInfo = returnInfo & Asc(Mid(sBuffer, i, 2))
            Next
            txtSource = returnInfo

  2. Hi Sir
    ACC Modbus TCP Connect Excel VBA with LS Industrial Systems (XGB_XBC) PLC
    We tested communication.
    It shows the following result values

    PLC value 1 ~ 128 -> Excel 1 ~ 128 The normal value is displayed
    PLC value 129 ~ 254 -> Excel 63 Only certain values are displayed
    PLC value 255 ~ 384 -> Excel 255 ~ 384 The normal value is displayed
    PLC values 385 to 510 -> Excel 319 Only certain values are displayed
    PLC value 511 to 640 -> Excel 511 to 640 The normal value is displayed
    PLC values 641 to 766 -> Excel 575 Only certain values are displayed

    Tell me what’s wrong with this pattern in Excel
    Thank you

    수정 제안하기

    Reply
    • Hi Ji
      Since information is being received in Excel, then all communication is working correctly. I would look at the address registers in the PLC to determine the register type (Binary, Hex, ASCII, Decimal, etc).
      See if there is a relationship between the register type and the information being returned in Excel.
      Regards,
      Garry

      Reply
  3. Can we use same VBA script for modbus RTU (serial) device over which is connected to TCPIP ethernet converter ?

    If yes then where we will put device ID, baud rate, parity, stop bit in your excel file and VBA script ?

    Reply
    • Hi Santosh,
      Yes you can use VBA script to communicate through the serial port Modbus RTU. Here is a link where you will find additional information.
      https://pps2.com/smf/index.php?topic=25.0
      You would specify the serial communications when opening the serial port. The device ID would be similar to the Modbus TCP example above.
      Hope this helps you out.
      Regards,
      Garry

      Reply
  4. Hi There,

    I really like this spread sheet you made but I cannot get it to work. I am trying to read 400001-400008 on a productivity 2000 plc. I have those locations setup as 32 bit integers but I could change that. Any differences between the two PLCs that could cause this not to work?

    Reply
  5. Hi I’m trying to read a single value from my Schneider power meter 5320; this has a Modbus over IP connection through Ethernet. The meters address is 255 with an IP address of 192.168.0.201.
    I apply the following parameters (below), but the only thing that happens is MHR 3 to MHR 5 flash up with values for less than a second..
    IP Address 192.168.0.201 Unit Ident. 254
    Transaction Ident. 0000 Function Code 03
    Protocol Ident. 0000 Address First Reg. 43060
    Message Length 0006 Number of Reg. 2

    Any ideas?

    Reply
    • Hi Adam,
      I have not programmed this unit that you are trying to communicate. The address that you specify is not one that is listed in the Modbus addresses of the unit.
      https://www.schneider-electric.ca/en/faqs/FA234017/
      You will see an excel file with the Modbus addresses. (PM5100_PM5300_ModbusRegisterList.xls)
      I would try one of the other addresses. If that fails then I would look for another device to try your communications to the unit. This will try to eliminate where the problem resides.
      Hope this helps you out.
      Regards,
      Garry

      Reply
  6. Hi,
    Im trying to read from the Excel to the PLC, now i can read from the PLC to the Excel, what i need to change in the code?

    Thank you.

    Reply
    • Hi Mary,
      You would need to change the function code from 3 to 16 and set the appropriate registers.
          ‘ Write the information
          ‘0000:    Transaction Identifier
          ‘0000:    Protocol Identifier
          ‘0009:    Message Length (6 bytes to follow)
          ’01:      The Unit Identifier
          ’16:      The Function Code (write Analog Output Holding Registers)
          ‘0000:    The Data Address of the first register
          ‘0001:    The number of registers to write
          ’02:      The number of data bytes to follow
          ‘0030     The number to put into the register
      Regards,
      Garry

      Reply
  7. Hi,
    I previously had program running with much success for over two years on a windows 7 machine, however I recently upgraded to windows 10 pro and latest version of excel with VBA 7.1 on the same machine and I can not get automation to run. I receive error 429: active x component can’t create object. I tried to register manually regsvr32 oswinsck.dll and receive error oswinsck.dll may not be compatible with x86 or x64 version of regsvr32. Any ideas on how to resolve this problem. Thank you.

    Reply
    • Hi Jim,
      Did you install the 64 bit or 32 bit version of Excel? Use the 32-bit Office. Switching from 64-bit Office will require an uninstall and reinstall. You will then need to reinstall oswinsck.
      Let me know how you make out.
      Regards,
      Garry

      Reply
      • My Lord, I’ve spent countless hours on trying to resolve the problem and it’s now working. Who would have thought to run the 32 bit version on a 64 machine.

        Thank you! Happy Holidays!!

        Reply
  8. Hello Garry;

    I’ve tested pulling in registers from my Modbus PLC simulator into Excel and have had great success thanks to your tutelage.

    I am now attempting to write directly to the same simulator Holding Registers using the example for the MbusQuery:
    ‘ Write the information
    ‘0000: Transaction Identifier
    ‘0000: Protocol Identifier
    ‘0009: Message Length (6 bytes to follow)
    ’01: The Unit Identifier
    ’16: The Function Code (read Analog Output Holding Registers)
    ‘0000: The Data Address of the first register
    ‘0001: The number of registers to write
    ’02: The number of data bytes to follow
    ‘0030 The number to put into the register

    I am using the following command: MbusQuery = Chr(0) + Chr(0) + Chr(0) + Chr(0) + Chr(0) + Chr(9) + Chr(0) + Chr(16) + Chr(0) + Chr(1) + Chr(0) + Chr(1) + Chr(0) + Chr(2) + Chr(0) + Chr(30)

    BTW – I have found that I cannot get it to read successfully if I include The Unit Identifier.

    Every time I initiate the macro, I get 512 in the register even though I change the Chr(30) to other values. I’ve looked for detailed syntax structure for MbusQuery but have been unable to track it down. Any ideas on what I am doing wrong here? Thanks!

    Reply
  9. Hello,

    I whant read float values but only can read integer registers, houw can I do that if the registers are floating.

    Thanks

    Reply
    • Hi Jacob,
      Yes, this code will work for pulling data from a Productivity 2000 PLC. See the other comments about getting floating-point data.
      Depending on how you will be using the data, you could also use AdvancedHMI.
      This was demonstrated in the following post from the Productivity 1000 series.
      https://accautomation.ca/productivity-1000-series-plc-advancedhmi-modbus-tcp-ethernet-communication/
      Here is the entire P1000 Series link:
      https://accautomation.ca/series/productivity-1000-plc/
      Regards,
      Garry

      Reply
      • Garry,

        I was able to get it to start pulling data from the P2000, thank you.

        I am now trying to pull in a large array but am having trouble getting it to read over 125 integers. If I change to code to this I can read 125.

        MbusQuery = Chr(0) + Chr(0) + Chr(0) + Chr(0) + Chr(0) + Chr(6) + Chr(0) + Chr(3) + Chr(0) + Chr(0) + Chr(0) + Chr(125)

        I thought I would just have to change it like this to get more, but when I try it my whole all my data changes to zeros, which is what the rest of end of the array has in it, so I am thinking that the data is getting overwritten.

        MbusQuery = Chr(0) + Chr(0) + Chr(0) + Chr(0) + Chr(0) + Chr(6) + Chr(0) + Chr(3) + Chr(0) + Chr(0) + Chr(10) + Chr(0)

        Thanks,

        Reply
        • Hi Jacob,
          The maximum number of bytes in a message size for Modbus is 256. This would explain the 125 limit that you experienced.
          You will have to break the program up to read the maximum of 125 integers each time.
          Congratulations on getting it working!
          Garry

          Reply
  10. Hi Garry,
    Have you tried the maximum address?
    I figure out the maximum address is 3380. Pulling the data above this address will not work.
    I need to pull data starting at address 3881. I wonder if using a developer license will work

    Thanks

    Reply
    • Hi Lizwan,
      I have not tried the maximum addresses. The OstroSoft Winsock API Calls for communication on network. This should not have anything to do with the maximum address. I would check the Hardware that you are communicating to with Modbus TCP to see if a limit has been put on the device.
      Regards,
      Garry

      Reply
  11. Hi garrys,

    Is it possible to configure excel VBA to communicate as a slave/server using Modbus TCP?
    I plan to connect excel VBA to multiple HMI over Ethernet without a PLC. Can it be done?

    The HMI’s act as masters/clients to export data to excel.

    Thanks,
    SKLim

    Reply
    • Hi Lim Sai Keong,
      It would be possible to configure excel VBA to communicate as a slave/server using Modbus TCP however, I do not understand the reason why.
      Excel is just a spreadsheet.
      I would look at AdvancedHMI. This is a VB.Net program running on visual studio. It can communicate directly to an Excel spreadsheet.
      https://www.tutorialspoint.com/vb.net/vb.net_excel_sheet.htm
      In a typical application, the HMI would collect information and store in a database. Once in a database, excel can then connect and manipulate the information for display.
      I hope this helps you out.
      Regards,
      Garry

      Reply
  12. Hi Garry
    Your article is really instructive. But I can’t seem to put your program reading a device with 16 discrete inputs.
    The first address is 10001 and the function is 02. With other software I send a string:
    00 E1 00 00 00 06 01 02 00 00 00 10
    and get
    00 E1 00 00 00 05 01 02 02 20 00
    That is what I expect – input 6 is ON.
    With your program, I have coonnection but the values don’t change!
    What am I doing wrong?
    Thank you so much
    Amílcar – Portugal

    Reply
    • Hi Amilcar,
      Are you getting an error code back?
      Check what you are sending out to ensure that you have accounted for all of the bytes.
      Let me know how you make out.
      Regards,
      Garry

      Reply
  13. Hello Garry, thank you for the prompt answer.
    There are no errors. If the device is disconnected I get a timeout error. The slave ID of the device is 1. These are the details on Excel:
    IP Address 192.168.10.201
    Transaction Ident. 0000
    Protocol Ident. 0000
    Message Length 0005
    Unit Ident. 00
    Function Code 02
    Address First Reg. 0000
    Number of Reg. 0010
    My knowledge of this is very limited.
    Thank you so much for your help.
    Amilcar

    Reply
    • Hi Amilcar,
      Since you have no error messages coming back, I am thinking that it is just an interpretation of the data that is not correct.
      If you change the function code to read words like the example in the post, will the correct information be displayed?
      If the correct information is returned then, look at the returning information. This code would change depending on the bit status and not the register information.
      Regards,
      Garry

      Reply
  14. Hello, Garry

    Thank you for a nice program example! However, I does not seem to get it to work. I am sending a request to a Modbus TCP unit and the communication itself is ok, but I do only get 0 as an answer.
    I have tested my Modbus package that I send with a trial version of a Modbus client and it works fine. The textstring that I am sending is this:

    000100000006010303F30010

    I am reading holding registers where 03F3 is the address and function code is 03.

    In your code the string is sent but I only get 0 back.
    How would it look using Chr(…) syntax?

    Kind regards!

    Matt

    Reply
    • Hi Matt,
      I would try sending the address as a decimal value instead of a hex. 3F3 = 1011
      Let me know how you make out.
      Regards,
      Garry

      Reply
  15. Thanks for very quick reply, Gerry!
    However, it does not work.

    All I am receiving is 0 though I know that the value in the address should be 83, which I see in the display on my hardware unit.

    I am trying to read a holding register in our system with the address 401012.

    I use the send function as below:

    wsTCP.SendData “000100000006010310110001”

    0001 – Transaction identifier
    0000 – Protocol Id
    0006 – Message length
    01 – Unit Identifier
    03 – Function code
    1011 – First data address
    0001 – Number of registers to read

    The rest f the code for receiving is as your example.

    Thanks on befiorehand!

    Matt

    Reply
  16. Hello,
    I would need some help. I can see that you are familiar with DOMore PLC’S
    Script seems not to be working with signed integer.
    I translate memory from DoMore D-Memory into MHR1 and MHR2 as signed DWord, BYte and Word Swap. And it works. When I have look in Data View It shows right data. But when VBA/excel reads it , as soon as it has negative value, it reads it in to something else. Would you be able to give me some example how to work that one out. It would be much appreciated.
    Thank you

    Reply
  17. Hi Garry!!

    I’m using your example to test a polling to near 150 PLCs. Is there any way to shorten the timeout (10060: Connection timed out)? I need that beacuse my problem is that when one of the PLCs is disconnected the communication spends near 25 seconds to realize it isn’t working…

    Thanks a lot!!

    Alex

    Reply
  18. tried to use the excel in the download with Delta PLC
    starting address is D0 = 44097 (16 bit)
    IP address = 192.168.225.5
    but he trying to connect it shows
    Error: -2147221164 Class not registered.

    can you help me whit this
    OS: win 10 64bit
    excel 2016

    Reply
  19. Hello Garry,

    Can I have some more explanation on below coding pls ?

    Dim j As Byte
    returnInfo = “”
    For i = 1 To bytesTotal
    wsTCP.GetData j, vbByte
    MbusByteArray(i) = Asc(Mid(sBuffer, i, 2))
    returnInfo = returnInfo & Asc(Mid(sBuffer, i, 2))
    Next
    txtSource = returnInfo
    txtSource = Val(Str((MbusByteArray(10) * 256) + MbusByteArray(11)))
    Sheets(“Sheet1”).Range(“B10”) = Val(Str((MbusByteArray(10) * 256) + MbusByteArray(11)))
    Sheets(“Sheet1”).Range(“B11”) = Val(Str((MbusByteArray(12) * 256) + MbusByteArray(13)))
    Sheets(“Sheet1”).Range(“B12”) = Val(Str((MbusByteArray(14) * 256) + MbusByteArray(15)))
    Sheets(“Sheet1”).Range(“B13”) = Val(Str((MbusByteArray(16) * 256) + MbusByteArray(17)))
    Sheets(“Sheet1”).Range(“B14”) = Val(Str((MbusByteArray(18) * 256) + MbusByteArray(19)))
    Sheets(“Sheet1”).Range(“B15”) = Val(Str((MbusByteArray(20) * 256) + MbusByteArray(21)))
    Sheets(“Sheet1”).Range(“B16”) = Val(Str((MbusByteArray(22) * 256) + MbusByteArray(23)))
    Sheets(“Sheet1”).Range(“B17”) = Val(Str((MbusByteArray(24) * 256) + MbusByteArray(25)))
    Sheets(“Sheet1”).Range(“B18”) = Val(Str((MbusByteArray(26) * 256) + MbusByteArray(27)))
    Sheets(“Sheet1”).Range(“B19”) = Val(Str((MbusByteArray(28) * 256) + MbusByteArray(29)))

    Reply
    • Hi Adtapong,
      The For/Next loop will get the returned data and convert the information into ASCII characters. Information contained in the returned information will be put into an ASCII string called returnInfo.
      The remaining of the code will then convert the ASCII characters to a value that it represents.
      Most significant byte x 256 + Least significant byte = Value
      The information is then put on the Excel datasheet.
      I hope this helps you out.
      Regards,
      Garry

      Reply
  20. Hello,

    I have the same issue than Harish Nair : Class not registered
    I followed the procédure (http://www.ctimls.com/Support/KB/How%20To/Register_dll.htm) but I get an error message after the command : regsvr32 OSWINSCK.dll
    The error message is : The OWWINSCK.DLL may not be compatible with your windows current version. MAke sure your module is compatible with 32 or 64bit version
    Any help would be welcome !
    Thnaks a lot.
    Christophe

    Reply
  21. Hi, Great job!!!
    I’am using WIN 11 64 bit and Microsoft® Excel® per Microsoft 365 MSO (Versione 2206 Build 16.0.15330.20260) a 64 bit , but it doesn’t work….
    Any suggestion?

    Thanks
    Mauro

    Reply
    • Hi Mauro,
      This popular post was done several years ago. If I were to do this again today I would use Node-RED.
      https://accautomation.ca/series/node-red-iot-enabling-software/
      Node-RED is an IoT enabling software. The above link will take you to a series that will read information from the PLC using Modbus TCP, Show a Dashboard (HMI), Save it to a Database, and view the information in a spreadsheet (Like Excel).
      This is the preferred method that will work on any platform. Try it and you will be surprised at how this flow programming is easy to learn and implement.
      Regards,
      Garry

      Reply

Leave a Reply to Santosh Cancel reply