Auto Detect Data, Auto Print Data, Auto Erase Data Loop

[Gamma's and Ruiter's]

I have a barcode scanner hooked up to excel. It decodes the encoded
text in the barcode it scans. The barcodes scanned may have up to
40-50 lines and 600 + characters of data. It will all stay in column A
and could populate all the way down to line 40-60. I'm trying to write
a macro for this with not too much luck.

When the information is scanned, the macro needs to start at A1 sense
how many cells were populated with data, maybe it fill A1 - A12 or
maybe A1-A60, it needs to auto prints those cells, then erases the
information that was scanned in and awaits the next scan, and does it
all over again until i stop it. It only prints once when it receives
new data.

Example: Basically its an autoprint as if you were to do a price check
in a store. you put the item under a scanner and the screen shows you
the price when its scanned, how ever instead of the screen output i
want it to print to a printer.

 

[Gamma's and Ruiter's]

Office Mac 2011

 

[Ron Rosenfeld]

I have a barcode scanner hooked up to excel. It decodes the encoded
text in the barcode it scans. The barcodes scanned may have up to
40-50 lines and 600 + characters of data. It will all stay in column A
and could populate all the way down to line 40-60. I'm trying to write
a macro for this with not too much luck.

When the information is scanned, the macro needs to start at A1 sense
how many cells were populated with data, maybe it fill A1 - A12 or
maybe A1-A60, it needs to auto prints those cells, then erases the
information that was scanned in and awaits the next scan, and does it
all over again until i stop it. It only prints once when it receives
new data.

Example: Basically its an autoprint as if you were to do a price check
in a store. you put the item under a scanner and the screen shows you
the price when its scanned, how ever instead of the screen output i
want it to print to a printer.

Does your version of Excel support VBA?

Perhaps you can work with this event-triggered macro. It assumes that all of the data is pasted in at once. If it is read in line-by-line, then you'll need a different "trigger" to know when the reading in has finished.

I have enabled print preview for debugging, but you don't need this.

To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
Then paste the code below into the window that opens.

===========================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rBarCodeData As Range
Set rBarCodeData = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))

Application.EnableEvents = False

If Not Intersect(Target, rBarCodeData) Is Nothing Then
With rBarCodeData
.PrintOut Preview:=True
.ClearContents
End With
End If

Application.EnableEvents = True
End Sub
===============================

 

[GammaRuit]

this works but if i have a scan come in thats say 11 lines long it
prints 1 line then it prints the next. doesn't wait for the whole scan
to come in to print it.
also it doesn't go back to a1 with the blinking cursor to await the
next input.

close though..
any ideas?

cv

 

[GammaRuit]

my version does support VBA

 

[Ron Rosenfeld]

this works but if i have a scan come in thats say 11 lines long it
prints 1 line then it prints the next. doesn't wait for the whole scan
to come in to print it.

As I indicated in my initial response, "If it is read in line-by-line, then you'll need a different "trigger" to know when the reading in has finished."

You have not provided any information regarding this.

also it doesn't go back to a1 with the blinking cursor to await the
next input.

I wasn't aware that Mac versions of Excel have "blinking cursor". Why do you require this?

If you want to return to A1, you can add something like:

With Range("A1")
.Activate
.Show
End With

to the end of the macro.

close though..
any ideas?

Not with the information you have so far supplied.

 

[GammaRuit]

Apologies Ron,

1. Its read in line by line.
2. Your right about the blinking cursor. It just blinks when I double
click the cell. Forget that. I just want to go back to A1 as you
showed me how in the previous message.

cv

 

[Ron Rosenfeld]

1. Its read in line by line.

How do you know when the last line has been read in?

 

[GammaRuit]

How do you know when the last line has been read in?

Every scan is a different amount of data.

It constantly populates line after line. When its done it stops. There
is nothing in the decoded text to indicate that its finished.

I can program my barcode generator to print out something specific as
the very last line of the decode. Maybe END or COMPLETE or THANK YOU

 

[Ron Rosenfeld]

I can program my barcode generator to print out something specific as
the very last line of the decode. Maybe END or COMPLETE or THANK YOU

How do you get the information into Excel?

I'm thinking you could just run a test after each line, and print out the range after you read or Last Line code; adapting from what I've already written.

 

[GammaRuit]

How do you get the information into Excel?

I'm thinking you could just run a test after each line, and print out therange after you read or Last Line code; adapting from what I've already written.

I click A1

I take my scanner scan a QR Code. say my QR is encoded with:

Hi
How
Are
You


It prints it out line by line as it you were typing really fast.

A1 Hi
A2 How
A3 Are
A4 You

 

[Ron Rosenfeld]

I click A1

I take my scanner scan a QR Code. say my QR is encoded with:

Hi
How
Are
You


It prints it out line by line as it you were typing really fast.

A1 Hi
A2 How
A3 Are
A4 You

How does the information get from the scanner to your Excel worksheet?

 

[Ron Rosenfeld]

I click A1

I take my scanner scan a QR Code. say my QR is encoded with:

Hi
How
Are
You


It prints it out line by line as it you were typing really fast.

A1 Hi
A2 How
A3 Are
A4 You

Although with a better description of your interface, we might be able to do something more efficient, try this (entered the same as the previous):
Note that sLastLine can be defined as you like and that it is case-sensitive

=======================
ption Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rBarCodeData As Range
Dim AOI As Range
Const sLastLine As String = "END"
Set AOI = Range("A:A")

Application.EnableEvents = False

If Not Intersect(Target, AOI) Is Nothing And _
Target.Value = sLastLine Then
Set rBarCodeData = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
With rBarCodeData
.PrintOut Preview:=True
.ClearContents
End With

With Range("A1")
.Activate
.Show
End With

End If
Application.EnableEvents = True
End Sub
========================

 

[GammaRuit]

Works Ron ! Thank You

Do I need to Define A Header and Footer within the Macro?
I tried to apply it in the layout and it won't work.

What would be a better description of the interface be? The type of
Scanner?
Would you like to see a video of how it works so you can understand
the process?

Chris

 

[Ron Rosenfeld]

Works Ron ! Thank You

Do I need to Define A Header and Footer within the Macro?
I tried to apply it in the layout and it won't work.

What would be a better description of the interface be? The type of
Scanner?
Would you like to see a video of how it works so you can understand
the process?

Chris

Glad you've got it working.

So far as the Header and Footer are concerned, I don't understand why you cannot set it up in the "layout". I can go to the Page Layout tab of my ribbon; select Print Titles; enter header and footer; and it "sticks".

If you want to set it up in the macro, you certainly can, but it shouldn't be necessary:
=========================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rBarCodeData As Range
Dim AOI As Range
Const sLastLine As String = "END"
Set AOI = Range("A:A")

Application.EnableEvents = False

If Not Intersect(Target, AOI) Is Nothing And _
Target.Value = sLastLine Then
Set rBarCodeData = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
With rBarCodeData
With .Worksheet.PageSetup
.CenterHeader = "": .RightHeader = ""
.CenterFooter = "": .RightFooter = ""
.LeftHeader = "This is my Barcode output"
.LeftFooter = "Printed at " & Format(Date, "Short Date") & _
" " & Format(Time, "h:mm AM/PM")
End With
.PrintOut Preview:=True
.ClearContents
End With

With Range("A1")
.Activate
.Show
End With

End If
Application.EnableEvents = True
End Sub
=============================

So far as the interface is concerned, all you've written is that you scan something in and it appears in your Excel instance in certain cells.

That is not sufficient information for anyone to reproduce what you are doing. For example, if I have a computer here, turn it on and run Excel, and then run a scanner, nothing will appear in my Excel.

There has to be something going on that routes the information from the scanner to a particular instance of Excel, and directs it towards the sequential cells. This usually involves a VBA macro, or some other code.

However, since you seem to have the macro working satisfactorily, there is no need to go further. (Perfect is the enemy of "good enough"). But if you can't define a header or footer, even within the macro, there is possibly something in those routines that is interfering. In that case we would have to know all the details.

 

[GammaRuit]

I wasn't seeing the header and footer because i didn't input the data
on the left header.
I'm using a receipt printer right now so it was off the page.

So i put the header and footer on the left and it prints. but it moves
into where my data is printing as well.
it is overlapping slightly. i would assume i just have to play with
formatting? nothing to do with macro code right?

cv

 

[Ron Rosenfeld]

I wasn't seeing the header and footer because i didn't input the data
on the left header.
I'm using a receipt printer right now so it was off the page.

So i put the header and footer on the left and it prints. but it moves
into where my data is printing as well.
it is overlapping slightly. i would assume i just have to play with
formatting? nothing to do with macro code right?

cv

I don't think it has to do with macro code. Playing with formatting might work, maybe a larger margin. Or there may be something in your printer driver.