VB Sorting Macro

[Mable]

Alright, I've asked quite a few questions on my quest to
figure this VB thing out, but I'm completely stumped now.

What I'm trying to do is create a solution for print-tray
issue. The QC people want to automate the process of
sorting documents that need to be printed on different
types of paper, between trays 1 and 2 of an HP 5, for
documents that go up into the 200 page range.

My solution was to insert a white colored 1 or 2 at the
beginning of each page (there's a universal "first-line
blank" margin for the documents) that the macro could find
and sort accordingly.

So I've set up the following macro to search for white-
colored characters, and depending upon the results, send
the page to tray 1 or tray 2.

However, when it searches, it finds either the entire
document (which is all black) or it finds nothing. In a
seperate test environment, where I had two blank pages and
a white 1 on the top of page 1 and a white 2 on the top of
page 2, it found both characters and all of the space in
between and then still didn't process the sorting portion.

I know this code is incomplete (for example, if it were to
work right now it'd just send the entire document to each
print tray until it was done searching) but basically what
I need to know is this: Why doesn't it search one object
at a time? Why doesn't it find the '1' at the top of page
one, process, and then search for the '2' ? I suspect it
has something to do with my coding in of the myRange
object.

Code:

Sub SortPrintTrays()

'Gets page count
Dim intPages As Integer
intPages = ActiveDocument.BuiltInDocumentProperties
(wdPropertyPages)

'Creates placeholder for White characters
Dim trayMarker As Integer

'Creates variable for the entire document
Dim myRange As Range
Documents("Document1").Activate
Set myRange = ThisDocument.Content


'Test Message
MsgBox "This document contains " & Documents.Count & "
pages."

'Sorting FOR Loop, finds each White character and prints
page accordingly'
For Sort = 1 To intPages
With myRange.Find
.Forward = True
.Font.Color = wdColorWhite
.Execute
If .Found = True Then
GoTo out
End If
End With
GoTo startover
'This is the part where it sends the Job to tray 1 or 2,
depending upon the FIND results
out:
MsgBox ("Found " & myRange)
If myRange = "1" Then
MsgBox ("Printing to Tray 1!")
Options.DefaultTrayID = wdPrinterUpperBin
ActiveDocument.PrintOut
Else
MsgBox ("Printing to Tray 2!")
Options.DefaultTrayID = wdPrinterLowerBin
ActiveDocument.PrintOut
End If

startover:
Next Sort

End Sub

Thanks.

 

[Helmut Weber]

Hi Mable,
have a look at this as a start.
There my be faster, more elegant solutions.
At last it works here and now. Improvements welcome.
And take care of selecting trays,
it may be tricky, as you see from the constants
of paper trays in my example, where tray 1 is 257.
You find help for "enumpaperbins" at:
http://www.gssg.de/
"Tips", "Drucker", "Papierfaecher"
.... Printer, Papertrays or Paperbins, whatever.
Sub Test321()
Dim oRng As Range
Dim s As String
Set oRng = ActiveDocument.Range
With oRng.Find
.Font.Color = wdColorWhite
While .Execute
s = oRng.Text
oRng.Collapse direction:=wdCollapseEnd
oRng.Select '!
ActiveDocument.Bookmarks("\Page").Select
If oRng.Text = "1" Then
' Options.DefaultTrayID = 257 ' Tray 1
' ActiveDocument.PrintOut Range:=wdPrintSelection
Else
' Options.DefaultTrayID = 258 ' Tray 2
' ActiveDocument.PrintOut Range:=wdPrintSelection
End If
oRng.End = ActiveDocument.Range.End
Wend
End With
End Sub
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

 

[Mable]

I copied and pasted this into my test environment, but it
doesn't execute the search. It looks good, certainly a lot
cleaner than my solution, but it doens't seem to want to
execute the search when I perform a Step-Into. Any ideas?

 

[Helmut Weber]

Hi Mable,
don't forget to reset search options,
which seems to to work with range.
Sub Resetsearch()
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute
End With
End Sub
---
An slightly improved version:
Sub Test321()
Dim oRng As Range
Dim s As String
Resetsearch
Set oRng = ActiveDocument.Range
With oRng.Find
.Font.Color = wdColorWhite
While .Execute
s = oRng.Text
oRng.Collapse direction:=wdCollapseEnd
oRng.Select
ActiveDocument.Bookmarks("\Page").Select
If s = "1" Then '!
Options.DefaultTrayID = 257 ' Tray 1
Else
Options.DefaultTrayID = 258 ' Tray 2
End If
ActiveDocument.PrintOut Range:=wdPrintSelection
oRng.End = ActiveDocument.Range.End
Wend
End With
End Sub
---
Note that ActiveDocument.Bookmarks("\Page").Select
does not print headers or footers, at last not here
and now. I am assuming, that your white characters
are in the main story, not in a header or footer.
But wouldn't be a big thing to take care of that, too.
And for speed, a lot depens on how many different white
characters there are in your doc. It may be faster,
just to print all "1-pages" from tray 1 and again all
pages from tray 2.
HTH
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

 

[Helmut Weber]

....
still shorter,
this time prints headers and footers, too.
---
Dim oRng As Range
Dim s As String
Resetsearch
Set oRng = ActiveDocument.Range
With oRng.Find
.Font.Color = wdColorWhite
While .Execute
s = oRng.Text
oRng.Collapse direction:=wdCollapseEnd
oRng.Select
If s = "1" Then
Options.DefaultTrayID = 257 ' Tray 1
Else
Options.DefaultTrayID = 258 ' Tray 2
End If
ActiveDocument.PrintOut Range:=wdPrintCurrentPage
oRng.End = ActiveDocument.Range.End
Wend
End With

 

[Mable]

Thanks a lot for your continued support. I've integrated
sections of my code with the examples you have provided
and have come up with an effective, working solution. The
document properly prints from trays 1 and 2 when it's
supposed to.

Is there a place where I can find a list of those paper
tray codes? I designed this to work with an HP 5 because
that's what the QC people told me it would be for, but it
turns out they want to use it to print to a Xerox
thing...I can't remember the exact model, might be a
DocuPrint 90-something...but if I could find a list of
tray codes I could easily make this program work for any
printer in the office, a scenario which will probably
happen in the near future.

I saw the link from your first post, and I picked up the
Enumpaperbins VBA, but I cannot get it to work because A)
I am new to VB and B) I don't understand German at all.

Thanks for your help.

 

[Jonathan West]

Thanks a lot for your continued support. I've integrated
sections of my code with the examples you have provided
and have come up with an effective, working solution. The
document properly prints from trays 1 and 2 when it's
supposed to.

Is there a place where I can find a list of those paper
tray codes? I designed this to work with an HP 5 because
that's what the QC people told me it would be for, but it
turns out they want to use it to print to a Xerox
thing...I can't remember the exact model, might be a
DocuPrint 90-something...but if I could find a list of
tray codes I could easily make this program work for any
printer in the office, a scenario which will probably
happen in the near future.

I saw the link from your first post, and I picked up the
Enumpaperbins VBA, but I cannot get it to work because A)
I am new to VB and B) I don't understand German at all.

The paper tray situation in Windows printer drivers is a total mess. Every
printer has its own set of ID codes. Take a look at this article for how to
find out what codes are available for any specific printer.

Controlling the Printer from Word VBA
Part 1: Using VBA to Select the Paper Tray
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101

 

[Mable]

I actually have stumbled across that article, and I was
able to implement that VB code, but I couldn't get it to
give me any numbers, just names. I assume I'm missing
something? I made it so the code would find the trays and
place them into a listbox, as per the article.

 

[Helmut Weber]

Hi Mable,
having VB 6.0 on my machine, I didn't
think of "EnumPaperBins.vbp" as VB-Program.
I am sorry, I don't know of anything comparable,
though there must be something similar somewhere.
It will need VB as it was downloaded from gssg.de.
Maybe there is a machine with VB on it, where
you work. In that case, everything would be alright,
as it probably gets the few words it needs from the
printer driver, or it is bi- or multilingual anyway.
(It lists trays for installed drivers only).
But I'd suggest to look for VB or ask gssg.de for
a ready to use version. They may ask for an handful
of dollars, but it is really worth it.

 

[Jonathan West]

I actually have stumbled across that article, and I was
able to implement that VB code, but I couldn't get it to
give me any numbers, just names. I assume I'm missing
something? I made it so the code would find the trays and
place them into a listbox, as per the article.

The GetBinNames routine gets the list of names for the available papertrays.
The GetBinNumbers routine returns the equivalent list of papertray ID codes,
which can be used for setting the FirstPageTray and OtherPagesTray
properties of the PageSetup object.

 

[Mable]

Alright, I set it up to try to deliver the bins numbers to
a listbox, but when I try to run it, it says "Ambiguous
Name Detected: GetBinNumbers" and halts execution. Any
ideas?

 

[Jonathan West]

Alright, I set it up to try to deliver the bins numbers to
a listbox, but when I try to run it, it says "Ambiguous
Name Detected: GetBinNumbers" and halts execution. Any
ideas?

That means you have the same routine included twice within your project
somewhere, or at least two routines of the same name.

 

[Mable]

I looked and found that yes, I did in fact have the name
listed twice. I shut down word and reloaded it with a
fresh document, copied in the code, set up two listboxes
(one with names, one with numbers) and came to the results
I was looking for. I can now implement those numbers into
my program and hopefully this'll be the last necessary
step in development.

Thanks Jonathon and Helmut for your help.

-Mable