PLC and Microsoft Office Excel

How to connect between PLC (Programmable Logic Controller) and Microsoft Office Excel?
This application uses PLC, Microsoft Office Excel 2007 and Modbus ActiveX Control.
Communication between PLC (Programmable Logic Controller) and Microsoft Office Excel using Modbus Protocol.
I use this application for data logging: each switch is pressed from the PLC, it will produce data such as the date and time in Microsoft Excel.
You can see the video below:

Hardware:
1. PLC (Programmable Logic Controller), I use Siemens S7-200
2. RS232 PLC Cable: I use PPI Multi-Master Cable for Siemens S7-200
3. USB to RS 232 Converter, I use Laptop without RS232 port
4. Micro Limit Switch

Hardware Connections:

Download Project File:
1. MBAXP Modbus ActiveX Control ( MBAXPSetup32Bit ): http://www.modbustools.com/download.asp
Alternative Download, click here
Install MBAXPSetup32Bit.exe  to your computer with windows OS

2. Project file for Microsoft Office Excel 2007, click here
3. PLC Ladder Programming for Siemens S7-200, click here

How can the settings on Microsoft Office Excel 2007:
1. Click Office Button, and Click Excel Options

2. Click Popular, and then select the Show Developer tab in the Ribbon check box
3. Click Trust Center, and then click Trust Center Settings...

3. Click Macro Settings, and then Select Enable all macros (not recommended; potentially dangerous code can run)
4. Click ActiveX Settings, and then Select Enable all controls without restrictions and without prompting (not recommended; potentially dangerous controls can run)
5. Check Modbus ActiveX Control: Click Developer, and then Click Insert and Click More Controls

PLC Ladder Programming:

Macro VBA Microsoft Office Excel:

Dim R As Integer
Dim SW As Integer
Dim Exec As Boolean

Private Sub CommandButton1_Click()
If Mbaxp1.IsConnected = True Then
Mbaxp1.CloseConnection
CommandButton1.Caption = "Connect"
Exit Sub
End If

Mbaxp1.Connection = Range("F1")   'Port Number in Excel Cell
Mbaxp1.BaudRate = B9600     '9600 Baud
Mbaxp1.DataBits = Eight    '8 Data bits
Mbaxp1.Parity = Even       'Even parity
Mbaxp1.StopBits = One    '1 Stop bits
Mbaxp1.ProtocolMode = RTU 'RTU Mode
Mbaxp1.Timeout = 1000
Mbaxp1.OpenConnection
If Mbaxp1.IsConnected = True Then
   'PresetMultipleRegisters(ByVal Handle As Integer, ByVal SlaveID As Integer, ByVal Address As Long, ByVal Quantity As Integer, ByVal UpdateRate As Long) As Boolean
  R = Mbaxp1.PresetMultipleRegisters(0, 1, 0, 2, 100)
   'Register(ByVal Handle As Integer, ByVal Index As Integer) As Integer
  Mbaxp1.Register(0, 0) = 0
   Mbaxp1.Register(0, 1) = 0
   'Function UpdateOnce(ByVal Handle As Integer) As Boolean
  Mbaxp1.UpdateOnce (0)
'ReadHoldingRegisters(ByVal Handle As Integer, ByVal SlaveID As Integer, ByVal Address As Long, ByVal Quantity As Integer, ByVal UpdateRate As Long) As Boolean
R = Mbaxp1.ReadHoldingRegisters(1, 1, 0, 2, 500)
'UpdateEnable(ByVal Handle As Integer) As Boolean
Mbaxp1.UpdateEnable (1)
CommandButton1.Caption = "Disconnect"
End If

End Sub

Private Sub Mbaxp1_ResultError(ByVal Handle As Integer, ByVal Error As Integer)
On Error Resume Next
Range("F9") = Error
End Sub

Private Sub Mbaxp1_ResultOk(ByVal Handle As Integer)
  If Handle = 1 Then
    On Error GoTo ErrorHandler
    'Register(ByVal Handle As Integer, ByVal Index As Integer) As Integer
   SW = Mbaxp1.Register(1, 0)
    Range("F11") = SW
    Range("F12") = Mbaxp1.Register(1, 1)
If Exec = True And SW > 0 Then
   Exec = False
   'Add DateTime In Cells
  sw0 = (SW And 1)
   If sw0 = 1 Then
   rw = Application.WorksheetFunction.CountA(Range("A:A")) + 1
   Cells(rw, 1).NumberFormat = "yyyy-mm-dd hh:mm:ss"
   Cells(rw, 1) = Now()
   End If
 
   sw1 = (SW And 2)
   If sw1 = 2 Then
   rw = Application.WorksheetFunction.CountA(Range("B:B")) + 1
   Cells(rw, 2).NumberFormat = "yyyy-mm-dd hh:mm:ss"
   Cells(rw, 2) = Now()
   End If
 
   'PresetMultipleRegisters(ByVal Handle As Integer, ByVal SlaveID As Integer, ByVal Address As Long, ByVal Quantity As Integer, ByVal UpdateRate As Long) As Boolean
  R = Mbaxp1.PresetMultipleRegisters(0, 1, 1, 1, 100)
   'Register(ByVal Handle As Integer, ByVal Index As Integer) As Integer
  Mbaxp1.Register(0, 0) = SW
   'Function UpdateOnce(ByVal Handle As Integer) As Boolean
  Mbaxp1.UpdateOnce (0)
End If

If SW = 0 Then
   Exec = True
   R = Mbaxp1.PresetMultipleRegisters(2, 1, 1, 1, 100)
   'Register(ByVal Handle As Integer, ByVal Index As Integer) As Integer
  Mbaxp1.Register(0, 0) = 0
   'Function UpdateOnce(ByVal Handle As Integer) As Boolean
  Mbaxp1.UpdateOnce (2)
End If

    Range("F9") = ""
ErrorHandler:
    Exit Sub
  End If
End Sub

Labels:
Excel
Microsoft Office Excel
Modbus
ModBus Communication
PLC and Microsoft Office Excel
PLC Application