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]

Glad to see you find the code helpful still.
Easy if the sheet is a worksheet .... just add a line

Worksheets("Memorandum").Printout
ws.Printout    ' existing line

 

[zakasnak]

Glad to see you find the code helpful still.
Easy if the sheet is a worksheet .... just add a line

Worksheets("Memorandum").Printout
ws.Printout    ' existing line

Do I put it here?

'- SUCCESS - record result. exit the loop ... or try again
                If Err.Number = 0 Then
                    [COLOR="Red"]ws.PrintOut[/COLOR]
                    SuccessfulPrintout = True
                    Exit For
                Else
                    Err.Clear   ' clear for next error
                End If

 

[BrianB]

Yes. Before that line of code as indicated. The memo sheet is printed, then the worksheet.

                If Err.Number = 0 Then
                    Worksheets("memo").PrintOut
                    ws.PrintOut

 

[zakasnak]

Thanks so much BrianB, it works perfectly! I'm using a word document & am embedding it into the spreadsheet. Is there a way to call the word doc from inside the code? I appreciate all of your help!

 

[BrianB]

call the word doc from inside the code?

What do you want to do with it ?

You could try recording a macro. Gives a better idea of what you are trying to do.

You seem a bit afraid to experiment.
OK, we make mistakes - but these are good .............

 

[zakasnak]

So this is what I've come up with & it opens the document & acts like it's printing, but after checking with the store, only the spreadsheet has printed.

Also, I have the code close the word document, but I really need it to close the Word program, since each time it opens the document it opens a new instance of Word. Help?

'==================================================================================================
'- 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_WORD_MEMO()
    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
                        [B][COLOR="Blue"]Dim objWord As Object
                        Set objWord = CreateObject("Word.Application")
                        objWord.Visible = True
                        objWord.Documents.Open "C:\Old Crappy Computer\Write-A-Macro\Memo for Spreadsheet Test.doc"
                        objWord.ActiveDocument.PrintOut
                        objWord.Documents.Close[/COLOR][/B]
                    ws.PrintOut    ' existing line
                    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 ----------------------------------------------------------------------------------

... and I'm not afraid to experiment, I just wasn't sure how to start.

Thanks for your help, BrianB.

 

[BrianB]

looks cool. Try adding 2 lines :-

Dim objWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open "C:\Old Crappy Computer\Write-A-Macro\Memo for Spreadsheet Test.doc"
objWord.ActiveDocument.PrintOut
objWord.Documents.Close
'----------------------------------------------
'- extra lines
objword.Quit
set objword = Nothing
'----------------------------------------------

An alternative would be to insert the Word document into an Excel sheet and print that.

I hope you have not hurt your computer's feelings with your folder description. I try very hard to keep mine sweet.

 

[zakasnak]

My computer knows that that's not her name... it was her predecessor who really WAS a crappy computer!

Thanks for all your help. I'll give the code a try tomorrow.

 

[zakasnak]

I found that the memo printed on my default printer here. Any ideas?