How to Scan Barcodes to Microsoft Excel Spreadsheet: Tips and Apps

What Are Barcodes, and Why Use Them in Excel?

What is a Barcode?

Barcodes are graphical representations of data that can be scanned and decoded using a barcode reader. In the context of an Excel spreadsheet, utilizing barcodes can significantly enhance inventory management and data organization. Excel is a straightforward yet powerful tool for small businesses, enabling the automation of various routine accounting tasks.

How Can Barcodes Enhance Data Management in Excel?

Furthermore, for advanced users, VBA can be utilized to automate the process of data entry from scanned barcodes into Excel sheets.

What Equipment Do You Need to Scan Barcodes into Excel?

To effectively scan barcodes into Microsoft Excel, various equipment and tools are necessary. First and foremost, one must acquire a reliable barcode scanner that is compatible with your operating system. This device will enable users to scan a barcode, but they represent captured data in two common ways:

• Transmit a bar code as keystrokes to any application. This process is straightforward; however, it presents two notable drawbacks. Firstly, the keystrokes must be directed to the active application. If a window has been minimized, the code will not be received. Secondly, it is important to note that not all codes can be articulated as a series of keystrokes. Certain QR or DataMatrix codes are intricate and may incorporate specialized control characters.
• Emulate a virtual COM port in your system and provide a sequence of bytes or characters after each scanning. This is the most powerful way, but requires additional tools or program to transfer this data to Excel.

Do You Need a Dedicated Barcode Scanner, or Can You Use a Smartphone?

The dedicated bar code scanner, being a specialized instrument, is the preferred choice for this task. It has been specifically engineered for this purpose. Additionally, the ergonomic design and scanning speed of this specialized equipment are noteworthy.

Yes, you can find various apps on Google Play that function as a simple scanning app to streamline this process. But they don't have a direct connection with Excel. So, you'll return to the situation when you need another tool to get data from your smartphone and send to Excel on your computer.

How Can You Ensure Compatibility with Microsoft Excel?

Any scanner is compatible with Excel; however, its suitability is contingent upon your particular usage scenarios. What barcodes do you usually scan? If you are primarily using EAN-13, UPC, Code 3 of 9 or similar barcodes, any scanner will suffice. If you want to use QR codes, you should find a reader that can emulate both a keyboard and a virtual COM port. This will provide you with greater flexibility and potential for future upgrades.

Which Apps Can Help You Scan to Excel?

AGG Software Barcode Capturing Apps

The choice is simple:

• Advanced Serial Data Logger software is used with scanners that emulate a virtual COM port (almost all USB, Bluetooth, or Wi-Fi connected).
• You need the USB HID Logger application if your scanner works in the key emulation mode. It can intercept data from your reader.

Both loggers can additionally save captured data to a log file, open the Excel spreadsheet if necessary, or transform a received barcode before feeding to Excel.

How Do These Apps Integrate with Excel?

Our data loggers provide multiple methods for transmitting barcode data to Excel:

• Input keystrokes directly into the currently active cell within the spreadsheet. This approach is useful, for instance, when converting data from a COM port into keystrokes.
• Populate a column with barcodes that have been scanned in sequence, which is particularly advantageous during cataloging or inventory tasks in your warehouse.
• Insert a code into a designated cell in a spreadsheet while operating in the foreground.
• Transfer a value to a specified cell using Dynamic Data Exchange (DDE), a traditional yet still effective method.
• Integrate an ActiveX barcode add-in within your Excel document to facilitate the processing of scanned codes through your VBA code and ActiveX API.

Are There Free Options Available?

Both utilities are provided at no cost for the initial 100 processed barcodes, irrespective of their length or type. Following this limit, it is necessary to restart the program to continue its use. While this may be inconvenient for applications with extensive usage, a license can be obtained at a reasonable cost.

How to Set Up Your System for Scanning Barcodes into Excel?

What Are the Initial Steps to Configure Your Scanner?

1. Configure your device to work in the appropriate mode (keyboard emulation or virtual COM port). Usually, the manual contains a page with barcodes to configure the device.
2. The COM port mode may require to download and install a special virtual COM port driver from the device's manufacturer.
3. In the keyboard emulation mode, your reader appears in the Devices Manager as a separate HID device in the corresponding group.

How to Use Excel to Prepare Your Document and Start Scanning?

It depends on your integration method. In the most simple case, you can:

1. Open Excel.
2. Design the document for your task (add headers, buttons, etc.).
3. Select the destination column and cell.
4. Use a scanner to and start scanning.

If you use an Excel add-in, the application will ask you to let the 3rd party code run.

What Settings Should You Adjust for Optimal Performance?

• The recommended limit of data rows per a document is about 10000 records. Otherwise, real-time readings may appear with a delay in a target document.
• Do not open two copies of the document at the same when your scanner emulates a COM port. Keyboard emulation will send all captured codes to the foreground document.

What Troubleshooting Tips Can Help If Scanning Doesn't Work?

Barcode Scanner Hardware Problems

The device does not appear in the Devices Manager at all:

• Check power and cable. Maybe, it is damaged.
• USB devices - try to connect to another USB port.
• The device appears with an asterisk in Devices Manager - you need a special driver from a manufacturer.

Hint for old or no name devices. Open the device properties in Devices Manager, go to the "Info" tab and switch to the "Device ID" page. You can see a string like "USB\VID_2516&PID_0015" there. You can use this VID and PID to find a driver in Google or in Microsoft Community.

Software Problems

If your scanner does not recognize a code, unfortunately, you should upgrade it. It is the hardware limitation. In rare cases, "qrcode", UPC, "3 of 9" barcodes can be separately enabled or disabled in the scanner parameters. Verify it in the documentation.

What Should You Check If Your Scanner or App Isn’t Recognizing Barcodes?

If your application does recognize a barcode, it may check the format of your barcode. In the context of Excel, it means that you should check your Visual Basic code. If you use another application, it may internally check the format of a code, special characters, check digits or characters at the specific position (QR or DataMatrix codes). Our software can dynamically in real-time transform or customize the captured code and re-send the modified code to a target application.

What Resources Are Available for Further Help?

Please look at our tutorials about barcode data parsing and handling:

• Parse a barcode and write to a log file with a date/time stamp.
• Process data from a scanner.
• Serial port data aggregation. Combine data from two different COM ports.