Join us today!
Read Write IO in Excel using TwinCAT ADS-Script-DLL
You can control the IO in your program from Excel and develop a simple interface using Visual Basic thanks to the TwinCAT ADS - Script DLL.
If the developer options is not enabled in Excel, you need to enable this option first. Go to File-Options in Excel and select the 'Customize Ribbon' option on the following menu and check the Developer under Main Tabs options.
Since we will create a macro and save our script, click the 'Save' option and change the file format to 'Excel-Macro-Enabled Workbook'.
Create a macro in Excel.
Select the 'Visual Basic' under Developer menu and right click on somewhere on the project explorer, select 'Insert' , then 'Userform'.
Now you can create your own interface using input box, label, buttons etc.. from the toolbox. For this tutorial we simply created a pump control application. This simple interface allows you to connect to IPC, read/write the ADS State, set/read the water flow value and turn on/off the pump.
Add the variables below in your global variable list called 'GVL_IO' .
//for pump control IgbPumpControl AT %MX20.0 : BOOL := FALSE; //true- pump on, false - pump off OgbPumpStatus AT %MX20.1 : BOOL := FALSE; IgfWaterFlow AT %MB18 : REAL := 0; OgfWaterFlow AT %MB18 : REAL := 0;
Add the code below in your MAIN program.
OgbPumpStatus := IgbPumpControl; OgfWaterFlow := IgfWaterFlow;
Build the solution, activate the configuration and login the PLC. Now we will add the visual basic script in Excel to read/write the variables. Using TwinCAT ADS-Script-DLL, you can either use the Index-Group and Index-Offset of a variable or the name of the variable to control it.
To see the Index-Group and Index-Offset of a variable, you can use the 'Target Browser'.
When you are logged in the PLC, on the menu select 'Target Browser', and choose port '851' (PLC Port), and expand the GVL_IO. You can see all the Index-Group and Index-Offset of the variables in this global variable list.
Now we need to add the ADS-Script library in Excel. In the Visual Basic editor window, select the 'Tools', then 'References'.
Select the checkbox of 'Beckhoff TcScript' dll and click OK.
Right click your form and select 'View Code'.
Add the public declarations in the form's script editor.
Public TcClientSync As TCSCRIPTLib.TcScriptSync Public nAdsAmsPortNum As Integer Public strAdsAmsNetId As String
strAdsAmsNetId is the AMS Net ID of the IPC. For Local Target, you could either use "" or "0.0.0.0.0.0".
nAdsAmsPortNum is the ADS Port Number. You can check this tutorial if you want to enable the ADS Server for your EtherCAT Master and use that port number in Excel.
Connecting to IPC:
The lines below enables you to connect to IPC.
Set TcClientSync = CreateObject("TcScript.TcScriptSync") Call TcClientSync.ConnectTo(strAdsAmsNetId, nAdsAmsPortNum)
Read a Boolean variable using the Index-Group and Index-Offset:
You can create a function and use this function in the main part of your program. To read a boolean variable with this method, we will need to pass TcClientSync object, index group and the index offset to our function.
Private Function ReadBoolVar(TcClientSync As TCSCRIPTLib.TcScriptSync, indexGroup As Long, indexOffset As Long) As Boolean On Error GoTo errFunc ReadBoolVar = TcClientSync.ReadBool(indexGroup, indexOffset) Exit Function errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Function
You can use call this function as shown below:
'read bool by index group and offset Dim result As Boolean result = ReadBoolVar(TcClientSync, &H4021&, &H418&) MsgBox (result)
&H4021& - Index Group, &H418& - Index Offset. How to getting these values is explained above already.
Read a Boolean variable using its path:
Reading the value of a variable by name is a way easier method. All you need is to the path of the variable name.
Private Function ReadBoolByName(TcClientSync As TCSCRIPTLib.TcScriptSync, VarName As String) As Boolean On Error GoTo errFunc ReadBoolByName = TcClientSync.ReadVar(VarName) Exit Function errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Function
Dim bPumpState As Boolean bPumpState = ReadBoolByName(TcClientSync, "GVL_IO.OgbPumpStatus")
Write a Boolean variable using the Index-Group and Index-Offset:
Private Function WriteBoolVar(TcClientSync As TCSCRIPTLib.TcScriptSync, indexGroup As Long, indexOffset As Long, varBool As Boolean) On Error GoTo errFunc Call TcClientSync.WriteBool(indexGroup, indexOffset, varBool) Exit Function errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Function
'write bool Dim bWriteVar As Boolean bWriteVar = True Call WriteBoolVar(TcClientSync, &H4021&, &H419&, bWriteVar)
Write a Boolean variable using its path:
Private Function WriteBoolByName(TcClientSync As TCSCRIPTLib.TcScriptSync, VarName As String, varBool As Boolean) On Error GoTo errFunc Call TcClientSync.WriteVar(VarName, varBool) Exit Function errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Function
'write bool by name Call WriteBoolByName(TcClientSync, "GVL_IO.IgbProxSensor", False)
Read/Write ADS State
You can also Stop and Run your plc application using the ADS State. You can write '5' to run the PLC and 6 to stop.
Read the ADS State:
AdsState.Caption = TcClientSync.ReadAdsState()
Write the ADS State:
If (ADSStateWriteBox.Text <> TcClientSync.ReadAdsState()) Then Call TcClientSync.WriteAdsState(ADSStateWriteBox.Text) '5 for run, 6 for stop End If
You can also use the same methods to read/write integer, real, string etc.. Here's the full visual script code for this simple interface. You can also see the implementation read/write for other data types in this code.
Public TcClientSync As TCSCRIPTLib.TcScriptSync Public nAdsAmsPortNum As Integer Public strAdsAmsNetId As String Private Sub Connect_Click() On Error GoTo errFunc nAdsAmsPortNum = CLng(PortNumber.Text) If (Len(AmsNetIDInputBox.Text) > 0) Then strAdsAmsNetId = AmsNetIDInputBox.Text Else strAdsAmsNetId = "" End If Set TcClientSync = CreateObject("TcScript.TcScriptSync") Call TcClientSync.ConnectTo(strAdsAmsNetId, nAdsAmsPortNum) Exit Sub errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Sub Private Sub PumpControl_Click() On Error GoTo errFunc Dim bPumpState As Boolean If PumpControl.Value = False Then PumpControl.Caption = "Turn On Pump" Call WriteBoolByName(TcClientSync, "GVL_IO.IgbPumpControl", False) Else PumpControl.Caption = "Turn Off Pump" Call WriteBoolByName(TcClientSync, "GVL_IO.IgbPumpControl", True) End If Application.Wait (Now + TimeValue("0:00:01")) '1s delay bPumpState = ReadBoolByName(TcClientSync, "GVL_IO.OgbPumpStatus") If bPumpState = True Then PumpState.Caption = "Pump is On" Else PumpState.Caption = "Pump is Off" End If Exit Sub errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Sub Private Sub ReadFlow_Click() On Error GoTo errFunc Dim fFlow As Single Dim sPLCPathFlow As String sPLCPathFlow = "GVL_IO.OgfWaterFlow" fFlow = ReadRealByName(TcClientSync, sPLCPathFlow) ReadFlowInputBox.Caption = CStr(fFlow) + " " + "gpm" Exit Sub errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Sub Private Sub ReadState_Click() On Error GoTo errFunc AdsState.Caption = TcClientSync.ReadAdsState() Exit Sub errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Sub Private Sub WriteAdsState_Click() On Error GoTo errFunc If (ADSStateWriteBox.Text <> "") Then If (ADSStateWriteBox.Text <> TcClientSync.ReadAdsState()) Then Call TcClientSync.WriteAdsState(ADSStateWriteBox.Text) End If End If Exit Sub errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Sub Private Sub SetFlow_Click() On Error GoTo errFunc Dim fFlow As Single Dim sPLCPathFlow As String sPLCPathFlow = "GVL_IO.IgfWaterFlow" If (SetFlowInputBox.Text <> "") Then fFlow = CSng(SetFlowInputBox.Text) Call WriteRealByName(TcClientSync, sPLCPathFlow, fFlow) SetFlowInputBox.Text = fFlow End If Exit Sub errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Sub Private Function WriteBoolVar(TcClientSync As TCSCRIPTLib.TcScriptSync, indexGroup As Long, indexOffset As Long, varBool As Boolean) On Error GoTo errFunc Call TcClientSync.WriteBool(indexGroup, indexOffset, varBool) Exit Function errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Function Private Function WriteBoolByName(TcClientSync As TCSCRIPTLib.TcScriptSync, VarName As String, varBool As Boolean) On Error GoTo errFunc Call TcClientSync.WriteVar(VarName, varBool) Exit Function errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Function Private Function WriteRealByName(TcClientSync As TCSCRIPTLib.TcScriptSync, VarName As String, varReal As Single) On Error GoTo errFunc Call TcClientSync.WriteVar(VarName, varReal) Exit Function errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Function Private Function WriteIntByName(TcClientSync As TCSCRIPTLib.TcScriptSync, VarName As String, varInt As Integer) On Error GoTo errFunc Call TcClientSync.WriteVar(VarName, varInt) Exit Function errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Function Private Function ReadBoolVar(TcClientSync As TCSCRIPTLib.TcScriptSync, indexGroup As Long, indexOffset As Long) As Boolean On Error GoTo errFunc ReadBoolVar = TcClientSync.ReadBool(indexGroup, indexOffset) Exit Function errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Function Private Function ReadBoolByName(TcClientSync As TCSCRIPTLib.TcScriptSync, VarName As String) As Boolean On Error GoTo errFunc ReadBoolByName = TcClientSync.ReadVar(VarName) Exit Function errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Function Private Function ReadRealByName(TcClientSync As TCSCRIPTLib.TcScriptSync, VarName As String) As Single On Error GoTo errFunc ReadRealByName = TcClientSync.ReadVar(VarName) Exit Function errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Function Private Function ReadIntByName(TcClientSync As TCSCRIPTLib.TcScriptSync, VarName As String) As Integer On Error GoTo errFunc ReadIntByName = TcClientSync.ReadVar(VarName) Exit Function errFunc: MsgBox "Error: (0x" & Format(Hex(Err.Number), "00000000") & "), " & Err.Description End Function
You can find more information on TwinCAT ADS-Script-DLL here.
In case you want to say thank you !)
We'd be very grateful if you could share this community with your colleagues and friends. You can also buy us a coffee to keep us fueled 😊 This is the best way to say thank you to this project and support your community.
twinControls - https://twincontrols.com/
- 17 Forums
- 267 Topics
- 942 Posts
- 5 Online
- 722 Members