Controlling the Printer from Word VBA

Editor's Note: Although this solution has helped many people solve their Word printing issues, I have since discovered an inexpensive software program that will do the same thing, and more...without the hassle of having to write your own programming code. So if you need a solution to your Word printing issues and don't want to deal with writing and debugging your own code...you might want to first check out Tray Selector. It will add a customizable printing toolbar to Word that allows you to handle your complex printing with just a click.

Tray Selector is shareware, so you can download a trial version to verify that it'll do the job for you before you buy. See this link for details and to download your free trial: http://www.trayselector.com

Part 1: Using VBA to Select the Paper Tray
This is the first part of what will, hopefully, be a multi-part article on controlling the printer properties from Word VBA.

Introduction
There's a very strange thing about Word—in all its versions, since it was introduced on Windows. Word is a word-processor, designed for output to the printed page. The versions for Windows have always had a scripting language (WordBasic up to Word 95, VBA since Word 97). However, the scripting language has never been able to control the properties of the printer, such as whether to print in color or monochrome; or whether or not to print on both sides of the paper; or provide information about the printer, such as what paper trays it has; or what sizes of paper it can take.

For some time, Visual Basic has had the Printers collection and the Printer object, which allowed this kind of control over the printer for applications written in Visual Basic. In Office XP, Access 2002 has acquired the same objects, but still nothing in Word.

It is quite possible that they will get around to it for the next version of Office. That's all very well, but it doesn't help us right now!

Current Capabilities of Word VBA
In Word VBA, dealing with paper trays is a horrible mess. The Word object model offers the DefaultTrayID, FirstPageTray and OtherPagesTray properties. The first one is a property of the Options object, and defines the default tray used when printing from Word. The other two are properties of the PageSetup object, and are document-specific.

The VBA Help for Word lists a number of constants which it suggests should be used with the DefaultTrayID, FirstPageTray and OtherPagesTray properties. These are as follows.

Unfortunately, no two printers use quite the same names and numbers for their paper trays, and most of them do not use the numbers defined by the Word constants. Take for instance these two printers, and the paper tray names and numbers they use.

(No, that last item on the Tektronix list isn't a typo, that's what the printer driver actually returns!)

If you use the Word constants when trying to set the paper trays for these printers, in most cases absolutely nothing will happen—the tray won't change. The printer will simply ignore a request to change to a tray number that is not available.

Getting the Available Paper Bin Names and Numbers
So, we need a way of finding out what paper trays are actually available for the printer you want to use, and what their numbers are. Word VBA doesn't give you direct access to this information, but the Windows API does allow you to obtain this information from the printer driver. With careful programming, the Windows API is accessible from VBA.

The following code provides a means of getting the list of the paper bin names and numbers for the current printer. Paste it into a fresh module. Each function returns a Variant containing an array. GetBinNumbers lists the numbers, and GetBinNames lists the equivalent names for the paper bins. The code is commented so you can see what is happening at each step. If you are not familiar with VB programming of the Windows API, then it will not be at all obvious how it all works even with the comments, but I promise you, it does work!

Warning! This code makes use of a Windows API function to gain access to the printer information. Unless you are confident that you know what you are doing, messing about with the Windows API from VB or VBA is dangerous. Making a mistake in ordinary VBA will just crash your macro. Making a mistake with an API call will often bring down the whole of Word, and in a bad case even the whole of Windows, requiring a reboot. If you want to modify this code in any way, make sure you save everything first. Don't say I didn't warn you!

 
Option Explicit
  
Private Const DC_BINS = 6
Private Const DC_BINNAMES = 12
	
Private Declare Function DeviceCapabilities Lib "winspool.drv" _
	Alias "DeviceCapabilitiesA" (ByVal lpDeviceName As String, _
	ByVal lpPort As String, ByVal iIndex As Long, lpOutput As Any, _
	ByVal dev As Long) As Long

Public Function GetBinNumbers() As Variant

	'Code adapted from Microsoft KB article Q194789
	'HOWTO: Determine Available PaperBins with DeviceCapabilities API
	Dim iBins As Long
	Dim iBinArray() As Integer
	Dim sPort As String
	Dim sCurrentPrinter As String

	'Get the printer & port name of the current printer
	sPort = Trim$(Mid$(ActivePrinter, InStrRev(ActivePrinter, " ") + 1))
	sCurrentPrinter = Trim$(Left$(ActivePrinter, _
		InStr(ActivePrinter, " on ")))

	'Find out how many printer bins there are
	iBins = DeviceCapabilities(sCurrentPrinter, sPort, _
		DC_BINS, ByVal vbNullString, 0)

	'Set the array of bin numbers to the right size
	ReDim iBinArray(0 To iBins - 1)

	'Load the array with the bin numbers
	iBins = DeviceCapabilities(sCurrentPrinter, sPort, _
	DC_BINS, iBinArray(0), 0)

	'Return the array to the calling routine
	GetBinNumbers = iBinArray
End Function

Public Function GetBinNames() As Variant
	
	'Code adapted from Microsoft KB article Q194789
	'HOWTO: Determine Available PaperBins with DeviceCapabilities API

	Dim iBins As Long
	Dim ct As Long
	Dim sNamesList As String
	Dim sNextString As String
	Dim sPort As String
	Dim sCurrentPrinter As String
	Dim vBins As Variant

	'Get the printer & port name of the current printer
	sPort = Trim$(Mid$(ActivePrinter, InStrRev(ActivePrinter, " ") + 1))
	sCurrentPrinter = Trim$(Left$(ActivePrinter, _
		InStr(ActivePrinter, " on ")))

	'Find out how many printer bins there are
	iBins = DeviceCapabilities(sCurrentPrinter, sPort, _
		DC_BINS, ByVal vbNullString, 0)

	'Set the string to the right size to hold all the bin names
	'24 chars per name
	sNamesList = String(24 * iBins, 0)

	'Load the string with the bin names
	iBins = DeviceCapabilities(sCurrentPrinter, sPort, _
		DC_BINNAMES, ByVal sNamesList, 0)

	'Set the array of bin names to the right size
	ReDim vBins(0 To iBins - 1)
	For ct = 0 To iBins - 1
		'Get each bin name in turn and assign to the next item in the array
		sNextString = Mid(sNamesList, 24 * ct + 1, 24)
		vBins(ct) = Left(sNextString, InStr(1, sNextString, Chr(0)) - 1)
	Next ct

	'Return the array to the calling routine
	GetBinNames = vBins
End Function

Using the Code
Fortunately, you don't need to know all the details of how that code works in order to be able to use it! It has been designed so that minimal additional code is needed when you want to manipulate the paper bins.

If you want to give the user of a VBA macro the choice of which paper bin to use, then it is necessary to display the list of bins. This is quite straightforward. Create a UserForm, and include a ListBox on it (call it ListBox1). To put the list of bin names into the ListBox, just use the following code in the UserForm_Initialize event, so that the ListBox is filled with the list of paper trays when the UserForm is first displayed.

	ListBox1.List = GetBinNames

Later, if the user has selected a bin, and you now want to assign the selection to the current document, the following code could be used.

Dim vBinNumbers as Variant
If ListBox1.ListIndex >= 0 Then
	vBinNumbers = GetBinNumbers
	ActiveDocument.PageSetup.OtherPagesTray = _
		vBinNumbers(ListBox1.ListIndex)
Else
	MsgBox "No paper tray has been selected."
End If

That's all there is to it!

Don't miss Jonathan's next article in this series: Part 2: Using VBA to control Duplex, Color Mode and Print Quality