Import a PDF in Excel Power Query Using a Dynamic File Path
Question details
Create a dynamic file path for a PDF import by combining a folder path and file name stored in Excel cells.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user wants a Power Query PDF connection to update automatically when the folder location or PDF file name changes in the worksheet.
- Observed behavior
- Power Query needs a complete file path before it can read the PDF and return its available tables.
Create two single-cell named ranges in the workbook: FolderPath and PCRFileName. Make sure FolderPath ends with a backslash (\) on Windows or the appropriate path separator for the location you use.
Use Excel named ranges to build the PDF file path in Power Query
Use this method when the PDF folder and file name should be controlled from worksheet cells rather than hard-coded in the query.
Power Query can read values from named ranges with Excel.CurrentWorkbook, combine them into one path, and pass that path to File.Contents. After the PDF source is created, select the table or page you need and apply the required transformations.
In Excel, enter the PDF folder path in one cell, such as C:\Reports\, select the cell, and use Formulas > Define Name to name it FolderPath.
Enter the PDF file name, such as monthly-report.pdf, in another cell. Select it and use Formulas > Define Name to name it PCRFileName.
Go to Data > Get Data > Launch Power Query Editor, then select Home > New Source > Blank Query.
Select Home > Advanced Editor and use this M code: let FolderPath = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1], PCRFileName = Excel.CurrentWorkbook(){[Name="PCRFileName"]}[Content]{0}[Column1], Source = Pdf.Tables(File.Contents(FolderPath & PCRFileName), [Implementation="1.3"]) in Source
In Power Query Editor, choose the required PDF table or page from the Source results, apply transformations, then select Home > Close & Load to return the data to Excel.
Use a Power Query parameter for a fixed or manually updated PDF location
Use this option when the PDF location changes occasionally and you do not need worksheet cells to control the path.
Use WPS Office for Everyday Spreadsheet Work After Exporting PDF Data
Power Query PDF connectors are a Microsoft Excel feature, so WPS Office does not repair or run an Excel Power Query PDF query. For lightweight spreadsheet editing, reviewing imported results, and working with shared workbook files, WPS Office provides a free, familiar Office-style alternative with seamless file migration.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheet from the app launcher.
- 2. Open the exported workbook: Select Open and choose the XLSX workbook that contains the PDF data already loaded or exported from Excel.
- 3. Review and continue spreadsheet work: Use WPS Spreadsheet tools to edit cells, apply formulas, format tables, and save the workbook in XLSX format for sharing.
Frequently Asked Questions
Why does my Power Query PDF path return a file not found error?
Check that FolderPath and PCRFileName produce one complete valid path. In particular, ensure the FolderPath cell ends with a backslash, such as C:\Reports\, before it is combined with the file name.
Can I store the folder path and PDF name in regular Excel cells?
Yes, but Power Query reads them most reliably through named ranges. Select each one-cell value and use Formulas > Define Name, then reference the names with Excel.CurrentWorkbook in the query.
How do I refresh the PDF import after changing the file name?
Update the PCRFileName named-range cell, then select Data > Refresh All in Excel. Power Query rebuilds the path and imports from the new PDF file.
What does Pdf.Tables return in Power Query?
Pdf.Tables reads detectable tables and pages from the PDF and returns a navigation result. Select the appropriate item in Power Query Editor, then expand, filter, or transform its data.
Can the same query import PDFs from different folders?
Yes. Change the value in the FolderPath named range to the new folder, keep the correct file name in PCRFileName, and select Data > Refresh All.
