Printing to a multiple-drawer printer

[DaveOMatic]

Hello Board, does anyone have some code tips regarding the selection of specific printer drawers in a multi-drawer printer? The macro-recorder is out too lunch in helping me on this one.

I have an HP 4650 networked printer that is accessed from multiple machines running Windows XP Office Professional.

I am printing a label sheet ( sheet 3 of a 4 sheet workbook) .

I solved the problem of printing the label sheet via macro from different machines, as below (showing partial code in Sub):

Sheets("Lbl1").Visible = True
Sheets("Lbl1").Select
With Selection
If ActivePrinter = "HP Color LaserJet 4650-2nd Floor on Ne01:" Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"HP Color LaserJet 4650-2nd Floor on Ne01:", Collate:=True
ElseIf ActivePrinter = "HP Color LaserJet 4650-2nd Floor on Ne02:" Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"HP Color LaserJet 4650-2nd Floor on Ne02:", Collate:=True
ElseIf ActivePrinter = "HP Color LaserJet 4650-2nd Floor on Ne03:" Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"HP Color LaserJet 4650-2nd Floor on Ne03:", Collate:=True
End If
End With
Sheets("Lbl1").Visible = False


I have macro buttons on a title workbook sheet, and one of the buttons initiates printing of Avery Labels stored in drawer 4 of the printer ( the actual label sheet is hidden from the user ). However, because of XP registry settings changing by different users, the labels keep trying to print from drawers other than the label drawer. I have programmed the printer paper handling a thousand different ways and perhaps a specific code snippet similar to " Collate:= True " in drawer selection might solve my problem.

My apologies board in the length of question, did not know how to phrase it better.
Thanks in advance.
DaveOMatic

 

[BrianB]

This might help

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101


'=====================================================
'InstrRev function is not in XL97 - this is my custom version :-

Function InStrRev(str1, str2)
    Dim MyLen As Integer
    MyLen = Len(str1)
    InStrRev = 0
    For c = MyLen To 1 Step -1
        If Mid(str1, c, 1) = str2 Then InStrRev = c
        Exit Function
    Next
End Function
'

 

[DaveOMatic]

Thanks BrianB very much for the info. I did find the web site you suggested but the exact link as posted shows up as page not found...I'll keep hunting around there.

As for my posted code, I need more practice on correctly posting it using the Code button above, I'm still relatively new at this, sorry bout' that board.

And your code you graciously offered, could you break down some of the symbolism a little more please? What is Str1 and Str2 ?
Regards and Thanks
DaveOMatic

 

[BrianB]

I again tried doubleclicking the link in my message a couple of times and got to the page without problem. I am reluctant to copy/paste the code from there to here because, although it seems relatively simple, I understand the huge amount of work involved in making it so. I think the site deserves the credit. I tried doing the job myself, but did not have the motivation of actually needing it to put in the necessary work.

There is no need to get bogged down with my code. There is a VB function called Instr() which finds a string within a string starting from the left. It looks like newer versions of Excel have a similar one called InStrRev() that finds from the right. This is used several times in the main code. All that is needed is to copy paste my code underneath the code copy/pasted from the website. It will be worth your while persevering because I have done just that and it worked first time. There is also code there to select a tray (which I have not tried).

 

[DaveOMatic]

Hello BrianB, I was rereading your very much appreciated help in coding printer drawers here and the use of the Instr function. At the time of your last reply I did not see it and neglected to reply.

Many thanks for your insightful help. I have used the Instr function and it has solved many problems for me in my rudimentary coding but it is getting better with your's and the board's excellent posts.
Regards
DaveOMatic