VBA - Active Printer Port Problem

[technotrope]

I'm trying to use vba to print a worksheet. Here's the bit of code that is
causing me fits:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Local Printer on NE03:", Collate:=True

And, the problem is...The "NE03:" keeps changing when I reboot. Sometimes
the printer is on NE03:. The next time it's on NE04:". So, I borrowed some
code from a site ("TechTrax") that reports a bunch of info about the
printers. Then, I tried to build the ActivePrinter name by combining the
string "Local Printer on " with a string of the port name. However, the
port name gets reported as "USB001" instead of "NE03."

In my list of printers, the printer's name is simply "Local Printer". Any
ideas how to do this?

TIA for any help and suggestions.

 

[jaf]

Hi,
You can use the LIKE operator. Even though VBA help says activeprinter is
read/write its read only.

You can only change the active printer by calling the print dialog and
clicking on a printer.


Sub test()

If Application.ActivePrinter Like "Adobe*" Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
ActivePrinter:=Application.ActivePrinter Like "Adobe*", Collate:=True
Else
'Choose printer
Application.Dialogs(xlDialogPrinterSetup).Show
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
ActivePrinter:=Application.ActivePrinter, Collate:=True
End If

Debug.Print Application.ActivePrinter
End Sub

'Debug.Print=Adobe PDF on Ne03:


John

 

[Thomas Dettenberg]

You can check the NE-ID (value 1 - 99)
using the following code:
your printer is for example: "HP-LJ-6"

sub ne_search ()
dim ne as sting, printer$, i%

printer = "HP-LJ-6"
on error resume next
for i = 1 to 99
ne = vba.format(i,"00")
err.number = 0
application.activeprinter = printer & ne & ":"

if err.number = 0 then
exit for
end if
next

end sub

 

[Dick Kusleika]

You can check the NE-ID (value 1 - 99)
using the following code:
your printer is for example: "HP-LJ-6"

sub ne_search ()
dim ne as sting, printer$, i%

printer = "HP-LJ-6"
on error resume next
for i = 1 to 99
ne = vba.format(i,"00")
err.number = 0
application.activeprinter = printer & ne & ":"

if err.number = 0 then
exit for
end if
next

end sub

Here are a couple more options (though not necessarily better than yours)

http://www.dailydoseofexcel.com/archives/2008/04/05/getting-the-printer-port/