You are not registered yet. Please click here to register!


 
 
plc storereviewsdownloads
This board is for PLC Related Q&A ONLY. Please DON'T use it for advertising, etc.
 
Try our online PLC Simulator- FREE.  Click here now to try it.

New Here? Please read this important info!!!


Go Back   PLCS.net - Interactive Q & A > PLCS.net - Interactive Q & A > LIVE PLC Questions And Answers

Reply
 
Thread Tools Display Modes
Old November 8th, 2015, 03:51 PM   #1
gclshortt
Member
Canada

gclshortt is offline
 
gclshortt's Avatar
 
Join Date: Dec 2014
Location: Ontario
Posts: 3,219
Talking How to Implement Modbus TCP Protocol Using VBA with Excel

Here is a post that I just finished. I thought that it would be worth sharing here.
Let me know what you think.
Thank you,
Garry
http://www.accautomation.ca

How to Implement Modbus TCP Protocol Using VBA with Excel

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

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

Read the rest of the post...
How to Implement Modbus TCP Protocol Using VBA with Excel

__________________
Garry
https://www.accautomation.ca
Connect with us on Facebook: facebook.com/accautomation/
  Reply With Quote
Old November 21st, 2016, 05:58 AM   #2
Daniel.Nilsson85
Member
Sweden

Daniel.Nilsson85 is offline
 
Join Date: Nov 2009
Location: Sweden
Posts: 2
Hi,
Do you know what to change in the code to read the coils instead?
i tried to change the function code to 01, but it didn't work.

Thanks in advance.

/Daniel
  Reply With Quote
Old November 21st, 2016, 09:20 AM   #3
gclshortt
Member
Canada

gclshortt is offline
 
gclshortt's Avatar
 
Join Date: Dec 2014
Location: Ontario
Posts: 3,219
Hi Daniel,
You are correct in just changing the function code to get the coils. However the Do-More Simulator and Do-More PLC do not support this function. Information can only be obtained through the MHR (Modbus Holding Registers) memory of the PLC using Modbus TCP. To get the bit status you must first transfer the bits to a MHR area in the PLC program.
Regards,
__________________
Garry
https://www.accautomation.ca
Connect with us on Facebook: facebook.com/accautomation/
  Reply With Quote
Old November 22nd, 2016, 12:15 AM   #4
Daniel.Nilsson85
Member
Sweden

Daniel.Nilsson85 is offline
 
Join Date: Nov 2009
Location: Sweden
Posts: 2
Thanks for a quick reply

I'm using the excel document to connect to another modbus unit, a mitsubishi PLC. I just changed the IP-address and it worked like a charm.

But I would like to use it to check the modbus coils from the PLC.
Do you know how to change the VBA code to get it to work with coils?

Thanks in advance

/Daniel
  Reply With Quote
Old November 22nd, 2016, 09:47 AM   #5
gclshortt
Member
Canada

gclshortt is offline
 
gclshortt's Avatar
 
Join Date: Dec 2014
Location: Ontario
Posts: 3,219
Here is the original 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
‘0002: The number of registers requested

If we want to read the coils this information would change to the following:

If (wsTCP.State = 7) Then
MbusQuery = Chr(0) + Chr(0) + Chr(0) + Chr(0) + Chr(0) + Chr(6) + Chr(0) + Chr(1) + Chr(0) + Chr(13) + Chr(0) + Chr(25)
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
’01: The Function Code (read Coil Status)
'0013: The Data Address of the first coil to read. ( 0013 hex = 19 , + 1 offset = coil #20 )
‘0025: The total number of coils requested. (25 hex = 37, inputs 20 to 56 )

Additional information can be obtained from the following url:
http://www.simplymodbus.ca/TCP.htm
The reading of the coil example is from the following url:
http://www.simplymodbus.ca/FC01.htm

Regards,
__________________
Garry
https://www.accautomation.ca
Connect with us on Facebook: facebook.com/accautomation/
  Reply With Quote
Old November 25th, 2016, 04:50 PM   #6
nichonj
Member
United States

nichonj is offline
 
Join Date: Nov 2016
Location: NJ
Posts: 3
Modbus Excel Parsing

Hi,
I used your ACC Modbus TCP example above and have my plc modbus server communicating with excel and is functioning as it should.

The problem I am trying to solve involves implementing a carriage return in the VBA code so that data is logged with a time stamp.

My project involves 3 separate platform scales that run live in R registers. The scale data streams continuous through serial connection. At intervals during the batching process contacts will move a frame of the scale data to a second memory location located within the MHR registers. The data is then sent to the excel example you provided.

Once the data reaches the designated cell in excel I would like to then have that data copied to a second sheet or even an entirely different workbook where the data can be logged within a new cell every time the data is captured. In addition I would require a time stamp and a carriage return so that the next block of data will automatically be placed in the cell below the previous cell data.

Any and all help with coding this is greatly appreciated. I really don't have any experience with VBA.

Thanks,
  Reply With Quote
Old November 26th, 2016, 05:58 AM   #7
gclshortt
Member
Canada

gclshortt is offline
 
gclshortt's Avatar
 
Join Date: Dec 2014
Location: Ontario
Posts: 3,219
The following is inserting a carriage return:
activecell.FormulaR1C1 = activecell.FormulaR1C1 & Chr(10) & "MEASURE_1"

The Chr(10) inserted in the string is actually the carriage return.

This link will show you how to do conditional movement of data in the excel sheet.
http://accautomation.ca/excel-condit...ement-of-data/

Hope this helps you out.
Regards,
__________________
Garry
https://www.accautomation.ca
Connect with us on Facebook: facebook.com/accautomation/
  Reply With Quote
Old November 26th, 2016, 12:09 PM   #8
nichonj
Member
United States

nichonj is offline
 
Join Date: Nov 2016
Location: NJ
Posts: 3
Hi Gary,
Thank you for the quick reply. I specified a carriage return would be needed, however what I actually meant was a line feed, so that the courser moves below to the next cell down. I have tried the code below but not working.

Thanks again.
  Reply With Quote
Old November 26th, 2016, 04:22 PM   #9
Toine
Member
Netherlands

Toine is offline
 
Join Date: Nov 2016
Location: NL
Posts: 387
This would not move your cursor down to the next cell. Instead, it would add an additional line to the text within the same cell.

If you want to move the cursor to the next cell, you do would not change the cell's content but instead select the other cell.

See if this works for you:

Code:
ActiveCell.Offset(1).Select
  Reply With Quote
Old November 26th, 2016, 07:51 PM   #10
gclshortt
Member
Canada

gclshortt is offline
 
gclshortt's Avatar
 
Join Date: Dec 2014
Location: Ontario
Posts: 3,219
http://stackoverflow.com/questions/9...rough-vba-code

Check out the above link.
text = "Hello" & vbCrLf & "World!"
or
text = "Hello" & Chr(13) & Chr(10) & "World!"

Regards,
__________________
Garry
https://www.accautomation.ca
Connect with us on Facebook: facebook.com/accautomation/
  Reply With Quote
Old January 7th, 2017, 10:10 AM   #11
nichonj
Member
United States

nichonj is offline
 
Join Date: Nov 2016
Location: NJ
Posts: 3
Gary,
I want to thank you for posting your Modbus VBA example. After two months of expanding on the example and learning vba code, I was able to create full automation of our batch control record keeping from information streaming from my Domore PLC. From soup to nuts I am able to track incoming material usage, outgoing orders, batch weights, label printing, assign batch control numbers, date and time stamp and the list goes on.

Keep up the great work and thank you for sharing your wealth of programming knowledge and providing this free example.

Best regards,
  Reply With Quote
Old January 9th, 2017, 08:24 AM   #12
gclshortt
Member
Canada

gclshortt is offline
 
gclshortt's Avatar
 
Join Date: Dec 2014
Location: Ontario
Posts: 3,219
Thank you nichonj.
You made my day!
__________________
Garry
https://www.accautomation.ca
Connect with us on Facebook: facebook.com/accautomation/
  Reply With Quote
Old June 15th, 2017, 07:51 AM   #13
ciao87
Member
Italy

ciao87 is offline
 
Join Date: Jan 2012
Location: Italy
Posts: 2
How to set or reset coil or write Holding Register

Hello, it's possible to write a value into Holding Register or put True or False a Coil?
  Reply With Quote
Old June 15th, 2017, 03:33 PM   #14
gclshortt
Member
Canada

gclshortt is offline
 
gclshortt's Avatar
 
Join Date: Dec 2014
Location: Ontario
Posts: 3,219
Yes this is possible as long as the device you are communicating to understands the command.
http://ostrosoft.com/oswinsck/oswinsck_reference.aspx
Here is a site with a good overview of the commands.
http://www.simplymodbus.ca/TCP.htm

Regards,
__________________
Garry
https://www.accautomation.ca
Connect with us on Facebook: facebook.com/accautomation/
  Reply With Quote
Old June 18th, 2017, 10:16 AM   #15
ciao87
Member
Italy

ciao87 is offline
 
Join Date: Jan 2012
Location: Italy
Posts: 2
Thanks.
  Reply With Quote
Reply
Jump to Live PLC Question and Answer Forum

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Topics
Thread Thread Starter Forum Replies Last Post
Modbus RTU/Modbus TCP. Terminal server? marcius LIVE PLC Questions And Answers 2 August 5th, 2013 08:36 AM
ML1400 as a modbus tcp slave? cuffers65240 LIVE PLC Questions And Answers 6 September 15th, 2011 11:56 PM
Modbus TCP and Omron CJ PLC Communication drunken LIVE PLC Questions And Answers 2 June 15th, 2007 05:09 PM
Mitsubishi Q Series Modbus TCP module QJ71MT91 set up help req'd... Pocko LIVE PLC Questions And Answers 0 July 30th, 2006 03:45 AM
Looping VBA code in Excel pagewe LIVE PLC Questions And Answers 8 February 10th, 2003 11:19 AM


All times are GMT -4. The time now is 08:24 AM.


.