Printing issues (VBA)

[William Maka]

I'm having problem printing to a specific printer (TorIT)
from different locations on our network. Specifically
Ne## (i.e. Ne06) has to be changed for different desktop
location.

How can I change the syntax for all the users to be able
to print on that printer (TorIT) without having to alter
the code for each of them?

VB code below:
Application.ActivePrinter = "\\servername\TorIT on Ne06:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
ActivePrinter:= _
"\\servername\TorIT on Ne06:", Collate:=True
..

 

[Dave Peterson]

You could cycle through all their connected printers (they do have it installed
on their pcs???):

Option Explicit
Sub testme01()

Dim WSH As IWshRuntimeLibrary.WshNetwork
Dim myPrinters As Variant
Dim iCtr As Long

Set WSH = New IWshRuntimeLibrary.WshNetwork

Set myPrinters = WSH.EnumPrinterConnections
For iCtr = 0 To myPrinters.Length - 1 Step 2
'MsgBox myPrinters.Item(iCtr) & "--" & myPrinters.Item(iCtr + 1)
If LCase(myPrinters.Item(iCtr + 1)) Like "\\servername\TorIT*" Then
MsgBox "found it"
Exit For
End If
Next iCtr

End Sub

This first version requires a reference (tools|references in the VBE) to Windows
Script Host Model.

This second version uses late binding to avoid any version mismatches. (The
version with the references is much nicer to develop in. After you're done,
convert it to late binding and you won't have to worry about getting calls that
say references are missing.)

Sub testme01b()

'Dim WSH As IWshRuntimeLibrary.WshNetwork
Dim WSH As Object
Dim myPrinters As Variant
Dim iCtr As Long

'Set WSH = New IWshRuntimeLibrary.WshNetwork
Set WSH = CreateObject("wscript.network")

Set myPrinters = WSH.EnumPrinterConnections
For iCtr = 0 To myPrinters.Length - 1 Step 2
'MsgBox myPrinters.Item(iCtr) & "--" & myPrinters.Item(iCtr + 1)
If LCase(myPrinters.Item(iCtr + 1)) Like "\\servername\TorIT*" Then
MsgBox "found it"
Exit For
End If
Next iCtr

End Sub

Once you find the printer, you can run your activeprinter code.