Excel to pdf Bookmarks on wrong pages

I've uploaded an excel file that causes the bookmark issue, along with 2 pdf files with and without good bookmarks generated from variations of the excel file. The excel link is at :

https://share.acrobat.com/adc/document.do?docid=b0824850-baa9-4026-a68e-cc94e4959b42

Tab L16.2.3 has the print titles. If the print titles are on, extra pages are created and the bookmarks are incorrect. See bad pdf bookmark link:

https://share.acrobat.com/adc/document.do?docid=ec8f2026-76b4-4aba-b12e-305d605d3546

If the print titles are not on, then the book marks are fine. However we need print titles on. See good bookmark pdf link:

https://share.acrobat.com/adc/document.do?docid=52701cda-ccba-4b90-8407-52ee8de24e48

Hi Dan,

If you do not require a tagged output PDF (to support Section 508 accessibility or PDF reflow then change the PDF conversion settings to uncheck the "Enable Accessibility and Reflow with tagged Adobe PDF" selection.

n.b.,
In the sample *.xls file, only worksheet L16.2.1 has: Print Titles > Rows to repeat at top: $1:$1
The TOC worksheet has a page break between columns "A" and "B".
[This results in the output PDF's first page being column "A" content and the second page being column "B" content.]
The text string in row 1 of worksheets L16.2.1 through L16.2.8 spans 10 columns ("A" through "J")
The *.xls file's print setup is for page size :: letter, page orientation :: portrait.
The margins' values (Top: 075, Bottom: 0.75, Left: 0.7, Right: 0.7)
The soft page break for these worksheets occurs between columns "I" and "J".
[The multiple pages in the output PDF pages for these worksheets can be attributed to the location of this page break.]



Converted to PDF. In the AdobePDFMaker, selected all worksheets (TOC through L16.2.8).
Convert to PDF settings used where:
Used "Prompt for selecting Excel Sheets" and "Add Links to Adobe Pdf".
De-selected "Enable Accessibility and Reflow with tagged Adobe PDF".

In worksheet L16.2.1 (which has Print Titles > Rows to repeat at top: $1:$1), column A cells, rows 2 - 100, were populated with a short text string to provide a multi-page worksheet.
In Excel:
--| Left TOC worksheet with the page break between column "A" and "B", page size: letter, page orientation: portrait
--| Changed page orientation to landscape for worksheets L16.2.1 and L16.2.2.

Regarding the output PDF -
Page 1 :: the content was the TOC worksheet's "A" column of links.
Page 2 :: the content was the TOC worksheet's "B" column text.
Pages 3 - 5 :: Content from worksheet L16.2.1. Content from row $1:$1 repeated on each page. No blank, 2nd page (column "J").
Page 6 :: Worksheet L16.2.2 (landscape orientation => no second page for column "J").
Page 7 - 8 :: Worksheet L16.2.3 (column "J", the second page in the worksheet became page 8)
Remainder of the 17 pages mirror the the L16.2.3 output (two page per worksheet - where the 2nd page reflects the content of column "J" (empty/blank).

Note: the TOC's L16.2.2 link, for some reason, had to be reset in Excel. After doing so, the associated link in the output PDF worked.

A link to the output PDF is below. It has an initial view set to show the Pages panel in the Navigation Panel.

[url]https://share.acrobat.com/adc/document.do?docid=6f7db50f-8504-46f1-8dfe-798fcefa72a8[/url]

In summary: With some adjustment(s) to the page layout of your *xls file and approriate setup of PDFMaker you ought to be able to achieve what you want. If the output PDF does need to be tagged then you would have to engage in post-processing of the PDF with Acrobat Professional.

Dan,
An addendum. Selecting a page size other than letter and tweeking the left/right margin values would keep the pages' in a portrait orientation while preventing the second page associated with column "J".

In Excel's Page Setup (for all but the TOC worksheet):
Set paper size to ARCH A (9x12). Reduce the left and right margins to 0.6 inches.
When printing the PDF to hard copy, use the printer driver setup to fit the content to a letter size sheet.

You might want to run some trials with various L/R margin values.

Sample output PDF:
[url]https://share.acrobat.com/adc/document.do?docid=c375a5e4-74ca-4baf-9ead-cc20a88f2dc1[/url]

Be well...

Daka630,

Thanks for the reply. I cleaned up the page formatting for portrait printing.

I deselected "Enable Accessibility and Reflow with tagged Adobe PDF." and the error message did not occur, however the bookmarks (created in Adobe) are still linking to the wrong pages, even when I remove the print titles.

https://share.acrobat.com/adc/document.do?docid=6e41a14d-5dc8-4c1f-a0a8-1f9a1f0cc841

If I remove the print titles, and re-enable "Enable Accessibility and Reflow with tagged Adobe PDF", the adobe bookmarks function correctly, however we need to repeat rows in out outputs.

I have been manually re-book marking the document with Acrobat Standard, however I was looking for a solution to avoid that step since we need the rows to repeat on the top of the pages.

Any other suggestions would be helpful.

Dan

Hi Dan,

re: PDF TesFile-ICH-to-PDF-print-titles_2.pdf

Page 1: Eight items listed. Only 7 link boxes present.
Top down, these are:
Link to page 2 (Listing 16.2.1)
Link to page 4 (Listing 16.2.3)
Link to page 5 (Listing 16.2.4)
Link to page 6 (Listing 16.2.5)
Link to page 7 (Listing 16.2.6)
Link to page 8 (Listing 16.2.7)
Link to page 9 (Listing 16.2.8)

Link "boxes" in the PDF are off-set from the underlying text.

An invite to poke 'n prod, eh?
Downloaded original Excel file.
Reset row column span to columns"A" through I" for worksheets L16.2.1 through L16.2.8.
[...no desire to fiddle with the extra pages that having column "J" in play poses or with page size/orientations]
Noted that TOC entries in column B are pulled from each respective worksheet's cell A1.
In the TOC worksheet, deleted all links. Resulted in data (text entry) for L16.2.1 being removed.
Note:
TOC cell B2 => =L16.2.2.1!$A$1
And worksheet L16.2.1 cell A1 is a defined name (L16.2.1!Print_Titles) to support printing the cell span on new pages >= page 2.
This combination may be contributing to the problem.

Manually re-entered text for columns "A" and "B" below existing rows. Deleted original rows 2 through 9.
Copied TOC cell B2 contents to clipboard. Pasted this to worksheet L16.2.2.1 cell A1.
Re-created Hyperlinks for TOC cells A2 through A9 to appropriate worksheets' A1 cell.

On TOC worksheet, set Print area to $A$1:$B$9.
[TOC page 1, left as-built, results in extra blank pages in the output PDF.]

Added "filler" text to worksheet L16.2.2.1 to force a second page for checking repeat of sheet's row 1 "title".

Using the default conversion setting "Standard" (the Distiller job option), created output PDF with set up as discussed earlier.
n.b., Used other job options as well and had good output PDF so job options selection, as expected, is not an issue here.

Output PDF results.
PDF pages :: have correct, expected page count and content.
Links from TOC's "Listing" column :: Each link correctly traverses link path to desired target.
Worksheet L16.2.2.1 "title/header" row repeats :: Yes.

Summary:
It appears that populating the TOC worksheet's column "B" cell B2 from the worksheet L16.2.2.1's A1 cell, which is also a "Defined Name" is a significant contributor to the problem(s) you are experiencing.
As stated above, I did a full replacement. However, you may be ok with a "pull" from the other worksheets. Just avoid doing so from any cell that is also a "Defined Name".

The tweaked Excel file and the output PDF file are here -
Excel File :: [url]https://share.acrobat.com/adc/document.do?docid=3ca1646a-c90b-4011-8940-ced739a9931a[/url]
PDF File :: [url]https://share.acrobat.com/adc/document.do?docid=e005ffba-b401-4688-8360-ecc7021c4203[/url]

Be well...

Daka 630,

Sorry for the delay in responding to the post.

I reviewed the PDF file you posted and the Acrobat bookmarks are not present. I tried the suggestions and I discovered the 'print area' needs to be set for each worksheet in order for Acrobat to create the bookmarks correctly.

Thanks for your investigation into the matter.

Regards,

Dan

Dan,
I did not select "Add Bookmarks to Adobe PDF" in the Acrobat PDFMaker configuration for the Excel file.
Actually, on the last round, I was more curious about the XLS file's links and build.

I downloaded the XLS file I'd uploaded (see link in last post).
Configured PDFMaker to add bookmarks. Generated an output PDF. Got the bookmarks & all the rest.Be well...

I have this same problem. I have Standard 8 Acrobat and need to pdf an Excel workbook with 85 sheets. All sheets are "fitted to one page" in A5 format and have Print Area selected. When I create the pdf I get the message:

Acrobat PDFMaker cannot create Tags for worksheets with Print Area set. Clear the 'Print area' field in Page setup to get this feature.
Do you want to proceed and create a PDF file without Tags?"

I select Yes and it creates the pdf WITH bookmarks, but the bookmarks start pointing to the wrong page from the 11th page onwards. I then have to manually correct each page.

I tried removing the Print Area from every sheet, but then it completely stuffs up and still doesn't point to the correct pages.

I have tried all sorts of different options, etc. to no avail. Please somone, help!

Ann

Ann (MACCA999),

The sample Excel file I posted is just the header pages for an ICH Clinical Study Report for Tables and Listings. The Excel file we use is about 1000 pages long with about 80-90 bookmarks.

That being said, the only solution I've found that works is to set the print area on each worksheet (don't forget any, because that is where the bookmarks will mess up). You can then print titles for repeating rows.

I'm looking into a Macro that will bookmark the PDF after it has been created based on our hyperlinks TOC page, but have not found one yet. I was going to contact this company: http://www.a-pdf.com/customization.htm. Has anyone used them before? It looks like they can write custom macros.

Dan

Hi Dan!
Thanks for your reply - my job is obviously a lot smaller than yours, but I definitely have set Print Areas on all worksheets and, as I said, the bookmarks set fine until the 10th page and then start dropping behind by two pages, eventually getting behind by six pages towards the end. Damned annoying!

Ann

Hi This page with most interesting comments with useful links

Hey guys! These posts are all very helpful, but I found a similar program to be quite useful, especially in business. At my work we use this program that quickly and painlessly converts my excel documents into pdf. It provides my business a simple way to make individualized statements for our business associates. Check it out here.