Sub network_print()
'
' network_print Macro
' Macro recorded 4/20/2009 by Carole James
'
Sheets("9603").Select
Application.ActivePrinter = "\\winprint\oki9603 on Ne09:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\winprint\oki9603 on Ne09:", Collate:=True
Sheets("9604").Select
Application.ActivePrinter = "\\winprint\oki9604 on Ne10:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\winprint\oki9604 on Ne10:", Collate:=True
End SubThe Nexx: number is not constant (you could have discovered this yourself with a simple check). I have even discovered that the same printer can get renumbered on my home pc (I think I updated the driver or something like that). (also see registry exercise below)
'- partial code *****
For P = 0 To 6
PrinterNetworkName = PrinterName & " on Ne" & Format(P, "00") & ":"
'- etc. ..................'===================================================================================
'- GET PRINTER NAMES & PORT CONNECTIONS OF HOST MACHINE'S INSTALLED PRINTERS
'- FROM THE REGISTRY
'- *** NOTE THAT Application.ActivePrinter PORT CAN DIFFER WITH THE REGISTRY ****
'- ALSO A PRINTER ON LPT1 NEEDS SEPARATE CHECK
'- Brian Baulsom January 2008
'===================================================================================
Sub GET_PRINTERS()
Dim WshNetwork As Object
Dim PrintersArray As Variant
Dim PrinterName As String ' name only
Dim PrinterPort As Variant
Dim PrinterFullName As String ' name and port
Dim ComputerName As String
Dim UserName As String
'-------------------------------------------------------------------------------
Set WshNetwork = CreateObject("WScript.Network")
Set PrintersArray = WshNetwork.EnumPrinterConnections
'-------------------------------------------------------------------------------
'- some other properties
ComputerName = WshNetwork.ComputerName
UserName = WshNetwork.UserName
'---------------------------------------------------------------------------------
'- loop
For p = 0 To PrintersArray.Count - 1 Step 2
PrinterName = PrintersArray.Item(p + 1)
PrinterPort = "Ne" & Format(p / 2, "00") & ":"
PrinterFullName = PrinterName & " on " & PrinterPort
rsp = MsgBox("Registry : " & PrinterFullName & vbCr & vbCr _
& "ActiveP : " & Application.ActivePrinter & vbCr & "User : " & UserName _
& vbCr & "Computer : " & ComputerName, vbOKCancel, "PRINTERS IN REGISTRY")
If rsp = vbCancel Then End
Next
'---------------------------------------------------------------------------------
End Sub'===============================================================================
'- GET PRINTERS AND PORTS FROM THE REGISTRY
'- Brian Baulsom April 2009
'===============================================================================
Sub GET_PRINTERS()
Dim WSH, RegObject, ValueSet, SubKeyPath
Dim PrinterName, PrinterPort,KeyValue
Const HKEY_CURRENT_USER = &H80000001
'---------------------------------------------------------------------------
Set WSH = CreateObject("WScript.Shell")
Set RegObject = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
SubKeyPath = "Software\Microsoft\Windows NT\CurrentVersion\Devices\"
RegObject.enumvalues HKEY_CURRENT_USER, SubKeyPath, ValueSet
'---------------------------------------------------------------------------
'- GET VALUES FROM THE REGISTRY
For Each PrinterName In ValueSet
KeyValue = "HKEY_CURRENT_USER\" & SubKeyPath & "\" & PrinterName
PrinterPort = Replace(WSH.RegRead(KeyValue), "winspool,", "")
MsgBox (PrinterName & vbCr & PrinterPort)
Next
'---------------------------------------------------------------------------
End SubFor P = 0 To 6
PrinterNetworkName = PrinterName & " on Ne" & Format(P, "00") & ":" Sheets("9602").Select
Application.ActivePrinter = "\\winprint\oki9602 on Ne09:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\winprint\oki9602 on Ne09:", Collate:=TrueDim wb As Workbook
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
For P = 0 To 300
PrinterNetworkName = “oki” & ws.Name & " on Ne" & Format(P, "000") & ":"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\winprint\oki9603 on Ne09:", Collate:=True
Next ws
End Sub'==================================================================================================
'- PRINT WORKSHEETS TO PRINTER NUMBER IN WORKSHEET NAME
'- UNKNOWN PORT Nexx: NUMBER - SO TRY SEVERAL
'- ***** NB. I have not been able to test this fully at home ******
' =================================================================================================
'- Traps error produced by trying to print to an invalid printer and continues trying
'- In processes such as this it is usually best to concatenate strings to a single variable
'- because often the method (eg. 'Printout' here) cannot handle it.
'--------------------------------------------------------------------------------------------------
'- RECORD RESULTS (NEEDS A SHEET CALLED "Results")
'- It may be a good idea to keep positive results in a sheet and use it next time instead of this
'---------------------------------------------------------------------------------------------------
'- Sample code from recorded macro. We do not use this exactly :
'- ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
'- ActivePrinter:= "\\winprint\oki9603 on Ne09:", Collate:=True
'- Brian Baulsom April 2009
'===================================================================================================
Dim ResultsSheet As Worksheet
Dim ToRow As Long
Dim ws As Worksheet
Dim PrinterName As String
Dim PrinterNumber As String
Dim PrinterPort As String
Dim PrinterFullName As String
Dim PortNumber As Integer
'===================================================================================================
'- MAIN ROUTINE
'===================================================================================================
Sub NETWORK_PRINT()
PrinterName = "\\winprint\oki"
Application.Calculation = xlCalculationManual
'- RESULTS SHEET
Set ResultsSheet = Worksheets("Results")
ResultsSheet.Cells.ClearContents
ToRow = 2
'-----------------------------------------------------------------------------------------------
'- LOOP WORKSHEETS
For Each ws In ActiveWorkbook.Worksheets
PrinterNumber = ws.name
If PrinterNumber <> "Results" Then
'------------------------------------------------------------------------------------------
'- LOOP POSSIBLE PORT NUMBERS
For PortNumber = 0 To 5
PrinterPort = "Ne" & Format(PortNumber, "00") & ":"
PrinterFullName = PrinterName & PrinterNumber & " on " & PrinterPort
Application.StatusBar = " Trying printer : " & PrinterFullName
'--------------------------------------------------------------------------------------
'MsgBox (PrinterFullName) ' for testing
'--------------------------------------------------------------------------------------
'- TRY TO PRINT
On Error Resume Next ' ERROR TRAP - code continues
ws.PrintOut ActivePrinter:=PrinterFullName
On Error GoTo 0 ' reset to trap any other errors
'--------------------------------------------------------------------------------------
'- SUCCESS - record result. exit the loop ... or try again
If Err.Number = 0 Then
With ResultsSheet
.Cells(ToRow, 1).Value = PrinterNumber
.Cells(ToRow, 2).Value = PrinterFullName
End With
Exit For
Else
Err.Clear ' clear for next error
End If
'--------------------------------------------------------------------------------------
Next
'- end of PortNumber loop
'-------------------------------------------------------------------------------------------
End If
'- Next worksheet
Next
'-----------------------------------------------------------------------------------------------
'- FINISH
MsgBox ("done")
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
End Sub
'------------ eop ----------------------------------------------------------------------------------
