Excel VBA export and append to PDF

Question

I am looking for some help to solve a save to PDF problem. I have a sheet with some information that prints on 2 pages. The information displayed on this sheet is controlled by a drop down option to select 1 of several hundred options. I have a print to PDF function working fine for a single view of the information displayed so I select 1 option using the drop down and click the save to PDF button which all works great.

I need to add a 2nd function that will loop through all of the options available in the drop down box and add all of this into a single PDF to be saved. I can handle all of the standard code in the loop but how do I build up the PDF file inside the loop. I know how to build an array of sheets to export to a single PDF but this kind of 'in loop' function is something I am struggling to find an answer to.

Can anyone help please? If you need any more info, please just ask.

Thanks

-1 This is going to be highly application dependent. That is, you're going to need to post some code to make it clear what you're doing now, and why it does not work. Generally speaking, Excel will export whatever you have to PDF. If you want to export multiple "views" etc. you will need to get all of them in one place and do a single PDF export. This typically involves creating helper sheets/workbooks. Alternatively, you could create multiple PDFs using Excel and use some other tool to combine them. There is no way to "append to a PDF" using Excel. — Byron Wall, Sep 26 '17 at 22:07
will try using helper sheets/workbooks but it feels like this may be intensive with over 400 records. I have found an example to merge PDFs so I will also try creating individual PDFs and merging them using this method. I think the second option may be a lot quicker but not sure at all. Is there any clever way of making the helper sheets/workbook option fast, optimising it, Byron? Not sure what protocol is here. Will I post my code once I get it working, as I don't have any code to post yet. In any case I will advise what option I go with and why. I am using Excel 2013, Adobe 10 standard — nelly2000, Sep 27 '17 at 06:33
Re: the 400 sheets, you might be surprised what Excel can handle. I would definitely put them in a fresh/blank Workbook that way you don't pollute your source Workbook. I've used this technique on some fairly intensive things and it tends to work. It's probably worth testing to see if it will run with that many, and then optimize the thing if it's too slow or can't run. More likely, it will run just fine and you'll be done. — Byron Wall, Sep 27 '17 at 15:30
Re: posting the code, Stack Overflow is meant to be a programming Q&A site. Folks (me included) tend to be less helpful when the question comes across as "please write a full solution for me". I assume that's not your intent, but it's common. It's difficult when you're looking for some tips to start and so that's what these comments are for. If you take a shot at this code and hit a snag, update your question with the new code. You will get a stronger response from folks willing to help you solve your problem once it's clear you've taken a shot and have an idea of what you're going for. — Byron Wall, Sep 27 '17 at 15:35
Thanks Byron. Will implement per your suggestion tomorrow. Advice appreciated, and you are right I definitely wasn't looking for anyone to give me a ready made solution, there's no fun in that! Will post back how I get on, am more confident in my performance concerns now after your last comments. Cheers — nelly2000, Sep 27 '17 at 21:17

score 0 · Answer 1 · answered Sep 26 '17 at 20:15

In the Solution, suppose that we have a table which named: Table2. We have also a help sheet(to store filtered tables) which is Hiden and named: Help.

Option Explicit

Sub print_to_pdf()
    Dim sh  As Long
    Dim rg  As Range
    Dim Rng As Range
    Dim rw  As Range

    Application.ScreenUpdating = False

    For Each rw In Range("Table2[#All]").Rows

        If rw.EntireRow.Hidden = False Then
            If Rng Is Nothing Then Set Rng = rw
            Set Rng = Union(rw, Rng)
        End If

    Next

    Rng.Copy

    With Sheets("help")
        .Visible = True
         sh = .Cells(Rows.Count, "A").End(xlUp).Row + 2
        Set rg = Range("a3" & ":" & "a" & sh - 2)

        .Activate
        .Cells(sh, "A").Select
        ActiveSheet.Paste

        ActiveSheet.PageSetup.PrintArea = rg

        ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\rep.pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

        .Visible = False
    End With

    Application.ScreenUpdating = True

    MsgBox "Your PDF Has been Created with Success!!", vbInformation
End Sub

You can append data in Help sheet then export pdf.

Ref: https://stackoverflow.com/questions/

You copied the linked answer verbatim even though it only marginally applies in this example. The posted code will not work based on OP's description since the underlying contents of the Range he wants exported are changing. That is, building a Range via Union and doing a single copy/paste are not going to help here. — Byron Wall, Sep 26 '17 at 22:12
That's complicated, is an approach of appending content to pdf. — mgae2m, Sep 27 '17 at 00:13

Excel VBA export and append to PDF

1 Answers1