Industrial Automation



We frequently get support questions about how to acquire and store data generated by our machine vision or Auto ID readers in various file formats. Microsoft Excel is a tool widely used by many businesses to manage, process, and share data. In this post I will demonstrate a way to integrate our Ethernet devices - such as the Vision HAWK and Vision MINI Xi Smart Cameras, and our QX Hawk and MINI Hawk Auto ID readers - and their output into an Excel sheet.

How do you actually retrieve and archive the data that a barcode scanner or smart camera generates? This is a question that we as Solution/Application Engineers often face. In particular, is there a direct way to get the data straight into an Excel spreadsheet without the need for any temporary flat-file storage? Such a solution would circumvent any intermediate data import or other data staging procedure, creating a lean framework for data capture and management.

The situation described above is a potential data management nightmare. Microsoft Excel is by far the most widely-adopted business intelligence tool across all domains of commercial enterprise. But how do you make a smart camera or barcode scanner write its output directly into an Excel spreadsheet without any flat-file data staging or third party software components?

Excel makes use of the Microsoft Windows event-driven programming language called VBA (Visual Basic for Applications). This gives access to the Windows API and the many functionalities offered in the Windows .dll files. In Excel this facility is referred to as "writing a macro".

Data arriving over TCP/IP to a Windows host system is being managed by the Windows Socket or "Winsock". This Winsock API makes it possible to read and write data across TCP/IP connections.

As with most programming endeavors, "all roads lead to Rome," as the saying goes, and I took one of them (which, admittedly, mostly involved copying and pasting from people who know more than I do). The VBA code contains one standard module and a class module (see Figure 1). The standard module sets up the connection by calling the class module with arguments such as IP address and port number via user input. The class module initializes the connection and manages the data capture and data writing into the spreadsheet.

The heart of the class module code is as shown below where the connection is created in Connect and data is received and written in the Winsock1_DataArrival Sub:

Public WithEvents  Winsock1 As MSWinsockLib.Winsock

Public Sub Connect(address  As String, port As Long)
     Winsock1.Connect address, port
     Debug.Print Winsock1.State
End Sub

Public Sub  Winsock1_DataArrival(ByVal bytesTotal As Long)
     Dim data As String
     Dim col As String 'Sheet Colum
     Dim row As Long 'Sheet Row
     col = Mid(ActiveCell.Address, 2, 1) 'Current  active Column
     row = ActiveCell.row 'Current active Row
     ic = ic + 1 'Event Counter
     Winsock1.GetData data, vbString, bytesTotal
     Set UpdateCell =  Sheets("Sheet1").Range(col & ic + 1)
     If Not pUpdateCell Is Nothing Then
          pUpdateCell.Value = Null 'Reset the  Cell content
          pUpdateCell.Value  = pUpdateCell.Value & data 'Add data to cell
          Split col, ic 'Call split and parse  to columns at " ;"
     End If
End Sub

Indeed, putting data in sheet1 is hard-coded here. The parsing of the semicolon separated output is done in a separate sub, essentially performing TextToColumns on the current cell.

The screen captures below show how this has been implemented and how Excel receives the data. In the example below we used a Vision Hawk that verifies a Data Matrix to the 15415 standard. It then outputs the applied ISO standard, the decoded text and the "˜"compact report&rsquo"; of the verification result. The data is transmitted using TCP/IP on port 49211.

Figure 1. A view of the VBA procedure taking care of the setup of the TCP/IP connection to the camera. To the right the setup of the connection to the camera in Excel.

Figure 2. The Excel sheet capturing data from the Vision Hawk Smart Camera. To the left, the setup of the verification job in AutoVISION.

Please contact me or the Microscan helpdesk for additional information and resources.

Category(s): Solutions & Applications Leave a Comment

Recent Posts

Leave a Comment


Written by on Reply