Join us today!

Read Write IO in Ex...
 
Notifications
Clear all

Read Write IO in Excel using TwinCAT ADS-Script-DLL

1 Posts
1 Users
3 Reactions
708 Views
TwinControls
Posts: 71
Admin
Topic starter
(@beckhoffsupport)
Member
Joined: 2 years ago

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. 

enabledeveloper

 

Since we will create a macro and save our script, click the 'Save' option and change the file format to 'Excel-Macro-Enabled Workbook'.

savemacroenabled

 

Create a macro in Excel. 

createmacro

 

Select the 'Visual Basic' under Developer menu and right click on somewhere on the project explorer, select 'Insert' , then 'Userform'. 

insertform

 

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. 

simpleinterfaceexcel

 

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'.

targetbrowser

 

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. 

indexgroupindexoffset

 

Now we need to add the ADS-Script library in Excel. In the Visual Basic editor window, select the 'Tools', then 'References'. 

references

 

Select the checkbox of 'Beckhoff TcScript' dll and click OK. 

TcScript library

 

Right click your form and select 'View Code'.

viewcode

 

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


 

simpleinterfaceexcel

 

  

You can find more information on TwinCAT ADS-Script-DLL here

 

Reply
Share: