5

I am currently trying to save an Excel document to PDF, but the same process could be applied to a Word document. I would like to save a specific range of pages (e.g. pages 1, 3-5, and 8) to the PDF file.

When I try to save the file as a PDF, when I select PDF as the file type and click the "Options..." button, I am only given two options under "Page range". I can select either All, or Page(s), which allows me to select only a single range (e.g. 1-10 and that's it).

Is there any way to save a generic page range, similar to when I am printing the document, so I can enter a comma-separated list of pages (like 1, 3-5, 8) to include in the PDF?

While I am not shy of using VBA, I would like to avoid using a PDF printer, as I cannot install drivers on my work computer (no administrative privileges). I also want to try to keep the page numbers consistent (if I print pages 1,4,5, and 8, it says at the bottom "Page 1 of 3", "Page 2 of 3", etc). I know this is possible, since this happens when I export the document as a PDF with multiple selected cells.

Thank you.

2
  • What version of office? Jul 8, 2011 at 14:22
  • I'm using Microsoft Office 2007. Jul 8, 2011 at 14:24

4 Answers 4

2

I know you would prefer to do this directly from within Word, but lacking a real solution to do it that way I would like to suggest PDFsam (PDF Split and Merge) which, so long as Java is available on the machine, does not need to be installed in order to be used.

You can effectively split, re-order, merge and generally shuffle documents around quite easily using PDFsam

Get it from http://www.pdfsam.org/

What is pdfsam?

PDF Split and Merge is a very simple, easy to use, free, open source utility to split and merge pdf files. It’s released in two versions: basic and enhanced. Includes a console and a GUI interface.

1
  • I would love to use that at work, but there's two small problems... #1, that website is blocked at my work (boo!), and #2, we don't have Java 1.6. That being said, +1 for the program, I will be using that at home quite a bit. ;) Jun 29, 2011 at 1:05
2

How does the excel document look inside OpenOffice?

OpenOffice can export directly to PDF, and you can obtain a Portable version of OpenOffice so that it doesn't need to be installed on the system.

4
  • Unfortunately, I cannot download anything off of PortableApps at work (they block the usual "download" sites). However, I would be willing to give it a shot - can you confirm that you can save a particular page range as per my question to a PDF? (Again, I need this for both Excel and Word) Jun 29, 2011 at 2:07
  • Just installed Win7 on home machine, and OOo isn't available for some reason at present. In LibreOffice (official fork of OOo) if you go to File|Export as PDF... it opens a dialog and the very first item in the dialog is "Range" where it appears you can put what pages to export. Once I have OOo reinstalled I'll test it as well to confirm. Jun 29, 2011 at 7:40
  • Ah, don't worry then - I forgot LibreOffice was the new fork of OpenOffice. +1 for the suggestion. I would like to select this as answer, but I was hoping to see if there was a solution built-in to Microsoft Office (even using VBA)... If possible, do you know if page headers are updated when you go through the process (last paragraph in my question)? Jun 29, 2011 at 10:21
  • I can't say if it does or not, I don't have any files with page headers/footers to test on currently (and won't have access to such files until Sunday). I can check then if you're still looking. Jun 29, 2011 at 18:07
2

Try this. It's a PDF creator app that installs as a printer. You just specify the page range and print to this 'printer'.

1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .