Results 1 to 5 of 5

Thread: VBA PDF Export printing 1 page
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA PDF Export printing 1 page

    I have used this VBA code for publishing a PDF report which does work, but what it is doing is showing 2 blank pages below the PDF report been published.

    Is there anyway to say to the report to export just the range I want leaving just 1 page instead of throwing out good paper!.

    Private Sub PrintPDFTrainingReport_Click()
    'turn off screen updating
    Dim Opendialog
    Dim MyRange As Range
    Application.ScreenUpdating = False
    'open dialog and set file type
    Opendialog = Application.GetSaveAsFilename("", filefilter:="PDF Files (*.pdf), *.pdf", _
    Title:="Training Due Report")
    'if no value is added for file name
    If Opendialog = False Then
    MsgBox "The operation was not successful"
    Exit Sub
    End If
    'set the named range for the PDF print area
    Sheet2.Select
    With Sheet2
    .Range("T1:AC1" & Cells(Rows.Count, "T").End(xlUp).Row).Name = "PDFRng"
    End With
    'set range
    Set MyRange = Sheet2.Range("PDFRng")
    Sheet2.PageSetup.Orientation = xlLandscape
    'Sheet2.PageSetup.FitToPagesWide = 1
    Sheet2.PageSetup.Zoom = 75
    Sheet2.PageSetup.PrintArea = "PDFRng"
    'create the PDF
    On Error Resume Next
    MyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Opendialog, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    'error handler
    On Error GoTo 0
    'clear the page breaks
    ActiveSheet.DisplayPageBreaks = False
    Application.ScreenUpdating = False
    Sheet1.Select
    End Sub

    Thanks

    Steve


  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA PDF Export printing 1 page

    Code:
    Sheet2.PageSetup.FitToPagesTall = 1
    I suspect that your real or additional problem is:
    Code:
    .Range("T1:AC1" & Cells(Rows.Count, "T").End(xlUp).Row).Name = "PDFRng"
    Last edited by Kenneth Hobson; Oct 22nd, 2019 at 12:49 AM.

  3. #3
    New Member
    Join Date
    Oct 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA PDF Export printing 1 page

    Arrr Yes Ken,

    You are right was meant to do a Current_Region in my data and Delete PDFrng then Put "Current_Region". That's why it was taking the blank screen to the bottom!

    Thanks for the help

    Steve

  4. #4
    New Member
    Join Date
    Oct 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA PDF Export printing 1 page

    Well There goes that idea Ken,

    I input the following line and it still creates 3 pages. Below is the code and I left the comas in to make it a text.

    Sheet2.Select
    Sheet2.PageSetup.FitToPagesTall = 1
    With Sheet2
    '.Range("T1:AC1" & Cells(Rows.Count,"T").End(xlUp).Row).Name = "Current_Region"
    'End With
    'set range
    'Set MyRange = Sheet2.Range("Current_Region")
    'Sheet2.PageSetup.Orientation = xlLandscape
    'Sheet2.PageSetup.FitToPagesWide = 1
    'Sheet2.PageSetup.Zoom = 75
    'Sheet2.PageSetup.PrintArea = "Current_Region"
    'create the PDF
    On Error Resume Next
    MyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Opendialog,Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    'error handler



    Alls I want is just to print a current region area and export it too PDF

    Let me know if I missed some thing

    Steve

  5. #5
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,079
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA PDF Export printing 1 page

    The normal method is to set the print area and then publish the worksheet to pdf but you can use a range to override the print area.

    You commented out the width setting.

    You can set the print area first and then select View and Page Break Preview to see what is going on. Change one setting and then record a macro and set it back so that Page Break Preview shows the one page if you need code.

    Select, Activate, and Selection are seldom needed.
    Code:
    Sub Main()
      Dim MyRange As Range
      With Sheet2
        Set MyRange = .Range("T1:AC" & .Cells(Rows.Count, "T").End(xlUp).Row)
        .PageSetup.Orientation = xlLandscape
        .PageSetup.FitToPagesWide = 1
        .PageSetup.Zoom = 75
        '.PageSetup.PrintArea = "Current_Region"
        'create the PDF
        MyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Opendialog, Quality:=xlQualityStandard, _
         IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
      End With
    End Sub
    Last edited by Kenneth Hobson; Oct 23rd, 2019 at 12:36 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •