Add MEMO sheet to beginning of network print job.

[zakasnak]

I am looking for help to modify this code that prints each sheet to a different printer on the network. I would like for the code to print a sheet that will be named "MEMO" at the beginning of each print job.

BrianB was extremely helpful setting up the Network Print code.

Thanks for any anyone can give me.

'==================================================================================================
'- VERSION 4 : PRINT WORKSHEETS TO PRINTER NUMBER IN WORKSHEET NAME
'- Changed method of recording results.
'- 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 set 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")
'---------------------------------------------------------------------------------------------------
'- 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 OriginalPrinter As String
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
Dim SuccessfulPrintout As Boolean
'===================================================================================================
'- MAIN ROUTINE
'===================================================================================================
Sub NETWORK_PRINT()
    OriginalPrinter = Application.ActivePrinter
    PrinterName = "\\winprint\oki"
   Application.Calculation = xlCalculationManual
    On Error Resume Next            ' ERROR TRAP - code continues
    '-----------------------------------------------------------------------------------------------
    '- RESULTS SHEET
    Sheets.Add
    ActiveSheet.name = "Results"
    Set ResultsSheet = Worksheets("Results")
    ResultsSheet.Cells.ClearContents
    ToRow = 2
    ResultsSheet.Cells(ToRow, 1).Value = "No printers found"
    '-----------------------------------------------------------------------------------------------
    '- LOOP WORKSHEETS
    For Each ws In ActiveWorkbook.Worksheets
        PrinterNumber = ws.name
        SuccessfulPrintout = False
        If UCase(PrinterNumber) <> "RESULTS" Then
            '---------------------------------------------------------------------------------------
            '- LOOP POSSIBLE PORT NUMBERS
            For PortNumber = 0 To 300
                PrinterPort = "Ne" & Format(PortNumber, "00") & ":"
                PrinterFullName = PrinterName & PrinterNumber & " on " & PrinterPort
                Application.StatusBar = "  Trying printer : " & PrinterFullName
                '-----------------------------------------------------------------------------------
                'MsgBox (PrinterFullName)  ' for testing
                '-----------------------------------------------------------------------------------
                '- TRY TO SET ACTIVEPRINTER
                Application.ActivePrinter = PrinterFullName
                '-----------------------------------------------------------------------------------
                '- SUCCESS - record result. exit the loop ... or try again
                If Err.Number = 0 Then
                    ws.PrintOut
                    SuccessfulPrintout = True
                    Exit For
                Else
                    Err.Clear   ' clear for next error
                End If
                '-----------------------------------------------------------------------------------
            Next
             '- end of PortNumber loop
            '---------------------------------------------------------------------------------------
            '- RECORD RESULTS
            '---------------------------------------------------------------------------------------
             With ResultsSheet
                 .Cells(ToRow, 1).Value = PrinterNumber
                 If SuccessfulPrintout = True Then
                     .Cells(ToRow, 2).Value = PrinterFullName
                     .Cells(ToRow, 3).Value = "SUCCESS"
                 Else
                     .Cells(ToRow, 3).Value = "**FAILURE**"
                 End If
                 ToRow = ToRow + 1
             End With
            '---------------------------------------------------------------------------------------
       End If
        '- Next worksheet
    Next
    '-----------------------------------------------------------------------------------------------
    '- FINISH
    MsgBox ("Print Job Complete")
    Application.ActivePrinter = OriginalPrinter
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
End Sub
'------------ eop ----------------------------------------------------------------------------------

 

[BrianB]

You are opening Word as a separate application. Looks like Excel active printer has no effect.

Looks like you need to insert document into Excel as suggested.

 

[zakasnak]

You are opening Word as a separate application. Looks like Excel active printer has no effect.

Looks like you need to insert document into Excel as suggested.

Then I will embed the doc & print the sheet named "Memo". Thank you so much for your help on this project, BrianB. You've been a great help!

Have a great week!