use print dialog to print worksheet RRS feed

  • Question

  • The code below prints a pdf from a print dialog on my menu strip on a winform.  What I wanted to do is to print worksheet posProfile directly, but I could not figure out how to do that.  Instead, I exported the sheet as a pdf and then printed that file.  Is there a way to get the sheet directly instead of my workaround?

        Private Sub prnCustonPages_Click(sender As Object, e As EventArgs) Handles prnCustonPages.Click
            'This procedure runs when when the Print selection on the
            'menu strip is clicked. The procedure exports the sheet to pdf
            'and then prints the sheet.
            Dim xlWBPath As String = Globals.ThisWorkbook.Application.ActiveWorkbook.Path
            Dim xlSheet As Excel.Worksheet = CType(CType(xlWB.Sheets("posProfile"), Excel.Worksheet), Excel.Worksheet)
            Dim docName As String = xlWBPath & "\" & "Custom Ranges" & ".pdf"
            'Export file to 'pdf
            xlSheet.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, docName, _
            Excel.XlFixedFormatQuality.xlQualityStandard, False, True, , , False)
            'Get document name and set as file to print
            docPrint.DocumentName = docName
            'Print File
            prnPrtCustomRanges.Document = docPrint
            prnPrtCustomRanges.PrinterSettings = docPrint.PrinterSettings
            prnPrtCustomRanges.AllowSomePages = True
            If prnPrtCustomRanges.ShowDialog = DialogResult.OK Then
                docPrint.PrinterSettings = prnPrtCustomRanges.PrinterSettings
            End If
    Sunday, August 11, 2013 12:11 AM


  • The only way to print a native Excel worksheet is through the Excel application. You could, theoretically, use the Excel object model to do the printing, as long as you've opened the workbook in the Excel application, to begin with. This would seem to make sense, as you're working anyway with a VSTO add-in?

    If you want to use the printing as you currently have it, then the Excel workbook/-sheet must be exported to a file format that can be handled by the functionality you're using, PDF, for example.

    The only alternative suggestion that occurs to me would be to use something other than the Excel object model to convert the workbook/-sheet to a different file format. That is theoretically possible, by leveraging the Excel Open XML file format. That would allow you to "transform" the native Excel XML to something else (doing the job the Excel application is doing for you).

    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by JMazorra Tuesday, August 13, 2013 12:59 AM
    Monday, August 12, 2013 12:57 PM