How to Extract PDF Bookmarks Using Excel VBA and Acrobat Type Library
Question details
The user needs to extract bookmark names and contents from PDF files into an Excel spreadsheet using VBA.
- Product
- Excel, Adobe Acrobat Standard
- Device & OS
- not provided
- Scenario
- Automating the extraction of PDF bookmarks to create a catalog or index of document contents within an Excel worksheet.
- Observed behavior
- Adobe does not provide a native front-end tool to export bookmarks directly to Excel, requiring the use of a custom VBA script leveraging Acrobat COM objects and the JavaScript Object Model.
Ensure you have a full version of Adobe Acrobat installed (such as Acrobat Standard 2017 or Professional). The free Adobe Reader application does not provide the required COM object functionality needed for this VBA script to work.
Extract Bookmarks Using Acrobat COM Objects and JavaScript Model
Use the Adobe Acrobat Type library in the Excel VBA editor to access the PDF document's JavaScript object and iterate through the BookmarkRoot.Children collection.
This method utilizes Acrobat's COM objects to bridge Excel VBA and the PDF file. It effectively reads flat bookmark structures.
If your PDFs contain nested bookmarks, the basic iteration will only grab top-level items. You will need to implement a recursive function to process sub-children within the bookmarks.
Open Excel and press Alt + F11 to launch the VBA Editor. Navigate to Tools > References in the top menu, scroll down, and check the box for 'Adobe Acrobat XX.0 Type Library' (the version number will vary based on your installation).
Insert a new module and declare your variables for Acrobat.CAcroApp, Acrobat.CAcroAVDoc, and Acrobat.CAcroPDDoc. Use CreateObject("AcroExch.App") to initialize the Acrobat application.
Open the target PDF file path using avDoc.Open. Next, retrieve the underlying document object with avDoc.GetPDDoc, and obtain the JavaScript object by setting jso = pdDoc.GetJSObject.
Assign the children of the bookmark root to a variant array using vBookmarks = jso.BookmarkRoot.Children. Loop through this array (For Each Child In vBookmarks) and extract Child.Name into your designated worksheet cells.
After extraction, close the document using pdDoc.Close and avDoc.Close False. Set all objects to Nothing to clear your system's memory and prevent background instances of Acrobat from locking files.
Run VBA Macros Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros and COM object integrations. You can run your existing Excel scripts to automate PDF bookmark extraction by hooking into the Acrobat Type Library directly within WPS Office.
- 1. Install WPS Office: Download and install WPS Office, then launch the WPS Spreadsheet application.
- 2. Access the VBA Editor: Open your macro-enabled workbook (.xlsm) and press Alt + F11 to launch the built-in WPS VBA Editor.
- 3. Run Your Macro: Navigate to Tools > References to link the Adobe Acrobat Type Library, paste your bookmark extraction code, and execute it just as you would in Microsoft Excel.
Frequently Asked Questions
Can I extract PDF bookmarks using Adobe Reader instead of Adobe Acrobat?
No, Adobe Reader does not expose the necessary COM objects or JavaScript Object Model required by Excel VBA to extract bookmarks. You must use a paid, full version of Adobe Acrobat like Standard or Pro.
How do I handle nested bookmarks in my VBA script?
The standard BookmarkRoot.Children iteration only retrieves top-level (flat) bookmarks. To capture nested bookmarks, you must write a recursive VBA function that checks if each child bookmark contains its own sub-children and loops through them appropriately.
Why do I get a 'User-defined type not defined' error in VBA?
This error occurs if the Adobe Acrobat Type Library is not enabled in your macro environment. Go to Tools > References in the VBA Editor and ensure the box next to 'Adobe Acrobat Type Library' is checked before running the code.
Can I extract the page numbers along with the bookmark names?
Yes, but accessing page numbers requires executing specific JavaScript methods within the Acrobat API via the GetJSObject to evaluate the destination page index for each individual bookmark object.
