locked
Loop to append pdf RRS feed

  • Question

  • I have an excel application for a golf course that creates a scorecard for their leagues.  I have a skeleton for the scorecard and I loop through all the players in the league to fill in the scorecard and then create a single PDF file that can be taken to the local printer.   Problem is I end up with as many as 24 individual PDF files that need to be individually printed.

    Right now I'm using the ExportAsFixedFormat to create a single 2-page PDF scorecard (front and back - Code below) Is there any way I can keep this file open and append the additional scorecards to the PDF to create a single PDF file with all the scorecards in it?  

                'Print the scoresheet to PDF
                subsheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=sPDFPath & sPDFName, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False

     

    Is there another function I should be using?  Do I need a 3rd party add-in like pdfCreator?

    Running Office 2010 with Windows 7.

     

    Monday, April 25, 2011 12:47 PM

Answers

  • You could print out all the scorecards with one print statement.  Here is an example of how to append all the print areas together.

     

        ws_num = 0
       
        For Each ws In ActiveWorkbook.Worksheets
           ws_num = ws_num + 1
           ReDim Preserve Print_Array(1 To ws_num)
           Print_Array(ws_num) = ws.Name
               
           LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
           ws.PageSetup.PrintArea = "$A$1:$F$" & LastRow

        Next
           

       'Application.ActivePrinter = "PDFCreator on ABC:"
       'Application.ActivePrinter = "PDFCreator"
       ActiveWorkbook.Worksheets(Print_Array).PrintOut


    jdweng
    • Marked as answer by fedude Wednesday, May 4, 2011 1:31 PM
    Monday, April 25, 2011 1:41 PM
  • Yes like that
     
    Maybe this page will help
     
    Or if the info is already on sheets use
     
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm


    "fedude" wrote in message news:87117740-33c4-4d4a-acc6-acf767871af1...

    Ron,

    Not sure what you mean?   Do you mean:

    Create a new "Print" worksheet

    1. Load the scorecard with the player information
    2. Copy the 2 pages to a "Print" sheet
    3. Continue doing this and concatenating the new data below the last one
    4. save the "Print" sheet as PDF
    • Marked as answer by fedude Wednesday, May 4, 2011 1:31 PM
    Monday, April 25, 2011 4:24 PM

All replies

  • Why not merge all data first in one sheet and then create the PDF
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm


    "fedude" wrote in message news:5b5dff10-d3ef-44c0-84a0-dadb391f9c59...

    I have an excel application for a golf course that creates a scorecard for their leagues.  I have a skeleton for the scorecard and I loop through all the players in the league to fill in the scorecard and then create a single PDF file that can be taken to the local printer.   Problem is I end up with as many as 24 individual PDF files that need to be individually printed.

    Right now I'm using the ExportAsFixedFormat to create a single 2-page PDF scorecard (front and back - Code below) Is there any way I can keep this file open and append the additional scorecards to the PDF to create a single PDF file with all the scorecards in it? 

                'Print the scoresheet to PDF
                subsheet.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=sPDFPath & sPDFName, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False

     

    Is there another function I should be using?  Do I need a 3rd party add-in like pdfCreator?

    Running Office 2010 with Windows 7.

     

    Monday, April 25, 2011 1:39 PM
  • You could print out all the scorecards with one print statement.  Here is an example of how to append all the print areas together.

     

        ws_num = 0
       
        For Each ws In ActiveWorkbook.Worksheets
           ws_num = ws_num + 1
           ReDim Preserve Print_Array(1 To ws_num)
           Print_Array(ws_num) = ws.Name
               
           LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
           ws.PageSetup.PrintArea = "$A$1:$F$" & LastRow

        Next
           

       'Application.ActivePrinter = "PDFCreator on ABC:"
       'Application.ActivePrinter = "PDFCreator"
       ActiveWorkbook.Worksheets(Print_Array).PrintOut


    jdweng
    • Marked as answer by fedude Wednesday, May 4, 2011 1:31 PM
    Monday, April 25, 2011 1:41 PM
  • Ron,

    Not sure what you mean?   Do you mean:

    Create a new "Print" worksheet

    1. Load the scorecard with the player information
    2. Copy the 2 pages to a "Print" sheet
    3. Continue doing this and concatenating the new data below the last one
    4. save the "Print" sheet as PDF
    Monday, April 25, 2011 3:39 PM
  • Joel,

    I'm going to need to play with your code.  I don't totally understand it but I think this is kind of what I'm looking for.  If I read the code right (not always a given) this would save all the worksheets as an array and then print the array using PDFCreator.  I was hoping to use the built-in PDF creation abilities of Office 2010.   And I'm not really printing different worksheets.  I'm printing the same worksheet that has different data loaded into it, but it seems like I could loop through that.

    Saving the print area as an array and then printing the array seems like it might be a solution - if that's what this code does.....

    The original worksheet that is the scorecard has 2 pages in it's printarea.  Will that make a difference?

    Is there a way to save the worksheet as a PDF using Office and not PDFCreator?

    I'll let you know how I make out.




    Monday, April 25, 2011 3:50 PM
  • Yes like that
     
    Maybe this page will help
     
    Or if the info is already on sheets use
     
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm


    "fedude" wrote in message news:87117740-33c4-4d4a-acc6-acf767871af1...

    Ron,

    Not sure what you mean?   Do you mean:

    Create a new "Print" worksheet

    1. Load the scorecard with the player information
    2. Copy the 2 pages to a "Print" sheet
    3. Continue doing this and concatenating the new data below the last one
    4. save the "Print" sheet as PDF
    • Marked as answer by fedude Wednesday, May 4, 2011 1:31 PM
    Monday, April 25, 2011 4:24 PM
  • But you can also select the sheets if you want and run example 2 here
     
     
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm


    "Ron de Bruin" wrote in message news:d47f3030-89d7-4635-b22b-f3c722ecfe21...
    Yes like that
     
    Maybe this page will help
     
    Or if the info is already on sheets use
     
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm


    "fedude" wrote in message news:87117740-33c4-4d4a-acc6-acf767871af1...

    Ron,

    Not sure what you mean?   Do you mean:

    Create a new "Print" worksheet

    1. Load the scorecard with the player information
    2. Copy the 2 pages to a "Print" sheet
    3. Continue doing this and concatenating the new data below the last one
    4. save the "Print" sheet as PDF
    Monday, April 25, 2011 4:37 PM
  • The code I create is like going ot the print menu and say you want to print sheets 1 - 5 or the entire workbook.  The code creates an array of sheet names you want to print.  Then feeding the sheet names to the print driver.
    jdweng
    Monday, April 25, 2011 5:54 PM
  • I know its been a while since the last post, but I came to this and a viable solution dawned on me:

    The ExportAsFixedFormat method will print only VISIBLE sheets.  So, you could generate the 2 sheet pairs for each golfer in your workbook, then pass the sheets to print to the function as a comma separated string list to printSheetsToPDF() routine.

    So, make a record of worksheet visibility, then make only those sheets visible that you want to print (use of dictionaries make this easier/faster), then use the exportAsFixedFormat method, then revert to the original workbook sheet visibility state.

    Here's my code in VBA:

    Sub printSheetsToPDF(strSheetsToPrint As String, pdfFile As String)
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim vSheets As Variant
    Dim myDict As Object
    Dim myDictVisible As Object
    Dim i As Long
    
        Set wkb = ThisWorkbook
        Set wks = ActiveSheet
        Set myDict = CreateObject("Scripting.Dictionary")
        Set myDictVisible = CreateObject("Scripting.Dictionary")
        
        vSheets = Split(strSheetsToPrint, ",")
        
        'capturing visibility
        For Each wks In wkb.Sheets
            myDictVisible.Add (wks.Name), wks.Visible
        Next wks
        
        'make PDF sheets visible
        For i = LBound(vSheets) To UBound(vSheets)
            wkb.Sheets(vSheets(i)).Visible = xlSheetVisible
            myDict.Add vSheets(i), Nothing
        Next i
        
        'make all other sheets not visible
        For Each wks In wkb.Sheets
            If Not myDict.exists(wks.Name) Then
                wks.Visible = xlSheetHidden
            End If
        Next wks
            
        'select sheets to print (not really necessary, as all visible sheets will be printed with the ExportAsFixedFormat method
        'wkb.Sheets(vSheets).Select
        
        On Error Resume Next 'this method must exist, and should in Excel 2007+.  Excel 2007 latest SP has this addin, otherwise, it can be downloaded at http://labnol.blogspot.com/2006/09/office-2007-save-as-pdf-download.html
        
        If Dir(pdfFile) <> vbNullString Then
            Kill pdfFile
            If Err.Number <> 0 Then
                MsgBox "Cannot delete PDF File: " & pdfFile & " You may have it open - close it and try again"
                Exit Sub
            End If
        End If
        
        wkb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFile, quality:=xlQualityStandard, includedocproperties:=True, _
                                ignoreprintareas:=False, openafterpublish:=True
        On Error GoTo 0
        
        'restore to prior visibility
        For Each wks In wkb.Sheets
            wks.Visible = myDictVisible(wks.Name)
        Next wks
        
        myDict.RemoveAll
        myDictVisible.RemoveAll
        Set myDict = Nothing
        Set myDictVisible = Nothing
    End Sub

    Cheers,

    Dave

    http://www.experts-exchange.com/M_2005511.html

    • Proposed as answer by DaveLM Friday, March 30, 2012 9:54 AM
    • Edited by DaveLM Saturday, March 31, 2012 8:38 PM
    Friday, March 30, 2012 9:54 AM