Send printer escape commands to a printer using VBA

[mikeburg]

Is there a way to send a printer escape command to a printer via VBA?

For example, in Lotus 123 we could put ||\027&l1O in the 1st cell of a
spreadsheet which would set the printer to landscape mode.

Thanks for everyones help! mikeburg

 

[Dave Peterson]

Maybe....

Option Explicit
Sub testme01()
Open "LPT1:" For Output As #1
Print #1, "yourstringhere"
Close #1
End Sub

But why not just page to landscape using file|page setup. If you need a macro,
you can record a macro when you do it manually.

 

[mikeburg]

The problem I am trying to solve is that my HPIIIsi printer is set to
default of 6.5 lines per inch. In Lotus 123, I put an escape comman
of ||\027 &l6D in the 1st cell that set the printer to 6 lines pe
inch. I am having trouble doing that in Excel.

The above code works great when modified as follows:

Sub testme01()
Open "LPT1:" For Output As #1
Print #1, Chr(27) + "&l6D" '(6lpi works!)
Print #1, "This is a test"
Print #1, Chr(12) '(FormFeed works!)
Close #1
End Sub

But I can't get it to print the active worksheet. When I run the abov
code then print the worksheet, I still get 6.5 lines per inch (it'
reset back to it's default). I get the same problem when I put it i
the before print event too.

It's important that I be able to set the print command to 6 lines pe
inch when printing labels & other forms on that particular printer.

Any suggestions? mikebur

 

[Dave Peterson]

First, I don't think I'd use Excel to print labels. I'd use excel to hold my
data, but I'd use all the stuff built into MSWord to print the labels.

MSWord has a nice way to merge data from excel file:

http://www.mvps.org/dmcritchie/excel/mailmerg.htm
http://www.mvps.org/word/FAQs/MailMerge

The first is from David McRitchie and the second is by Beth Melton and Dave
Rado.

And MSWord has lots of support for standard sized labels. Just way more options
that you'd ever think!

And second, if I had to print labels through excel and also send the escape
characters to the printer, then I'd use that same technique for sending the data
to the printer.

Untested!

Sub testme01()
dim myCell as range
dim myRng as range
with activesheet
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

Open "LPT1:" For Output As #1
Print #1, Chr(27) + "&l6D" '(6lpi works!)

for each mycell in myrng.cells
Print #1, mycell.value & "--" & mycell.offset(0,1).value
Print #1, mycell.offset(0,2).value & "--" & mycell.offset(0,3).value
print #1
next mycell

Print #1, Chr(12) '(FormFeed works!) 'I'd hit the button!
Close #1
End Sub

But seriously, take a look at MSWord and printing labels from there. You'll be
pleasantly surprised.