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.
Marked for Life: A Glance at Some Common Direct Part Marking Methods
Direct part marking is crucial for enabling the traceability of products and the parts comprising...
Do Your DPMs Make the Grade? Understanding the 2D Barcode Verification Parameters in ISO 15415
Direct part marks (DPMs) and other two-dimensional codes can vary widely in their readability. Fr...
How X-Mode Brings Out the Best in Barcodes
Barcodes have a unique challenge. Whether printed, etched, engraved or stamped onto their substra...