VBA & Macro Problems

How to Import Scanned PDFs into Excel and Add Hyperlinks using VBA

Chanuka Geekiyanage Oct 1, 2026 868 views

Question details

The user needs to extract specific records from scanned PDFs, compile them into a master Excel table, and use VBA to automatically generate hyperlinks pointing back to the original PDF files.

How to Import Scanned PDFs into Excel and Add Hyperlinks with VBA

Product: Excel
Device & OS: not provided
Scenario: Consolidating structured data (such as patient names and prescription details) from physical scanned documents into a centralized database while maintaining quick access to the source files.
Observed behavior: Because the files are scanned PDFs, they consist of unsearchable image data. Excel and VBA cannot directly read this text without first running Optical Character Recognition (OCR) to normalize the documents.

Before you start

Before running any VBA scripts, ensure your scanned PDFs have been processed through an OCR (Optical Character Recognition) tool to convert the images into searchable, extractable text files or Excel workbooks.

Solution 1Recommended

Extract PDF Text via OCR and Automate Data Import with VBA

Convert scanned documents into readable Excel files using an external OCR tool, then use a VBA macro to consolidate the data and generate hyperlinks automatically.

VBA cannot natively read image-based PDFs. The workflow requires a two-step process: converting the files via OCR, and then deploying a VBA script to scrape the standardized output and generate clickable links.

Convert Scanned PDFs with OCR

Use an OCR tool (like Adobe Acrobat or WPS PDF's 'PDF to Excel' feature) to batch convert your scanned PDFs into individual Excel workbooks. Save all converted files in a single, dedicated folder on your computer.

Set up the Master Workbook

Create a new Excel workbook. In the first sheet, create header columns corresponding to the data you want to extract (e.g., Patient Name, RX Number, Date, PDF Link).

Open the VBA Editor

Press 'ALT + F11' to open the Visual Basic for Applications (VBA) editor. Click 'Insert' > 'Module' to create a blank script window.

Write the Extraction Script

Write a VBA loop using the 'Dir' function to open each converted workbook in your folder. Use the 'Range.Find' method to locate specific labels (like 'Name:'), use 'Offset' to capture the adjacent text value, and write the extracted data to the next empty row in your Master Workbook.

Add Hyperlink Generation

Within the same loop, use the 'ActiveSheet.Hyperlinks.Add' command. Set the 'Anchor' to your designated link column cell, and set the 'Address' to the file path of the original scanned PDF.

Extract PDF Text via OCR and Automate Data Import with VBA

Data Normalization: OCR conversion is rarely 100% perfect. Ensure your VBA script accounts for slight variations in text formats or extra spaces when using the Find method.

Solution 2

Manually Extract Data using Excel's Data from Picture Feature

If you only have a few scanned PDFs, you can use Excel's built-in image data extraction feature instead of writing complex VBA macros.

Seamless PDF and VBA Integration

Easily Convert PDFs and Run VBA Macros with WPS Office

WPS Office features highly accurate built-in PDF-to-Excel OCR tools and fully supports VBA macros, allowing you to streamline the entire extraction and hyperlinking process in one lightweight application.

1. Convert via WPS PDF: Open your scanned PDF in WPS Office. Navigate to the 'Tools' tab and select 'PDF to Excel' to automatically perform OCR and create an editable spreadsheet.
2. Launch the Macro Editor: Open a new WPS Spreadsheet, navigate to the 'Tools' tab, and click 'Macros' (or press ALT + F11) to open the VBA editor.
3. Run your Script: Paste your data extraction and hyperlinking script into a new Module and click the 'Run' button.
4. Save as Macro-Enabled: Go to 'Menu' > 'Save As' and select 'Excel Macro-Enabled Workbook (*.xlsm)' to preserve your script alongside your data.

Built-in PDF to Excel conversion with advanced OCR technologyFull support for Excel VBA macros and customized scriptingHighly compatible with Microsoft Excel (.xlsx and .xlsm) formatsLightweight, fast, and free to download

Frequently Asked Questions

Can VBA perform OCR on scanned PDFs directly?

No, VBA does not have native OCR capabilities. You must first use third-party OCR software (like Adobe Acrobat or WPS PDF) to convert the scanned images into searchable text or Excel workbooks before VBA can process the data.

What is the VBA code to add a hyperlink to a file?

You can generate a hyperlink using the Hyperlinks.Add method. The syntax looks like this: ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), Address:="C:\Documents\File.pdf", TextToDisplay:="View Original PDF".

Why does my VBA code fail to find labels after OCR conversion?

OCR output can sometimes introduce formatting inconsistencies, extra spaces, or minor spelling errors. Ensure your VBA 'Find' function uses flexible matching criteria (such as LookAt:=xlPart) and consider adding a script to trim whitespace before searching.

Can I automate the PDF to Excel OCR process using VBA?

Automating the OCR step via VBA requires integrating a third-party OCR API or command-line tool (like Tesseract OCR or Adobe Acrobat's API). Native Excel VBA cannot execute OCR on its own.

Excel & Data Problems