{"id":2013,"date":"2019-03-25T17:37:39","date_gmt":"2019-03-25T20:37:39","guid":{"rendered":"http:\/\/xexeu.elipse.com.br\/pt\/connecting-a-vba-application-to-an-e3-server-using-the-e3dataaccess-library\/"},"modified":"2022-03-29T10:18:57","modified_gmt":"2022-03-29T13:18:57","slug":"connecting-a-vba-application-to-an-e3-server-using-the-e3dataaccess-library","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/connecting-a-vba-application-to-an-e3-server-using-the-e3dataaccess-library\/","title":{"rendered":"Connecting a VBA application to an E3 Server using the E3DataAccess library."},"content":{"rendered":"
\n
\n

GENERAL NOTES<\/b><\/p>\n

    \n
  1. This article applies to Elipse E3<\/b>, versions 4.0<\/b> or lower<\/b>.<\/li>\n
  2. There is a bug<\/a> when using E3DataAccess library in typeless languages, such as VBScript and JavaScript.<\/li>\n
  3. This feature is only compatible with 32-bit applications.<\/li>\n<\/ol>\n<\/div>\n

    1) Introduction<\/span><\/p>\n

    The E3DataAccess <\/span>Library enables a new way to connect to the E3 Server. Now, you can connect any application developed on VB, Delphi, .NET or other language that supports COM to the E3 Server by using this Library’s functions.<\/p>\n

    This article explains how the E3 Server works with a VBA application developed on Microsoft Excel. You can also use the procedures shown in this article for other languages, just adjusting the source codes according to the used language’s syntax.<\/p>\n

    2) Enabling the E3DataAccess Library’s usage on VBA <\/span><\/p>\n

    Before creating the application via VBA, you must enable the E3DataAccess Library’s usage on the development environment. After that, all necessary functions to communicate to the E3 Server become available.<\/p>\n

    After properly installing E3 Studio, Microsoft Excel and VBA, you must access the Excel application’s VBA interface to add the E3DataAccess Library to the project’s references list. To do so, open the Tools – References menu, according to the next figure:<\/p>\n

    \"\"<\/div>\n
    Figure 1<\/span>: Accessing the project’s references list
    \n<\/span><\/div>\n

    In the list of available references, select the E3DataAccess <\/span>Library checkbox.<\/p>\n

    \"\"
    \nFigure 2<\/span>: List of available references in the project<\/span><\/div>\n

    If the Library is not available, you must register it first. To do so, click the Browse button to display a dialog box to select the library file (Figure 3).<\/p>\n

    \"\"
    \nFigure 3<\/span>: Dialog box to select the library file<\/span><\/div>\n

    Select the E3DataAccess.dll file. You can find it in the E3 installation directory’s Bin subfolder (for example: C:\/Program Files\/Elipse E3\/Bin<\/span>). After opening the DLL file, the E3DataAccess <\/span>becomes available and you can enable it. Then, just click the OK button to confirm the change.<\/p>\n

    3) Instancing a new E3DataAccess object in the project <\/span>
    In this example, a new Module will be added to the project and an E3DataAccess <\/span>object will be instanced in this module.<\/p>\n

    To create a new Module, right-click the project in the Project Explorer tab (Figure 4), and select the Insert – Module option.<\/p>\n

    \"\"
    \nFigure 4<\/span>: Inserting a new Module via VBA<\/span><\/div>\n

    Then, edit the Module’s name to E3ServerCOM<\/span>.<\/p>\n

    Now, you must double-click the E3ServerCOM <\/span>module to access its source code. Insert the following line to instance the E3DataAccess object:<\/p>\n

    Public E3 As New E3DATAACCESSLib.E3DataAccessManager<\/pre>\n
    With this line, a new object named E3 is created derived from the E3DATAACCESSLib.E3WebLinkMan class. This class has all necessary functions to connect to the E3 Server. The object is also instanced as public, so it can be used anywhere in the project.<\/p>\n
    NOTE:<\/span> *It is important to notice that when instancing an E3DataAcces object, you use one Viewer license. You can check this by accessing the E3 Server’s menu Licenses option. So, you must be careful to not use more licenses than the necessary.<\/p>\n
    *From version 4.5.208 on, E3Server has featured unlimited support to E3DataAccess and E3AccessLayer connections, thus consuming no Viewer licenses or any other type of licenses.<\/p>\n
    4) E3 application <\/span><\/p>\n
    To demonstrate the E3DataAccess’s reading and writing methods, you must create two Demo Tags and one Internal Tag in the E3 application’s Data Server. Besides, you must create a Historic with two fields, E3TimeStamp <\/span>and TagHist <\/span>(which stores the DemoTag2<\/span>‘s values):<\/p>\n
    \"\"
    \nFigure 5<\/span>: E3 application’s Historic<\/span><\/div>\n
    You must also create an Access Database object named DataBase1 <\/span>to save all data in the Test.mdb<\/span> file. Then, link the Database1 <\/span>object’s DBServer property to the Historic.<\/p>\n
    \"\"
    \nFigure 6<\/span>: E3 application’s Database<\/span><\/div>\n
    After that, name the Historic’s table using its TableName <\/span>property and generate it by clicking the Create DB Structure button.<\/p>\n
    Create a Query object in the Data Server and configure it to connect to the DataBase1 <\/span>and access the created Historic table. In the Query’s settings, create a query to return all data from the Historic table (Figure 7).<\/p>\n
    \"\"
    \nFigura 7<\/span>: Query’s settings<\/span><\/div>\n
    5) Reading and writing data with E3DataAccess<\/span><\/p>\n
    After setting the E3 application, we get back to Excel. First, select its screen and create two Command Buttons, one to read a value from DemoTag1 <\/span>and one to write a value to InternalTag1<\/span>. To add the buttons, enable the Control Toolbox toolbars’ visualization using the View – Toolbars – Control Toolbox menu, as you can see on the next figure:<\/p>\n
    \"\"
    \nFigure 8<\/span>: Enabling the Control Toolbox bar in Excel<\/span><\/div>\n
    In the Control Toolbox, select the CommandButton <\/span>item and add two buttons. Right-click the first button and access its properties to edit its Caption property to Read Value. Also edit the second button’s Caption property to Write <\/span>and buttons’ Name property to readValueButton <\/span>and writeValueButton<\/span>, respectively.<\/p>\n
    After that, insert the following lines in the readValueButton<\/span>‘s source code. Double-click the button to open the VBA in the button’s Click() event:<\/p>\n
    Private Sub readValueButton_Click()\r\n   Dim timestamp As Variant\r\n   Dim quality As Integer\r\n   Dim value As Variant\r\n   \r\n   E3.Server = \"E3Server_HostName\"\r\n   \r\n   If E3.GetValue(\"Data.DemoTag1.Value\", timestamp, quality, value) Then\r\n      Range(\"D4\") = value\r\n      Range(\"E4\") = quality\r\n      Range(\"F4\") = timestamp\r\n   Else\r\n      MsgBox \"Connection failure!\"\r\n   End If\r\nEnd Sub<\/pre>\n
    The E3 object in this code is the same public object instanced in the E3ServerCOM <\/span>module. It sets all operations and connections to the E3 Server, so it is the key <\/span>of the code. After setting the routine’s variables, a value is attributed to the E3 object’s Server property to set the E3 Server’s name to connect to. This must be configured before performing any operation in the Domain.<\/p>\n
    Method GetValue(Link, TimeStamp, Quality, Value)<\/span><\/p>\n
    To read the tag’s value, the GetValue() method is used and it receives the following input and output parameters:<\/p>\n<\/div>\n