Excel VBA printing and NE numbers

Anyone who has dealt with printing in VBA for Excel knows the plight of the NE00-NE99 ports/numbers that need to be appended to the printer name when passing it into the Application.ActivePrinter.

There are many ways to do this. I have found little bits and pieces here and there on the web and have come up with numerous ways of doing it myself. So I hope to guide you through the easier ways to do this as there seems to be little on the web about working around this problem.

The key:

HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts

holds the values of the printers and NE port numbers. You could use these registry entries to figure out the NE port number for your printer. It requires some knowledge of using vba to access the registry for reading and using string functions to make a match.

I have also seem some people loop through all the NE numbers....from 00 to 99 to print out. It is possible but I think bad programming and I just don't like it.

The easiest way I've found is using the windows API via VBA.

How it works....

1. You need to declare the API function as below,

Private Declare Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA"(ByVal pszPrinter As String) As Long

Note: Don't try to be smart and change the function name or alias or whatever just copy the lines...seriously it won't work if you mess with it.

2. Call the Function: SetDefaultPrinter strPrinter where strPrinter is the name of the print queue..if local the PrinterName and if located on a print server then \\server\queue_name

3. Print Something like the selected sheets or whatever floats your boat.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

So now you are asking well but now my default printer changed even though it printer correctly...I can't live without my default printer...So here is what I implemented for my specific situation and should get you moving in the right direction. This was a module in Excel and I called it from Workbook_Open by using:

Call PrintModule.Print_Selected_Sheets("C:\Printer.txt")

where PrintModule is the name of my Module if you are creating a new module and copying/pasting the code your module will be Module1 most likely if it is a new workbook.

Also the way this code works is it prints the Selected Sheets so I added the following line prior to calling my PrintModule function.

Sheets(Array("Sheet1","Sheet2")).Select

You could obviously edit Sheet1 and Sheet2 with the sheets you want to print out. You could specify only Sheet1 or add more Sheets to print out....

Option Explicit Private Declare Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long Private Declare Function GetDefaultPrinter Lib "winspool.drv" Alias "GetDefaultPrinterA" (ByVal sPrinterName As String, lPrinterNameBufferSize As Long) As Long Sub Print_Selected_Sheets(strPrinterFileName As String) Dim DefPrinter As String 'Variable to hold original default printer Dim sLen As Long 'Variable that will hold the length of printer string Dim hResult As Long 'Holds result of GetDefaultPrinter Dim FileNum As Integer 'Used to Open a FreeFile for reading Dim strPrinter As String DefPrinter = Space$(255) sLen = 255 hResult = GetDefaultPrinter(ByVal DefPrinter, sLen) If hResult <> 0 Then DefPrinter = Left(DefPrinter, sLen - 1) FileNum = FreeFileOpen strPrinterFileName For Input As #FileNum While Not EOF(FileNum) Line Input #FileNum, strPrinter SetDefaultPrinter strPrinter ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Wend SetDefaultPrinter DefPrinter End Sub

Please post comments and I will do my best to reply. If you think there is a much better way then please post. I have seen lots of dead end posts with no real answer in my searches so hopefully this one will help out at least a few people. Also I apologize for the formatting as this is my first attempt at using Blogger.