Save same sheet to PDF multiple times - is it possible?

[Prophet4Profit]

Hello, I'm a new poster to the forum. I have a question and cannot find the answer anywhere so I thought I would ask here. I would like to know if it is possible to save the same sheet to a PDF in a loop (with different data each time) creating a multi-page PDF. As a test, I have been able to save multiple excel sheets from the same workbook to one PDF but ultimately that is not what I am trying to do.

Here is a little more detail: I have sheet that I import a bunch of data to. I run some calculations on each set of data from what I imported in a loop and at the end of calculating each set of data I want to save the results to a PDF. However, I want each set of data saved to a separate page within the same PDF file.

I have tried to make it work unsuccessfully and am now to the point wondering if it is even possible. Is it, or should I try a different approach? Speed is important because there is the potential to be going through a lot of data creating a lot of pages.

Thanks

Jeff

 

[DanteAmor]

Hi ★ Prophet4Profit, welcome to the forum!

Do you already have a code to execute the loop?

 

[Prophet4Profit]

Hello and thanks for the response. I don't have the code yet - currently I have code to save multiple excel sheets to one PDF. I have been researching whether or not the idea is even possible before attempting to write code. Ideally I imagined I would try to put each sheet iteration into an array and then print the array of sheets (if possible). The other idea is to put each sheet iteration on its own sheet and then print all sheets (which I currently have code for) but there are limitations to the number of sheets in a workbook. Any ideas or suggestions would be greatly appreciated.

Thanks

Jeff

 

[DanteAmor]

You can create a new book, you can put each data set on a sheet of the new book, that is, if you have 10 data sets, then you will end up with 10 sheets in the new book.
Then you save the entire book as a pdf.


What I don't understand is how you build each data set.

 

[Prophet4Profit]

I understand what you are suggesting which is the route I was initially going to take (and may still) - this is the code I have used for combining all worksheets into one PDF (which works):
I was just curios if the way I initially described is even possible or if there is a better/faster way.

Sub PrintAllSheetsToOnePDF() 'Combine all worksheets into one PDF


Dim strSheets() As String
Dim strfile As String
Dim sh As Worksheet
Dim icount As Integer
Dim myfile As Variant


'Save Sheet names to an Array
For Each sh In ActiveWorkbook.Worksheets
    If sh.Visible = xlSheetVisible Then
        ReDim Preserve strSheets(icount)
        strSheets(icount) = sh.Name
        icount = icount + 1
    End If
Next sh


If icount = 0 Then 'None found error.
    MsgBox "A PDF cannot be created because no sheets were found.", , "No Sheets Found"
Exit Sub
End If


'Prompt for save location
strfile = "Sheets" & "_" _
    & Format(Now(), "yyyymmdd_hhmmss") _
    & ".pdf"


strfile = ThisWorkbook.Path & "\" & strfile


myfile = Application.GetSaveAsFilename _
    (InitialFileName:=strfile, _
    FileFilter:="PDF Files (*.pdf), *.pdf", _
    Title:="Select Folder and File Name to Save as PDF")


If myfile <> "False" Then 'save as PDF
    ThisWorkbook.Sheets(strSheets).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
Else
    MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"
End If


End Sub

Thanks

Jeff

 

[DanteAmor]

In fact, that way you have seems like a good option.

 

[SpillerBD]

I was just curios if the way I initially described is even possible or if there is a better/faster way.

Jeff

You are constrained by the capabilities of Excel (and the Microsoft VBA.) Neither the MS Export to PDF nor the Print to PDF function in Excel has the ability to Append the file.
You are better off I think with the method you have as the time required for opening and appending a PDF file multiple times is likely far greater than creating the multiple sheets.

Using the Export as PDF is far better in Word, but I don't see any significant difference for Excel. Word allows bookmarks to appear in an Exported PDF.