Get data from magnetic card reader to each cell

[simon]

Subject: Re: how to collectly put data in each cell from magnetic card read
11/5/2006 9:20 PM PST

By: simon In: microsoft.public.excel.worksheet.functions


Is there programing do this for me with out me to use text to column manually?

You could use Text-to_Column (on the Data menu) to do this

Try to get data from magnetic card read into excel each cell automatically
every time i scan the card

data read from megnetic card reader as following :
%BAS49247 ^STRONG/MORRI ^ E61158?
each time when card scanned BAS49247 go to column A with out %
STRONG / MORRI go to column B without ^ and E61158 go to column C without
^ and ? And then cursor jump down to column A row 2
thanks

 

[simon]

Try to get data from magnetic card read into excel each cell automatically
every time i scan the card

data read from megnetic card reader as following :
%BAS49247 ^STRONG/MORRI ^ E61158?
each time when card scanned BAS49247 go to column A with out %
STRONG / MORRI go to column B without ^ and E61158 go to column C without
^ and ? And then cursor jump down to column A row 2
thanks

 

[NickHK]

Simon,
Is this card reader is a keyboard wedge type ? Basically simulates keyboard
input into the active control or cell ?

NickHK

 

[simon]

Yes, it's a USB card reader simulates keyboard input into cell .
but it read into cell A1 only with all data at once
%BAS49247 ^STRONG/MORRI ^ E61158?
I need BAS49247 go to cell A1 with out %
STRONG / MORRI go to cell B1 without ^
and E61158 go to cell C1 without ^ and ?
at present time the cursor jump down to cell A2 and want to keep this way
thanks

 

[NickHK]

Simon,
What are the groups of characters separated by ?
A space, multiple spaces ...

NickHK

 

[NickHK]

Simon,
You can adjust this to suit the incoming data :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value)
Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Mid(Temp(1), 2)
Target.Offset(0, 2).Value = Mid(Temp(2), 2)
Application.EnableEvents = True
End If
End Sub

NickHK

 

[simon]

where this code going to or how

Simon,
You can adjust this to suit the incoming data :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value)
Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Mid(Temp(1), 2)
Target.Offset(0, 2).Value = Mid(Temp(2), 2)
Application.EnableEvents = True
End If
End Sub

NickHK

 

[simon]

tried it only data BAS49247 go into cell A1 with out %
all orther ^STRONG / MORRI AND ^E61158 data missing

 

[NickHK]

Well, yes because you have to adjust what you are splitting on .
So if there are no spaces as you say in the earlier post, try :
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = left(Temp(2),len(Temp(2))-1)

NickHK

 

[simon]

Hi:
with your code I tried to run through magnetic card reader it work
perfectly. But i run into orther problem:
- every time I want to delete data on column A it will make a visual basic
error( delete orther column no proplem at all:
runtime error "9": subscript ut of rang
- every time I want to delete datas on a select rang included column A it
will:
Runtime error "13" : type mismatch

code in sheet one:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Temp As Variant

If Target.Column = 1 Then
Application.EnableEvents = False
Temp = Split(Target.Value, "^")

Target.Value = Mid(Temp(0), 2)
Target.Offset(0, 1).Value = Temp(1)
Target.Offset(0, 2).Value = Left(Temp(2), Len(Temp(2)) - 1)
' Temp = Split(Target.Value)
' Target.Value = Mid(Temp(0), 2)
' Target.Offset(0, 1).Value = Mid(Temp(1), 2)
' Target.Offset(0, 2).Value = Mid(Temp(2), 2)
Application.EnableEvents = True
End If
End Sub

look like this code goes into a loop. every time a delete or change in
column A get into error.
Help !

 

[NickHK]

Simon,
That one problem of using a keyboard wedge style input device ; you cannot
differentiate real keyboard input from the device input.
Maybe you should create a userform to accept the device input, process it
then set the cell values. Or use a specific cell for reader input, which you
can then test for first.
Or maybe create a button that sets a global variable, UsingReader=true

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Temp As Variant

If UsingReader=True Then
If Target.Column = 1 Then
'etc...

NickHK

 

[simon]

NickHK,
Yes, I tried to create a button to have a userform to accept the device
input, but it won't work for me, I don't know how. can you help? thank a lot
simon