ExcelBanter - can i import barcode data directly to excel

This is from a previous post by Rob. Hope this helps you!

Using a serial comm device
Rob

----------------------------------------------------------------

First off, if you hav'nt already done so - you will need to obtain the
MSCOMM32.OCX Active X library.

Start here - http://www.yes-tele.com/mscomm.html

You will need to register it. Go to command prompt and type the
following -

regsvr32 C:\Windows\System\MSCOMM32.OCX

You should get a message to indicate that the control has been
registered.

Then you may need to update the registry with this key -

[HKEY_CLASSES_ROOT\Licenses\4250E830-6AC2-11cf-8ADB-00AA00C00905]
@ = "kjljvjjjoquqmjjjvpqqkqmqykypoqjquoun"

As far as the registry stuff goes, I am no expert. This worked for
me...and as far as I know it is legal. But if you have any
concerns...or if you are not familiar with working within the
registry...I would suggest doing some more research first!

Now....you should be ready to program with MSCOMM!!!

Open up a new Excel workbook. Make sure you can see the 'Control
Toolbox (ViewToolbarsControl Toolbox).

Rename your worksheet 'SerialPort'.

Once you can see the toolbox- there should be a little toolbox icon.
Click the icon and select 'Microsoft Communication Control' from the
list.

The cursor will change, allowing you to draw a box on the worksheet.
This is the control. It does not matter where you place it - as when
you open the workbook this will not be visible to the user.

Once you have created the control, right-click and select 'View Code'.

You should see something like -

Private Sub MSComm1_OnComm()

End Sub

This is the 'OnComm' event and tells excel what to do when data is
received from the serial port. We will come back to this later. Lets
write some code to open a port up....

Insert the following into a new sub under the OnComm sub -

Sub OpenPort()

'Open the COM Port with the relevant settings

Worksheets("SerialPort").MSComm1.CommPort = 1
Worksheets("SerialPort").MSComm1.Settings = "9600,n,8,1"
Worksheets("SerialPort").MSComm1.RThreshold = 1
Worksheets("SerialPort").MSComm1.InBufferSize = 4096
Worksheets("SerialPort").MSComm1.PortOpen = True

End Sub

The sub above will configure the port when you try and open it.
'CommPort' is the port number your device is connected to. 'Settings'
are the device setting (baud rate, parity, etc) and are usually in the
device documentation (you can also use the device with Hyperterminal to
get these settings).

PortOpen = True tells Excel to open the port with the above settings.

RThreshold is what we are interested in here. By setting it to '1' we
are telling Excel to fire the 'OnComm' code whenever data is received
from the serial port.

The way I call the port open sub, is to have a worksheet onchange
event. I only want the port to open when the user selects a cell in a
particular column range, so I am using the following -

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Columns("A:A")) Is Nothing Then

If Target.Value = "" Then

Call OpenPort

End If
End If

Application.EnableEvents = True

End Sub

This piece of code must sit against the 'Worksheet' 'Selection Change'
section. Use the dropdown boxes at the top of your VBA editor to select
this.

So now, if a cell in column 'A' is selected, the port will be opened
with my settings. As RThreshold is set to '1' - the OnComm code will be
called whenever I try to read from the cheque reader and am in the 'A'
column.

Now to tell it do something when data is received....

Go back to your OnComm make it look like -

Private Sub MSComm1_OnComm()

If Worksheets("SerialPort").MSComm1.CommEvent = comEvReceive Then
Call GetData
End If

End Sub

The CommEvent - comEvReceive tells Excel that if data is coming in from
the serial device....do something. In this case, Call sub "GetData".

Now, to create sub "GetData" that will grab the information from the
device and place it in your worksheet.

The sub below is very straightforward - but will help get you started!

Private Sub GetData()

Dim MyData As String

Wokrsheets("SerialPort").MSComm1.InputLen = 0

MyData = Worksheets("SerialPort").MSComm1.Input
ActiveCell.Value = MyData

MyData = ""

Worksheets("SerialPort").MSComm1.PortOpen = False

End Sub

The 'InputLen' setting tells Excel how much data to read from the
serial device. Setting it to zero tells it to keep reading until the
end of the file (ie it gets all of the data). If you know your string
is always going to be 10 digits...you could set this to 10. Or you may
need a function to pull the characters one at a time until you get to a
specific value. It all depends on the device and what you are trying to
achieve....

And you are done! You should now have a very basic working script to
read data from a serial device and place it into your worksheet.

"colt" wrote:

Does excel have an interface to directly accept data from a barcode reader