How to Read RS232 to Excel Macro VBA Data Acquisition

Last Updated: April 1, 2019

This tutorial explains how to establish a bidirectional real-time communication between Microsoft Excel and your RS232 COM Port devices like a Scale, Proximity reader, Barcode reader, temperature sensor, Caliper, Micrometer, Gage. This solution uses the software "Bill Redirect" with the "Excel Plugin" to easily connect your peripheral and receive and send the data. The communication between Microsoft Excel and your device is made via a direct DDE link. An Excel Macro can be called after each data received from your device to validate the data received. This solution explains how you can send a command via VBA to your device and totally control your device. No programming or additional hardware required!

Part 1

Part 1 of 6:
Install Software (Establish RS232 & DDE Communication)

1
Download and install Bill Redirect software: http://www.billproduction.com/Bill_COMtoKB.ZIP. This software is used to establish the communication with your RS-232 COM Port device .
2
Download and install the Excel plugin. This is at http://www.billproduction.com plugin_DDE.zip. This plugin software is used to establish the communication with Microsoft Excel via DDE

Part 2

Part 2 of 6:
Bill Redirect Software (General Configuration)

1
Start the software: Bill Redirect. The default password to edit the configuration is: www.billproduction.com.
2
Disable all section except the Serial Port and Plugin.
- In section "Serial Port" set the switch to: On.
- In section "Plugin" set the switch to: On.

Part 3

Part 3 of 6:
Bill Redirect Software (RS232 Serial Port configuration)

1
Click on: Serial Port Configuration.
2
Enter the Serial Port Number where your device is connected.
- If the background color is green, it means that the serial port works and the port is open.
  - Select the Bauds, Parity and Databits configured in your device.
3
Click on button: Save Configuration
- If you use a RS-232 to USB converter to connect your device on the computer, it is important to use a converter with FTDI chipset for a good stability !

Part 4

Part 4 of 6:
Microsoft Excel (General Configuration)

1
Start: Microsoft Excel
. This solution is compatible with all Excel version and language !
2
Create a in Excel a: new blank document.
3
Make sure that the sheet name is: Sheet1.
4
Save your Excel document to: C:\BillProduction.CFG\MyFile.xlsx.

Part 5

Part 5 of 6:
Excel Plugin Configuration

1
Start the Excel Plugin
. The default password to edit the configuration is: www.billproduction.com.
- Important: The TCP Connection Status must indicate: Connect. If it is not to Connect then make sure that Bill Redirect Software is open.
- In trial mode you must restart the software Bill Redirect after each 15 minutes to continue your test."
2
In the Excel Plugin click on the folder icon and select your Excel File created at step before.
- If all is okay, the DDE Connection Status indicate: Connect.
- At this point the communication is establish with your Device via Excel. The data received from your device is sent in Excel.
3
Test your communication with Excel. Use the button: Test DDE. To see what your device sends or receive, use the section Debugger in Bill Redirect.

Part 6

Part 6 of 6:
Several Option Are Available

1
Consider example #1:' Call an Excel Macro after each data received:
- Simply enter your macro name in the field "Run macro:"
- To test the call of your macro use the button at right.
2
Consider example #2:' Send a command to your device via VBA:
3
To send data command from Excel to your device use this VBA Excel source code:
- ChannelNumber = DDEInitiate("BPEXCEL", "BPEXCEL")
- DDEExecute ChannelNumber, "{TX_SERIAL[Hello Word !{ASCII:13}]}"
- DDETerminate ChannelNumber
- Replace the command Hello Word !{ASCII:13} by the command you want.
- Supplemental documentation.
- Excel Plugin full manual with all explanation: http://www.billproduction.com
- /Bill_DDE_over_Ethernet.pdf.
- Bill Redirect Software full manual with all command:
  http://www.billproduction.com
- /Bill_Redirect_Manual.pdf.