I have finally found a solution to my problem, using some VBA code and the ExportAsFixedFormat
method. Basically, that function will export the current range with a type and filename. The answer below, while specific to my case, is easily adapted to anyone's situation.
After some deliberation and coding, I came up with a solution to find the proper ranges - although it is far from perfect. I wrote the following VBA function (for use with Excel only), which returns the range between two horizontal page breaks (you do need to specify the starting and ending columns):
' Name: Get Range
' Desc: Returns a string representing a range (e.g. "A1:Z30"), which encompasses the specified page breaks.
' Args: startHPBreak - Used to determine which starting page break to use, from 0 up to the number of pagebreaks.
' endHPBreak - Used to determine the last page break to return up to.
Function GetRange(ByVal startHPBreak As Long, ByVal endHPBreak As Long) As String
Dim startCol, endCol As String ' First, we define our starting/ending columns.
startCol = "A"
endCol = "Z"
Dim numHPBreaks As Long ' Holds the total number of horizontal page breaks.
Dim startRow, endRow As Long ' Holds the starting/ending rows of the range.
' First, we get the total number of page breaks.
numHPBreaks = ActiveSheet.HPageBreaks.Count
' Then, we check to see the passed ranges are valid (returns a blank string if they are not).
If (startHPBreak < 0) Or (startHPBreak > numHPBreaks) Then Exit Function
If (endHPBreak <= startHPBreak) Or (endHPBreak > numHPBreaks) Then Exit Function
' Next, we build our string by obtaining our starting and ending rows.
If startHPBreak = 0 Then ' If we're starting at the 0th page break...
startRow = 1 ' Then start exporting at the first row.
Else ' Else, just get the starting page break row.
startRow = ActiveSheet.HPageBreaks(startHPBreak).Location.Row
End If
' Lastly, we get the ending page break row, build the range as a string, and return it.
endRow = ActiveSheet.HPageBreaks(endHPBreak).Location.Row - 1
GetRange = startCol & startRow & ":" & endCol & endRow
End Function
There are some caveats, mainly that you need to have horizontal page breaks defined for this to work. In my case, this function worked, but there were graphs on each page. Whenever I tried to use a range over a single page, the graphs on the subsequent pages would all be cut off for some reason, so I wrote the following function to add each page as a different range in a a string:
Finally, the created range was passed to the ExportAsFixedFormat
function as detailed at the top of my answer through the following function (I only wanted to export pages 1-2 and 12-17):
' Name: Export Pages
' Desc: Exports the specified pages/range in the function to the passed filename as a PDF.
' Args: outFileName - Full or relative file name (include the extension) to export the file as.
Sub ExportPages(outFileName As String)
Dim outputRange As String ' Used to build the output range.
Dim currPage As Byte ' Holds the current page we want to export.
' There are no graphs on the first two pages, so we can get that range all at once.
outputRange = GetRange(0, 2)
' Next, we loop through and add pages 12 to 17 to the range (since they have graphs).
For currPage = 12 To 17
' We build the range one page at a time, and seperate the ranges with commas.
outputRange = outputRange & "," & GetRange(currPage - 1, currPage)
Next currPage
' Finally, we use the outputRange string to specify the range, and export it as a PDF.
ActiveSheet.Range(outputRange).ExportAsFixedFormat _
Type := xlTypePDF, _
Filename := outFileName, _
Quality := xlQualityStandard, _
IncludeDocProperties := True, _
IgnorePrintAreas := False, _
OpenAfterPublish := True
End Sub
Note that I had to add the range for pages 12 to 17 one at a time, otherwise (as I mentioned) my graphs would be cut off. I don't know why I had to do this, but it worked in the end. Hopefully what I've posted here is enough to get someone on the right track using VBA code to export a document as a PDF file.
If anyone can come up with a better solution in the future, please post it as an answer and it will be considered.