Results 1 to 4 of 4

Thread: Worksheet Page to PDF

  1. #1

    Worksheet Page to PDF



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    The code found here (http://www.excelguru.ca/node/21) for producing a PDF works fine for me, but is there a way to make it look at the one worksheet and create various different PDF's for each page of the sheet. I have made the pages match up to where the data is, so when using the code from the site it puts each thing on a new page so I get a 27 page document, but i want 27 x 1 page documents?

    Is it possible?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Hi Adam,

    Welcome to the forum. I'll have a play with this tonight and see what I can do.

    Just to confirm, you have all your data on one worksheet. So we're looking at printing the data between pagebreaks to separate files, correct?

    How would you like them named? Filename-page1.xls?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Your correct, the filenames aren't really an issue as I can change them to suit at a later date. Thanks!

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Hi Adam,

    Give this a try:
    Code:
    Sub PrintToPDF_SingleSheetToMultiPages_Early()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Print to each page of a specific sheet to a new PDF file using PDFCreator
    '   (Download from http://sourceforge.net/projects/pdfcreator/)
    '   Designed for early bind, set reference to PDFCreator
        Dim pdfjob As PDFCreator.clsPDFCreator
        Dim sPDFName As String
        Dim sPDFPath As String
        Dim lPage As Long
        Dim bRestart As Boolean
        'Activate error handling and turn off screen updates
        On Error GoTo EarlyExit
        Application.ScreenUpdating = False
        Set pdfjob = New PDFCreator.clsPDFCreator
        sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
        'Check if PDFCreator is already running and attempt to kill the process if so
        Do
            bRestart = False
            Set pdfjob = New PDFCreator.clsPDFCreator
            If pdfjob.cStart("/NoProcessingAtStartup") = False Then
                'PDF Creator is already running.  Kill the existing process
                Shell "taskkill /f /im PDFCreator.exe", vbHide
                DoEvents
                Set pdfjob = Nothing
                bRestart = True
            End If
        Loop Until bRestart = False
        If Not IsEmpty(ActiveSheet.UsedRange) Then
            For lPage = 1 To ActiveSheet.PageSetup.Pages.Count
            'Check if worksheet is empty and skip if so
                With pdfjob
                    '/// Change the output file name here! ///
                    sPDFName = "testPDF - Page " & lPage & ".pdf"
                    .cOption("UseAutosave") = 1
                    .cOption("UseAutosaveDirectory") = 1
                    .cOption("AutosaveDirectory") = sPDFPath
                    .cOption("AutosaveFilename") = sPDFName
                    .cOption("AutosaveFormat") = 0    ' 0 = PDF
                    .cClearCache
                End With
        
                'Delete the PDF if it already exists
                If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)
        
                'Print the document to PDF
                ActiveSheet.PrintOut Copies:=1, ActivePrinter:="PDFCreator", From:=lPage, To:=lPage
        
                'Wait until the print job has entered the print queue
                Do Until pdfjob.cCountOfPrintjobs = 1
                    DoEvents
                Loop
                pdfjob.cPrinterStop = False
        
                'Wait until the file shows up before moving on
                'Important:  Counter must reach zero or hangs on next iteration
                Do Until pdfjob.cCountOfPrintjobs = 0
                    DoEvents
                Loop
            Next lPage
        End If
        
    Cleanup:
        'Release objects and terminate PDFCreator
        Set pdfjob = Nothing
        Shell "taskkill /f /im PDFCreator.exe", vbHide
        On Error GoTo 0
        Application.ScreenUpdating = True
        Exit Sub
    EarlyExit:
        'Inform user of error, and go to cleanup section
        MsgBox "There was an error encountered.  PDFCreator has" & vbCrLf & _
               "has been terminated.  Please try again.", _
               vbCritical + vbOKOnly, "Error"
        Resume Cleanup
    End Sub
    I've attached a file with the code that I used to test it. Just be warned that you will probably need to re-set the reference to PDFCreator, as I'm running on a 64bit OS, so it may be pointed to a different directory than on your system.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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