Microsoft SQL Server Express 2014 is a free, edition of SQL Server this is ideal for learning and developing desktop, web and small server applications. The database size can be 10 gigabytes in size. SQL Server Express 2014 release includes the full version of SQL Server 2014 Management Studio. We will be using SQL Server 2014 Management Studio (SSMS) to create a database. The SQL Server database will be created so we can connect to it for data acquisition (data logging) from our PLC applications. Continue Reading!
Tag Archives: scada
Installing SQL Server Express 2014
Microsoft SQL Server Express 2014 is a free, edition of SQL Server this is ideal for learning and developing desktop, web and small server applications. The database size can be 10 gigabytes in size. SQL Server Express 2014 release includes the full version of SQL Server 2014 Management Studio. This is a method to read, write and maintain our database. (Tools) We will be installing this SQL Server database so we can connect to it for data acquisition (data logging) from our PLC applications.
Installing the software
The download file can be obtained from the website for SQL Express 2014.
https://www.microsoft.com/en-ca/download/details.aspx?id=42299
We will be installing the ExpressAdv 64Bit\SQLEXPRADV_x64_ENU.exe on a windows 7 64 bit machine.
This is the latest software that can be installed on the Windows 7 operating system. Continue Reading!
Modbus TCP Data Logging to Database
Using Visual Basic 2015 we will log three holding registers from the PLC along with time and date into a Microsoft Access Database. We will log every minute into the database with the information that we collect from the PLC via Modbus TCP (Ethernet). All code will be done and shown so you can implement this in your application with different parameters. The information collected in the database can then be distributed or analyzed in the future.
Visual Basic 2015 will be used with the EasyModbusTCP client/server library for .net. We will communicate to an Automation Direct – Do-More PLC. Using the free simulation software of the PLC Designer Software, we will retrieve three values of the Modbus Holding Registers using Modbus TCP. Once we have this information out of the programmable logic controller it will be placed in a Microsoft Access (2010) Database. This will be done by using a SQL command to insert the data.
Automation Direct has a powerful simulator with their Do-More PLC. The PLC software is available from Automation Direct as a free download.
http://support.automationdirect.com/products/domore.html
We will start with the PLC program. The first three holding registers will be used. That will be MHR1, MHR2, and MHR3 in the Do-More PLC. This will correspond to register numbers 40001, 40002 and 40003. The following is a table with all of the references for Modbus communications to the Do-More.
| Coil/Register Numbers | Data Addresses | Type | Do-More PLC | Table Name |
| 00001-09999 | 0000 to 270E | Read-Write | MC1 to MC1023 | Discrete Output Coils |
| 10001-19999 | 0000 to 270E | Read-Only | MI1 to MI1023 | Discrete Input Contacts |
| 30001-39999 | 0000 to 270E | Read-Only | MIR1 to MIR2047 | Analog Input Registers |
| 40001-49999 | 0000 to 270E | Read-Write | MHR1 to MHR2047 | Analog Output Holding Registers |
Note: The Do More PLC uses the Modbus area to communicate. This is because having direct access to the digital I/O can be dangerous when connected via Ethernet to the internet. Data must move in and out of this area via the PLC program.
Here is the PLC program that sets the three registers.
The next thing to do is set up our Microsoft Access 2010 Database. Our database will be named ACC_Database and it will be located at the following location: “C:\AccLog”. We will have one table called ACC_Log. In the table we will have five fields; LogDate, LogTime, Register1, Register2, Register3.
Visual Basic 2015 is free and is part of Visual Studio 2015. It can be downloaded at the following location:
https://www.visualstudio.com/en-us/products/visual-studio-community-vs
EasyModbusTCP is free software. It will be referenced in our visual basic program. It can be downloaded from the following location:
https://sourceforge.net/projects/easymodbustcp/
After downloading you must extract the files.
EasyModbusTCP is a Modbus TCP, Modbus UDP client/server library for .NET and Client library for JAVA. .NET (.dll) Client/Server also supports Modbus RTU. It supports the following function codes: – Read Coils (FC1)
– Read Discrete Inputs (FC2)
– Read Holding Registers (FC3)
– Read Input Registers (FC4)
– Write Single Coil (FC5)
– Write Single Register (FC6)
– Write Multiple Coils (FC15)
– Write Multiple Registers (FC16)
– Read/Write Multiple Registers (FC23)
Now we will call up visual studio and create our project. The first thing that we must do is reference to our EasyModbus.dll file.
Our program will log the three registers based upon a timer function. When the time expires we will set the interval to 1 minute and call a subroutine (Button1). This subroutine will communicate to the PLC via Modbus TCP and get our three registers. It will then insert the data from the registers along with the time and date into an access database.
Here is what our visual basic form will look like:
Here is the visual basic code for our project. We use the Imports command for the EasyModbusTCP namespacing.
Imports EasyModbus 'Import the EasyModbus
Here is the part of the subroutine that will communicate to the PLC via the EasyModbusTCP, You will notice that we have used the ‘Try’ command on the ModbusClient.Connect() statement. This way we can track if we are communicating or not to the PLC.
Public Class Form1 Private Sub Button1_Click() Handles Button1.Click 'This subroutine will communicate using the EasyModbusTCP to the PLC 'This will execute when the user clicks the button or at specific intervals from the Timer1 Dim ComError = 0 'Set communication error flag to 0 'Specify the IP Address and Port Number that we are connecting Dim ModbusClient As EasyModbus.ModbusClient = New EasyModbus.ModbusClient(TextBox1.Text, 502) Try ModbusClient.Connect() 'Connect to the PLC Catch ex As Exception 'What to do when an error occurs Label10.ForeColor = Color.Red Label10.Text = "Communication Error!" ComError = 1 'Set communication error flag to 1 End Try If ComError = 0 Then 'Do the following when communication is OK Label10.ForeColor = Color.Black Label10.Text = "Logging..." Dim Registers As Integer() 'Where to store the information Registers = ModbusClient.ReadHoldingRegisters(0, 3) 'Read three registers starting at the first one Label1.Text = Registers(0) 'Value of MHR1 Label2.Text = Registers(1) 'Value of MHR2 Label3.Text = Registers(2) 'Value of MHR3 ModbusClient.Disconnect() 'Disconnect from the PLC
Here is the code to open the database connection and insert the data into our ACC_Database file. We use the ‘Try’ command to ensure that if errors occur it will not stop our program. The connection string ( Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\AccLog\ACC_Database.accdb) may be different then yours. In order to get your connection string, please review the following from a previous post and search for the connection string.
'Log values into an Access Database 'We will use a SQL instruction to insert a record into the table Try Dim SQL As String 'SQL Command String Dim objCmd As New OleDb.OleDbCommand 'Command 'Connection String to the Access Database Dim Con = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\AccLog\ACC_Database.accdb") 'SQL Statement - All values must be set for the table SQL = "INSERT INTO ACC_Log VALUES ('" & Now.ToString("yyyy/MM/dd") & "', '" & Now.ToString("hh:mm:ss") & "', '" & Registers(0) & "', '" & Registers(1) & "', '" & Registers(2) & "')" Con.Open() 'Open the database connection objCmd = New OleDb.OleDbCommand(SQL, Con) 'Set the command objCmd.ExecuteNonQuery() 'Execute the SQL command Con.Close() 'Close the database connection Catch ex As Exception 'What to do when an error occurs Label10.ForeColor = Color.Red Label10.Text = "Database Error!" End Try End If End Sub
This timer is originally set for 100ms. When the program starts, it will log the first time and then set the timer interval to 1 minute. (60000ms)
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick Label9.Text = Now 'Display time and date Timer1.Interval = 60000 'Set interval for 1 minute Timer1.Enabled = True Call Sub() Button1_Click() 'Call the routine to get the PLC information and store in a database End Sub
This will handle the LinkLabel on the form.
Private Sub LinkLabel1_LinkClicked(sender As Object, e As LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked ' Specify that the link was visited. Me.LinkLabel1.LinkVisited = True ' Navigate to a URL. System.Diagnostics.Process.Start("https://www.accautomation.ca") End Sub End Class
Running the program:
The status will show ‘Initializing..’ so that the imports can be loading in the program. This will happen each time the software starts.
Logging will be displayed in the status to indicate that everything is working correctly.
Error messages will show in the status when an error has occurred. We have programmed two errors:
Communication Error! – Information is not being received from the PLC. This could be due to the wrong IP address or communications links have been broken.
Database Error! – Information cannot be written into the database, the database is not present, etc.
Here is what our database looks like after collecting a few samples.
As you can see, collecting information from the PLC via ModbusTCP and inserting it into an Access database can be easily done. The next steps would be to ensure that the data is always collected. What happens when the communication to the PLC is cut? Robust logging is required. This is when we create a buffer in the PLC of the logged values. Please refer to our EBook ‘Robust Data Logging for Free’ .
Download the sample database, visual basic and PLC code for this project here.
Watch on YouTube: Modbus TCP Data Logging to Database
If you have any questions or need further information please contact me.
Thank you,
Garry
If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLC’s are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII and Floating Point.
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.
Creating a HMI Login Screen on AdvancedHMI
We will be creating an HMI (human machine interface) login screen for the cylinder program that we developed previously.
BUILDING A PLC PROGRAM THAT YOU CAN BE PROUD OF – PART 6
Our program had a Run, Jog and Teach mode for the cylinders. Our user will login with a user name and password. Based upon the user level that is set we will control what modes each user will be able to operate. We will also write this user level mode into the PLC.
The following is the sequence of operation:
Watch on YouTube: Creating an HMI Login Screen on AdvancedHMI Sequence of Operation
Note: All of the programs used are provided free of charge and are an excellent way to learn PLC / HMI programming.
AdvancedHMI is a free HMI software package that communicates to a number of different PLC manufacturers. This package uses visual studio and vb.net to program, however, you do not need to be an expert on visual basic to implement this software. We will be using an access database (Access 2010) to store our user information. When the operator logs into the system, they will enter a user name and password. The program will compare this to the information in the database and set the appropriate modes on the HMI.
The first thing that we need to do is to create a database to store user information. We will create a table called ‘tbl_user’. The fields in the database will be as follows:
First_Name – Text Field – 50 characters
Last_Name – Text Field – 50 characters
User_Level – Number – Integer
User_Name – Text Field – 50 characters
Password – Text Field – 50 characters
The user level will be as follows:
0 – Stop and reset the machine only (Not logged in)
1 – Run, stop and reset the machine only
2 – Run, stop, reset and jog the machine
3 – Run, stop, reset, jog and teach the machine
Here is a list of addresses that we will be using for our login screen between the HMI and PLC.
| Device | Data Addresses | Type | Do-More PLC | Description |
| Run/ Jog Selector | 00008 | Input | MC8 | MC8 on is jog mode |
| Jog / Teach Selector | 00070 | Input | MC70 | MC70 on is teach mode |
| User Level | 40005 | Output | MHR5 | Current user level |
We will add a Login / Logout button on the main page of our control. This will allow the operator to sign in or out of the machine. To ensure that the operator knows that he is logged into the system we will display a message in a label also on the main screen. Here is what our main page now looks like:
The next thing we need to do is to design the form for our login information. This will contain two text boxes for the password and user name. We will also need two buttons; Login and Cancel. Our new form will be called Page2. Here is what it will look like:
When the HMI first powers up and no user has logged in we will ensure that the Jog and Teach functions are reset by writing values directly to the PLC. We will also set the user level to 0.
Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles Me.Load
ModbusTCPCom1.Write(“00008”, 0) ‘Reset Run / Jog Selector PLC
ModbusTCPCom1.Write(“00070”, 0) ‘Reset Jog / Teach Selector PLC
ModbusTCPCom1.Write(“40005”, 0) ‘Set user level to 0
End Sub
Now let’s look at the logic for the button on the main page. If a person is logged into the machine then this button will log them out. This will also change the user level to 0 and write this to the PLC. In our case, MHR5 (400005) will contain the user level code. If no one is logged in then this button will call up our login form (Page2) so information can be entered.
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If Button1.Text = “Login” Then
Page2.txtUsername.Text = “”
Page2.txtPassword.Text = “”
Page2.Show()
Else
SelectorSwitch1.Value = False
SelectorSwitch2.Value = False
MomentaryButton1.Enabled = False ‘Start
SelectorSwitch1.Enabled = False ‘Run / Jog Selector
SelectorSwitch2.Enabled = False ‘Jog / Teach Selector
Button1.Text = “Login”
Label6.Text = “Please Login To Operate the Machine”
ModbusTCPCom1.Write(“40005”, 0) ‘Reset user level in PLC
End If
End Sub
Page2 is our login form. Here is the programming logic for the Login Button.
Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
If txtPassword.Text = “” Or txtUsername.Text = “” Then
MessageBox.Show(“Please complete the required fields…”, “Authentication Error”, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
‘ Connect to DB
Dim conn As New System.Data.OleDb.OleDbConnection()
conn.ConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source= F:\Users\Shortt\Downloads\Creating a HMI Login Screen in AdvancedHMI\HMI\AdvancedHMIBetaV399a\AccDatabase.accdb”
Try
Dim sql As String = “SELECT * FROM tbl_user WHERE User_Name='” & txtUsername.Text & “‘ AND Password = ‘” & txtPassword.Text & “‘”
Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
‘Open Database Connection
sqlCom.Connection = conn
conn.Open()
Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()
If sqlRead.Read() Then
Dim FirstName As String
Dim LastName As String
Dim UserLevel As Integer
FirstName = sqlRead(“First_Name”)
LastName = sqlRead(“Last_Name”)
UserLevel = sqlRead(“User_Level”)
MainForm.ModbusTCPCom1.Write(“40005”, UserLevel)
MainForm.Button1.Text = “Logout”
MainForm.Label6.Text = FirstName & ” “ & LastName & ” Level = “ & UserLevel
MainForm.MomentaryButton1.Enabled = True ‘Start
If UserLevel = 3 Then
MainForm.SelectorSwitch1.Enabled = True ‘Run / Jog Selector
MainForm.SelectorSwitch2.Enabled = True ‘Jog / Teach Selector
ElseIf UserLevel = 2 Then
MainForm.SelectorSwitch1.Enabled = True ‘Run / Jog Selector
MainForm.SelectorSwitch2.Enabled = False ‘Jog / Teach Selector
Else
MainForm.SelectorSwitch1.Enabled = False ‘Run / Jog Selector
MainForm.SelectorSwitch2.Enabled = False ‘Jog / Teach Selector
End If
MainForm.Show()
Me.Hide()
Else
‘ If user enter wrong username and password combination show error
MessageBox.Show(“Username and Password do not match..”, “Authentication Failure”, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
‘Clear all fields
txtPassword.Text = “”
txtUsername.Text = “”
‘Focus on Username field
txtUsername.Focus()
End If
conn.Close()
Catch ex As Exception
MessageBox.Show(“Failed to connect to Database.. System Error Message: “ & ex.Message, “Database Connection Error”, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End If
End Sub
A further explanation of the code above can be found at the following address.
How to create login form in VB.NET
Connecting to our database
The ConnectionString will vary from machine to machine. To automatically determine the connection string to our database we can do the following:
1 – Click DataBindings in the Properties window. Click F4 if you cannot see the properties window or add it on View – Properties Window.
2 – Under DataBindings, click on Text – Add Project Data Source. This will call up the Data Source Configuration Wizard.
3- Choose Database as your Data Source Type and click Next.
4- Choose Dataset as your Database Model and click Next.
5 – Click on New Connection
6 – Click on Microsoft Access Database File as your Data Source and click Continue
7- The Add Connection window appears. Beside the ‘Database file name:’ click Browse. Locate the database file that we created above.
8 – Click the Test Connection. You should get the test connection succeeded message.
9 – Click OK to close the Add Connection wizard. Expand the “Connection string that you will save in the application”. Copy the connection string given and paste it on conn.ConnectionString.
Note: Copy – Ctrl + C / Paste – Ctrl + V
Here is the programming logic for the Cancel Button.
Private Sub ReturnToMainButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
MainForm.Show()
Me.Hide()
End Sub
In this program, we are just writing a user level in PLC memory address MHR05. The control of the PLC can be limited if hardwired selector switches and pushbuttons were in the system. Implementing a login screen is very straight forward and can be part of your overall automation solution.
Download the access database and the Bin directory for the AdvancedHMI login screen.
The cylinder PLC program from Building a PLC Program That You Can Be Proud Of – Part 6 can be downloaded with the following link.
Download the PLC program and the Bin directory for the advanced HMI Screen.
Watch on YouTube: Creating an HMI Login Screen on AdvancedHMI – Explaining the Code
If you have any questions or need further information please contact me.
Thank you,
Garry
If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLC’s are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII and Floating Point.
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.
Create a PLC with HMI Training and Learning Environment Free
Learn PLC programming and use a powerful HMI (Human Machine Interface) easily and free. We will use the Automation Direct Do-More programming software tied into the Advanced HMI package via Modbus TCP.
Our application will show an HMI screen with a panel meter and a reset button. The panel meter value may be changed by clicking it. This will bring up an input screen to put in a number. When the reset button is selected the input value entered will show on the panel meter.
Since we will be communicating via Modbus TCP, the following table shows the Coil/Register Numbers and the associated Do-More PLC Addresses.
| Coil/Register Numbers | Data Addresses | Type | Do-More PLC | Table Name |
| 00001-09999 | 0000 to 270E | Read-Write | MC1 to MC1023 | Discrete Output Coils |
| 10001-19999 | 0000 to 270E | Read-Only | MI1 to MI1023 | Discrete Input Contacts |
| 30001-39999 | 0000 to 270E | Read-Only | MIR1 to MIR2047 | Analog Input Registers |
| 40001-49999 | 0000 to 270E | Read-Write | MHR1 to MHR2047 | Analog Output Holding Registers |
Note: The Do More PLC uses the Modbus area to communicate. This is because having direct access to the digital I/O can be dangerous when connected via Ethernet to the internet. Data must move in and out of this area via the PLC program.
We will first start with the PLC.
Automation Direct has a powerful simulator with their Do-More PLC. It is the Do-More Designer Software. This software simulator includes the entire instruction set (Not Just Bit Logic) as well as communication protocols. It can be downloaded and installed for free from the above link.
Our PLC program will have the following addresses:
Digital Panel Meter Present Value (PV) – MHR1 – Modbus 40001
Digital Panel Meter Set Value (SV) – MHR2 – Modbus 40002
Reset Button – MC1 – Modbus 00001
The first rung of the ladder will use the 1-second pulse bit and increment the PV value of our digital panel meter. This will also compare the current value to 4000 and if greater or equal, move the value of zero into the PV value.
The second rung of the ladder will move WX0 analog value from our simulator into the PV value of our digital panel meter.
The last rung of ladder will move the SV value into the PV value of our Digital Panel Meter. This happens when the reset is hit.
The simulator is showing X0 on and we can then use the WX0 slider to change the PV value of the Panel Meter.
Advanced HMI is a powerful HMI/SCADA (Supervisory Control and Data Acquisition) development package that takes advantage of Visual Studio. There is no coding required and you can simply drag and drop items onto the page. The best thing is that the software is free.
Communications drivers include the following and are accessible via VB or C# code:
- Allen Bradley DF1 RS232 Driver
- Allen Bradley Ethernet/IP Driver for SLC,MicroLogix, ControlLogix, and CompactLogix
- Beckhoff TwinCAT Driver
- Modbus TCP Driver
- Modbus RTU Driver
- Omron Ethernet FINS Driver – Ethernet for newer controllers such as CP1H with Ethernet module
- Omron Serial FINS Driver – Serial (RS232 / RS485) for newer controller such as CP1H
- Omron Serial HostLink Driver – Serial (RS232 / RS485) for controllers such as CQM1, C200H, K-Series (C28K), C200, etc
The power of Advanced HMI is that it works within Visual Studio. This is a program integrated development environment (IDE) that you can take advantage of to modify or create new features including data logging applications.
Advanced HMI runs on Visual Studio 2008 or higher and will need to be installed on your PC. Visual Studio Community Edition 2015 is the latest version of the software. If you do not have it installed, please download and install from the following link.
https://www.visualstudio.com/en-us/products/visual-studio-community-vs
We will now need the Advanced HMI project. Here is the link to download the zip file.
http://sourceforge.net/projects/advancedhmi/
After downloading ‘AdvancedHMIBetaV399a.zip’ extract the files from the zip file. (Right Click.. Select Extract All)
Note: Your version might be different than the one above.
Open the solution file (AdvancedHMIv35.sln) from the extracted files in the root directory.
Our initial screen looks like the following. The project will now need to be compiled in order to add the components to the Toolbox.
Select Build | Build Solution from the menu
The next thing to do is add the communication to the form. On the left-hand side of the screen, you will see the ‘Toolbox’. Click on it and under AdvancedHMIDrivers Components we will select ModbusTCPCom. To actually add a component to our form you need to drag it. Select the component and as you hold the mouse button down move to the form.
After adding the ModbusTCPCom component, it will appear at the bottom, beneath our form.
Click on the ModbusTCPCom1 at the bottom of our form. On the right-hand side, you will notice the properties for this communication driver. Under Communication Settings | IP Address, enter the value of the IP Address for the PLC. (192.168.1.3) Ensure that the port number is 502. This is the default port number for Modbus TCP.
We can now add the digital panel meter. From the toolbox select and drag the DigitalPanelMeter to our form.
Resize the panel meter on the form by dragging a corner of the component.
While the panel meter is clicked, set the Properties | PLC Properties of the component:
PLCAddressValue – 40001 – MHR1 – Value to display on the meter.
PLCAddressKeypad – 40002 – MHR2 – This is the location of the stored number when the operator selects the meter and enters a number in the keypad.
Add a MomentaryButton to our form by selecting and dragging it from the toolbox.
After re-sizing the component, we can change the colour to blue under Properties | Misc. Also, change the text on the button to ‘RESET’
Set the PLCAddressClick value to 00001. This is address MC1 in the Do-More PLC.
Run the application by selecting the ‘Start’ form the top menu. This also can be started by hitting ‘F5’. The form will then show in a separate window and the panel meter will be incrementing the value. Hitting the reset button will reset the value to the one entered when you click the panel meter.
When you hit the panel meter on the display a keypad will then pop up on your screen. Enter the new value and then select ‘Enter’. The new value will appear in MHR2 in the Do-More PLC.
Watch on YouTube: Create a PLC with HMI Training and Learning Environment Free
If you have any questions or need further information please contact me.
Thank you,
Garry
If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLC’s are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII and Floating Point.
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.
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:
- Explain Modbus TCP protocol
- Install OstroSoft Winsock Component
– Winsock API Calls for communication on network - Develop the Excel and VBA application
(Microsoft Excel 2010) - Communicate to the PLC and sample code
(Do-More Simulator)
The Modbus TCP/IP or Modbus TCP is a Protocol that is used for communications over TCP/IP networks. This is done on port 502. Modbus TCP does not require a checksum calculation as lower layers already 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
OstroSoft Winsock Component
OSWINSCK.dll serves as a wrapper for the Winsock API and helps programmers to abstract from the complexity of API calls and focus on application functionality. Works with programming and scripting languages supporting COM.
You will need to 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
2. Run downloaded file from Windows Explorer or command-line
Hit OK
I use the default directories where the program will be installed. Click the button to install.
Leave the program group to the default so I know what the program is after installation. Click continue.
Click OK
The OstroSoft Winsock Component is now installed.
Start Microsoft Excel.
Select ‘Developer’ along the top tabs.
If the Developer tab is not present then we must turn on the developer tab.
Select File | Options 
Select ‘Customize Ribbon’
Check the ‘Developer’ under Main Tabs. 
Under the Developer menu. Select ‘Visual Basic’
The Visual Basic Editor window will now be displayed. 
From the menu – Tools | References
We can now add the OstroSoft Winsock Component to our application.
Select OK
Select Sheet1(Sheet1). 
Now put the visual basic code in the 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 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(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 to write
‘ 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 colour
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 on the worksheet what we would like to see.
Note the following:
IP Address = B4
MHR 1 to 10 values located at B10 to B19
‘Stop Data’ – CommandButton2
‘Retrieve Data’ – CommandButton1
Communication to the PLC
Start the Do-More Designer software.
Under the Project Browser select ‘System Configuration’
Make note of the IP address. If you are running the simulator then this is automatically filled in.
Ensure that the Enable Modbus/TCP Server is checked. Also make sure that the TCP Port Number is 502.
The sample PLC program will write values in the range 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 gets to the value above 4000, a move instruction will put a 0 back into MHR 1.
If input X0 turns on then the value in XW0 will be moved into MHR1 and the previous clock bit will not be in effect. Values will be between 0 and 4096. (12-bit resolution)
This is repeated with different internal clock bit flags up to MHR10.
Running the program will produce the following:
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
If you have any questions or need further information please contact me.
Thank you,
Garry
If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLC’s are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII and Floating Point.
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.
Implementing the Omron CX Server DDE and Excel
Utilizing the CX Server DDE Manager will allow us to provide a link to the information in the Omron PLC. This link can be placed on an Excel spreadsheet. Charts, graphs, etc. can be made from the data. We will create links on an excel spreadsheet from a CQM1H and a CP1L PLC. The information will then be displayed as a bar graph comparing the first ten memory areas.
Dynamic Data Exchange (DDE) is a channel through which correctly prepared programs can actively exchange data and control other applications within Microsoft Windows. The DDE manager will allow us to set up all of the links.
Start the DDE Manager by clicking Start/Programs/Omron/CX-Server/DDE Manager
This will start the DDE Manager and place an icon on your toolbar at the bottom of your screen. The application will run minimized.
Right-click on the minimized DDE Manager icon and select Project/New.
Assign a filename (ACC DDE.cdm) to the CX-Server project that you will create, and save it.
The create project will minimize to the taskbar. Right-click on the minimized DDE Manager icon and select Project/Edit.
This will call up the DDE Manager Project Editor. We can now add the PLC’s (Devices) and Points that we want to display.
Select File/New/PLC, or hit the PLC Icon on the taskbar.
Under Change PLC, we can now enter the information about how we are communicating to the programmable logic controller.
Device Name: – Name in which the DDE Manager will call the PLC
Device Type: – The model of PLC that will be connected
Network Type: – This is the communication method that we will be talking with the device.
In our example, we will use the following two Omron PLCs.
- CQM1H – SYSMAC WAY (Omron Host Link Protocol) Communication Port 11, 9600, E, 7, 2. Unit #00
- CP1L – USB
Once we have the PLC information, we can now add points that will contain the information that we wish to retrieve from the units. Select File/New/Point or select the point editor icon to start the point editor.
On the Logical tab, you can give a name (Or Change) to the point in the PLC to get. Select the Physical tab.
On the physical tab we set the following information:
PLC: – This is the list of PLCs entered in the previous step
Data Location: – The memory location that we want to read from
Internal Data Type: – This is the interpretation of the data that we are reading. (Example: Bit, Word, BCD, etc.)
Command Modifier: – This is used if we need to Force Set / Reset the data location. This can be used to override the PLC program. Use with caution.
Press OK when finished setting the address.
Our point is now programmed. You will notice the symbols before the name of the point. This tells us at a quick glance what type of data we are looking at. Please refer to the CX Server Runtime manual for all of the symbol meanings.
Fill out the remaining points for our example.
CP1L – D0 to D9
CQM1H – DM0 to DM9
Start Microsoft Excel.
Go back to the DDE manager and the points under the Project Editor. We can now select the first ten items on the list. Click the first one and then move to the last item. Hold the shift while clicking the last item will select all of them between.
Hit the DDE Link icon.
In Excel, we now paste the information onto the worksheet. (Ctrl + V) Right click the cell and select paste. 
The information will now be displayed and updated on your worksheet.
The update time will depend on the interval set in the DDE Manager. Right-click on the minimized DDE Manager icon and select Update Interval…
We can also set individual point update times in the DDE link item. The default update time is set via the DDE Manager but we can also set the time in the individual points on the spreadsheet.
DDE Update Default Link:
=CDMDDE|’C:\ACC DDE.cdm’!’\CP1L D0′
5 Second Update Link:
=CDMDDE|’C:\ACC DDE.cdm’!’\CP1L D0,5′
In the Project Editor of the DDE Manager, you can see the first 10 memory areas are being used. This is the yellow triangle with the lightning bolt symbol. (You will not be able to delete the point if it is in use.)
Calling up an excel file with the links embedded will give you a Security Alert – Links warning. Select ‘Enable the content’ and then hit OK.
Here is an example of the finished excel file. We have 10 memory locations from each PLC listed. We have inserted a graph that will dynamically change when the values are read.
Download the excel file here.
As you can see implementing the CX Server DDE Manager is not a difficult task. It provides a quick and efficient way to view the information that we need.
Additional Information:
Excel – Conditional Movement of Data
Watch on YouTube: Implementing the Omron CX Server DDE and Excel
If you have any questions or need further information please contact me.
Thank you,
Garry
If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLC’s are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII and Floating Point.
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.