In this article, we’ll explore how to use Microsoft Excel for data-logging using the RIO Pocket PLC and an Ethernet connection. I’ll explain the steps necessary to get from a blank spreadsheet to a useful data logging application in about 15 minutes.

There are a few things needed for this to work. The first thing is a RIO-471x0 (although any DMC motion controller with Ethernet capability will also work) and the second thing is to install GalilTools($195) or GalilTools-Lite (free).

After installing GalilTools software, you should assign an IP address to the RIO. In this example, I’m using 10.0.6.47 but you should assign an IP address that is compliant with your network settings. After assigning an IP address – connect up to the controller in GalilTools to verify that communication is working.

Part 1: A Simple Application

We’ll start with a simple example of sending a command to query the built in “TIME” variable on the controller. This variable is constantly updated inside the controller to reflect a running time in milliseconds. To get this value, we need to send down the string “MG TIME” and that tells the controller to return the value of TIME.

To do this, turn on the View -> Toolbars ->“Control Toolbox”

and make sure you are in “Design Mode” by enabling the icon on the far left.

Next, add a button to your Excel spreadsheet by clicking on the button icon and dragging it on to your spreadsheet. Right Click on the button and choose Properties. Modify the Properties to name the button and give it a caption.

Your sheet should look something like this:

Now, double click on the button and the Visual Basic Editor window will pop up. Go to Tools-References and enable the checkbox for “Galil” as shown below.

If there is no Galil checkbox, click “Browse” and choose the file C:\Program Files\Galil\GalilTools-x86\lib\IGalil.tlb. Click OK.

Now add the following text to your Button Click Function:

Private Sub GetTIME_Click() Dim g As New Galil.Galil g.Address = "10.0.6.47" 'Connect to IP address of RIO Pocket PLC Sheet1.Cells(4, 4) = g.Command("MG TIME") 'Send Command MG TIME End Sub

But replace the IP address with the IP address of your RIO unit. Go back to Sheet1 and click the icon on the Control Toolbox to “Exit Design Mode”. Now click on the button and you should get a value in cell D4 like this:

You can build an entire application from the simple steps shown above to achieve an interactive user interface to the RIO. This simple example allows you to send commands and receive responses via an Excel spreadsheet. However, for a data logging application – this just isn’t going to cut it so Part 2 of this article is going to look at some more advanced features such as accessing the internal Data Record of the controller. So go grab a cup of coffee because you'll want to be awake for the next part.

Part 2: Using the Data Record

Now that we’ve gotten the Excel spreadsheet communicating to the controller, its time to grab some data! Create a new Excel workbook and follow the same steps as Part1 to add a button named GetDataRecord and include the Reference to IGalil.tlb. For all Galil controllers, there is a "snapshot" of all the internal values that can be sent out at a regular interval. This snapshot is called the DataRecord and you can see exactly what values are stored by looking at Chapter 3 in the User Manual of the controller. The code below runs a loop that grabs all the values from the DataRecord and stores them into successive rows of the Excel spreadsheet.

In your Code View window, add the following code:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Private Sub GetDataRecord_Click() Sheet1.Range("A1:BH100").Clear 'clear spreadsheet 'Make sure to add the Reference to IGalil.tlb under Tools - Add Reference 'Security level in Excel should be set to Medium to allow macros Dim g As New Galil.Galil g.Address = "10.0.6.47" 'Connect to IP address of RIO Pocket PLC Dim i As Integer i = 1 For j = 0 To 60 'For loop to list sources and the units Sheet1.Cells(1, j + 1) = g.sources(j) Sheet1.Cells(2, j + 1) = g.Source("Units", g.sources(j)) Next j j = 0 Dim DataRecord As Variant 'Data starts at row 3 and we are collecting 25 samples For i = 3 To 50 DataRecord = g.record For j = 0 To 60 'gets each datapoint Sheet1.Cells(i, j + 1) = g.sourceValue(DataRecord, g.sources(j)) Next j DoEvents 'allows user to do other things during loop Sleep (25) 'sleep for 25 msec Next i End Sub

The first statement just declares a Sleep function. The GetDataRecord_Click() subroutine is what runs the code to grab the RIO datarecord and display it to the screen. Adjust the values of the For loop to the desired number of samples and the Sleep timer can adjust the desired sample rate.

Clicking on the Button should fill up Sheet1 with data like this:

 

The next step is to take advantage of the many chart & report functions of Excel that display the data in a format that’s easier to read. Here’s an example of graphing the analog output while it is performing a sine wave. The dmc code used to generate the sine wave on the analog output is located under the I/O section of the sample code library here: http://www.galil.com/learn/sample-dmc-code

(Click to see video)

As a last step, for Excel to allow the VB code to run, the Macro Security level needs to be set to "Medium". To do this, go to Tools -> Macro -> Security.... and then click on the "Medium" level as shown here: Although using Microsoft Excel does have its limitations, the big benefit of using this method to build a data-logging application is that it is quick and easy and allows the user to be generating graphs from their I/O in just a few minutes.