Printer setup for printing various documents


  • Hello! 

    I have an automation script running whose output is various document types and printing those documents with various printer settings. I am printing to the same printer for all documents, so that's nice. I have three excel worksheets that need to be printed. These I am printing using the built-in "PrintOutEx" method. One onto a letter size paper and two to 11x17. Next, I have the script print a word document, which I am printing using the built-in "PrintOut" method. This is to be printed on an 11x17 paper but scaled so that two pages print side by side. Lastly, I have to print a multi-page tiff document which I am printing using a print handler. I am having a number of issues with this and I apologize if these are silly question and appreciate any help I can gather. 

    1. I am trying to keep the default printer the same as when I start the subroutines and that doesn't seem to be working using the PrinterSettings methods.
    2. About the default printer: the default printer changes to the printer I want (doesn't change back at the end). The default printer is set to 11x17. If I change the default settings of the printer to 8.5x11 everything prints to 8.5x11.
    3. The script seems to be ignoring the settings/pagesettings/setups that I am providing it with. 
    4. The word document print is not scaled to fit the paper and I have tried setting the paper size to 11x17 as well as 8.5x11 and scaling the print but that just looks really bad.
    5. The first excel worksheet does not print to a letter size paper.
    6. The second and third worksheets work okay (as long as the default printer settings are as desired; see point 2).
    7. The multipage tiff document prints okay as long as the default printer's default settings are correct.

    I am not a professional programmer so I apologize if the question has a simple answer or the code looks horrifying. I researched tons on this question and couldn't find a solution that worked. :( I am attaching the code I have thus far.


        Public Sub PrintSPIDPackage(SPID As String)
            On Error GoTo errHandler
            Dim printerSettings As New Printing.PrinterSettings
            Dim curPrinter As String
            Dim engPrinter As String
            Dim purPrinter As String
            engPrinter = "engBLAH"
            purPrinter = "purBLAH"
            curPrinter = printerSettings.PrinterName
            Dim spidBBPath As String
            spidBBPath = ****REDACTED
            spidDrawingPackagePath = ***REDACTED*** 
            Dim eAPP As New Excel.Application
            Dim wB As Excel.Workbook
            Dim wS As Excel.Worksheet
            Dim rng As Excel.Range
            Dim lRow As Integer
            Dim wAPP As New Word.Application
            Dim doc As Word.Document
            wB = eAPP.Workbooks.Open(spidBBPath)
            wS = wB.Worksheets(selectedSheet & " OPs")
            wS.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperLetter 'I would think this causes the printer to printout to a letter size paper
            wS = wB.Worksheets(selectedSheet)
            lRow = wS.Range("A" & wS.Rows.Count).End(Excel.XlDirection.xlUp).Row + 3
            rng = wS.Range("A1:I" & lRow)
            rng.PrintOutEx(ActivePrinter:=engPrinter) 'PRINT SHOULD BE AN 11x17
            wS = wB.Worksheets(selectedSheet & " MLB")
            wS.PrintOutEx(ActivePrinter:=engPrinter) 'PRINT SHOULD BE AN 11x17
            If InStr(xPath, ".doc") <> 0 Then
                doc = wAPP.Documents.Open(xPath)
                wAPP.ActivePrinter = engPrinter
                doc.PageSetup.PaperSize = Word.WdPaperSize.wdPaperLetter ''''''print out should be 11x17 with two pages side by side
                wAPP.PrintOut(PrintZoomColumn:=2, PrintZoomRow:=1) ', PrintZoomPaperHeight:=2 * (11 * 1440), PrintZoomPaperWidth:=2 * (8.5 * 1440)) '''''AS you see I tried scaling...
                doc = Nothing
                wAPP = Nothing
            End If
            Dim pDoc As New Printing.PrintDocument
            currPage = 0
            AddHandler pDoc.PrintPage, AddressOf pDocPage
            PrintDialog1.Document = pDoc
            pDoc.DefaultPageSettings.Landscape = True
            pDoc.DocumentName = SPID & "-Drawing Package"
            pDoc.PrinterSettings.PrinterName = engPrinter
            pDoc.DefaultPageSettings.Landscape = True
            pDoc.DefaultPageSettings.Margins.Left = 50
            pDoc.DefaultPageSettings.Margins.Right = 50
            pDoc.DefaultPageSettings.Margins.Top = 50
            pDoc.DefaultPageSettings.Margins.Bottom = 50
            Dim ps As New Printing.PaperSize("Tabloid", 1700, 1100)
            ps.PaperName = Printing.PaperKind.Tabloid
            pDoc.DefaultPageSettings.PaperSize = ps
            printerSettings.PrinterName = curPrinter 'this should reset the user's default printer to the original setting to before when this routine started??
            Exit Sub
        End Sub
        Sub pDocPage(ByVal sender As Object, ByVal e As Printing.PrintPageEventArgs)
            Dim img As Image = Image.FromFile(spidDrawingPackagePath)
            Dim pCount = img.GetFrameCount(FrameDimension.Page)
            img.SelectActiveFrame(FrameDimension.Page, currPage)
            Using stReader As IO.MemoryStream = New IO.MemoryStream
                img.Save(stReader, ImageFormat.Bmp)
                Dim bmp As Bitmap = CType(Image.FromStream(stReader), Bitmap)
                e.Graphics.DrawImage(bmp, 0, 0)
            End Using
            currPage += 1
            If currPage < pCount Then
                e.HasMorePages = True
            End If
        End Sub

    Monday, April 10, 2017 4:58 PM

All replies

  • Your setup seems to be working more or less. I cant check your docs from excel etc but it prints an image. I am not sure about all the details you see.

    Setting the papersize seems to override the landscape setting. So you need to set the paper size as landscape. Then don't need landscape.

             Dim ps As New Printing.PaperSize("Tabloid", 1100, 1700)

    As far as the image print quality, you are scaling a bitmap so there is a loss of resolution if the image is resized. When the image is not resized you get the best quality but then the image does not necessarily fit on the paper. If you want to fit the borders then you need to resize the image.

    Do you have an option to output a windows metafile from excel instead of a bitmap? That would be much better quality and better for resizing.

    Tuesday, April 11, 2017 3:32 AM
  • So I omitted the landscape line of code and kept the line you recommended (...New Printing.Paper...) and the printer only prints with the default printer preferences which are set up "Devices and Printers" menu. E.G. currently I have the default preference as 8.5"x11" and regardless setting my papersize to tabloid the printed document comes out 8.5x11. 
    Monday, April 17, 2017 7:42 PM