sheet names to pdf bookmarks

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
Hello,

I like to print my workbooks to pdf.
With typically 50 sheets, it can be useful to create a bookmark for each sheet.
I could not succeed to do that.

In addition, I would like to do all this in VBA, if possible.
Till now I print to a postscript using the PrintOut method.
I would be interrested to print directly to a pdf, and include the sheet names as bookmarks.

Thanks for any suggestion.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
The following code may be useful but it will not help you with the bookmarks.
This prints using CutePDF (free version) and takes 'control' information from worksheet "Control Sheet". Name 'PDF_Printer' is a named cell containing the path to the printer (e.g. "CutePDF Writer on CPX2:") and "PrintWorksheets" is a named range (for a table) containing the names of the worksheets that I want to print - this can easily be changed to print all worksheets.
Code:
Option Explicit
Option Base 1
Option Private Module
Sub Print2CutePDF()
Dim wb As Workbook
Dim ws As Worksheet
Dim wsActive As Worksheet
Dim strCurrentPrinter As String
Dim strPDFprinter As String
Dim strSheets() As String
Dim strFirstSheet As String
Dim intIndex As Integer
Dim rng As Range
'
strCurrentPrinter = Application.ActivePrinter
Set wsActive = ActiveSheet
strFirstSheet = ""
intIndex = 1
'
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Control Sheet")
ws.Activate
strPDFprinter = ws.[PDF_Printer].Value
For Each rng In ws.[PrintWorksheets]
    ReDim Preserve strSheets(1 To intIndex)
    strSheets(intIndex) = rng.Value
    intIndex = intIndex + 1
    If strFirstSheet = "" Then
        strFirstSheet = rng.Value
    End If
Next rng
' select all sheets to be printed:
    wb.Sheets(strSheets).Select
    wb.Sheets(strFirstSheet).Activate
' print to PDF
    Application.ActivePrinter = strPDFprinter
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=strPDFprinter, Collate:=-True
    '
' ungroup sheets
    wb.Worksheets(strFirstSheet).Select
    '
    On Error Resume Next
    '
    Application.ActivePrinter = strCurrentPrinter
    wsActive.Activate
    Set rng = Nothing
    Set wsActive = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub
The code changes the default printer and then changes it back at the end. I have trimmed the code so you will need to add error-trapping and ensure that the default printer always gets reset.
If you don't find the answer to bookmarks you could look at JAWS PDF Editor but you will need to do that part manually.
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,282
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top