Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Excel VBA to Save As a PDF

    This macro works for saving & printing the current sheet as a PDF. How can I modify it to save & print a selection?

    Sub Save_to_PDF2()

    ChDir "G:\Payroll_Report\OT&TimeApprovalReport"


    Dim strXLName As String, strPDFName As String, intPos As Integer

    strXLName = ActiveWorkbook.FullName
    intPos = InStr(1, strXLName, ".")
    strPDFName = Left(strXLName, intPos) & "pdf"

    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strPDFName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

    End Sub


    I would like to add a group of sheets in the workbook like this:
    Sheets(Array("Sum Regular Hours", "Sum Regular Dollars", "Sum OT Hours", "Sum OT Dollars")).Select

    I tries a possible insertion point at ActiveSheet.ExportAsFixedFormat _ but I really don't have clue.
    Last edited by Arcturus16a; 2014-07-09 at 21:14.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Arcturus16a,

    Change this: ActiveSheet.ExportAsFixedFormat _
    to this: Selection.ExportAsFixedFormat _

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    No. That didn't help. The PDF ignores the preselected print ranges and appears to use only A1:A5.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Arcturus16a,

    Maybe I misunderstood your question. Are you trying to save as PDF a selection on a sheet or a selection of whole sheets, e.g. Sheet1 & Sheet3 & Sheet7?

    I assume it must be the latter as I tested for the former and it worked properly for me.
    Last edited by RetiredGeek; 2014-07-10 at 12:00.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    A selection of sheets. Originally, I posted

    Sheets(Array("Sum Regular Hours", "Sum Regular Dollars", "Sum OT Hours", "Sum OT Dollars")).Select

    but have modified the sheet names to exclude the spaces since I know that sometimes causes a problem
    Sheets(Array("Regular_Hours", "Regular_Dollars", "OT_Hours", "OT_Dollars", _
    "Regular_Hrs_by_Job", "OT_Hrs_by_Job")).Select

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Here's tested code to SaveAsPDF the currently selected Sheets in a Workbook.
    Code:
    Sub SelectedSheetsToPDF()
    
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="G:\BEKDocs\Excel\Finances\Cars.pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=False, _
            IgnorePrintAreas:=True, _
            OpenAfterPublish:=True
            
    End Sub
    Of course you'll replace the Filename:= value.
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    That's It! Perfect. Thanks Mr. Geek.

    I modified the Filename so that this code can be inserted into any workbook. All a user needs to do (I think) is to change the Sheets Array at the beginning of the macro.

    Sub SelectedSheetsToPDF()

    Sheets(Array("Regular_Hours", "Regular_Dollars", "OT_Hours", "OT_Dollars", _
    "Regular_Hrs_by_Job", "OT_Hrs_by_Job")).Select

    Dim strXLName As String, strPDFName As String, intPos As Integer


    strXLName = ActiveWorkbook.FullName
    intPos = InStr(1, strXLName, ".")
    strPDFName = Left(strXLName, intPos) & "pdf"


    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=strPDFName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=True, _
    OpenAfterPublish:=True


    End Sub

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Another option is to do the sheet selection in another macro that calls the one I posted. That will also allow the user to select the sheets manually then run the posted macro to print them. YMMV HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Arcturus16a (2014-09-19)

Posting Permissions

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