PDF Conversion Issues

How to Save an Active Excel Worksheet as a PDF in the Same Folder

Natalie Taylor Oct 21, 2026 868 views

Question details

The user wants to export the currently active Excel worksheet as a PDF file, save it directly into the same directory as the host workbook, and dynamically generate the PDF filename using values from specific cells (e.g., B3 and B2).

How to Save an Active Excel Worksheet as a PDF in the Workbook Folder

Product: Excel
Device & OS: not provided
Scenario: Automating the PDF export process of an active worksheet using VBA to streamline saving reports or calculations in a specific folder without manually browsing and typing file names.
Observed behavior: The user requires a working VBA script that accurately captures the active workbook's folder path and concatenates specified cell values to construct the destination PDF path before executing the export.

Before you start

Ensure that your Excel workbook has been saved at least once to a local drive so that the macro can detect a valid folder path, and verify that the target cells used for the filename do not contain invalid characters for file names (such as /, \, *, ?, <, >, |).

Solution 1Recommended

Use a VBA Macro to Export Worksheet as PDF

Utilize a VBA script combining ThisWorkbook.Path and ExportAsFixedFormat to dynamically generate the PDF path and save the active sheet.

By utilizing VBA, you can fully automate the process of creating PDFs from your active worksheet. The property ThisWorkbook.Path ensures the PDF is saved exactly where the Excel file is located, and Application.PathSeparator makes the macro more reliable across different system environments.

Open the VBA Editor

In Excel, press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click on 'Insert' in the top menu and select 'Module' to create a blank workspace for your code.

Construct the Dynamic File Path

Define a string variable for the path. Use the formula: pdfPath = ThisWorkbook.Path & Application.PathSeparator & Range("B3").Value & " - " & Range("B2").Value & " - Reserve Calculation.pdf".

Execute the Export Command

Add the command to export the sheet: ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath.

Run the Macro

Close the VBA editor and press F5 or assign the macro to a form button on your worksheet to generate the PDF instantly in the workbook's folder.

Handling Unsaved Workbooks: If you try to run this macro on a brand-new workbook that has never been saved, ThisWorkbook.Path will return an empty string, causing a run-time error. Always save the file first.

Export Worksheets to PDF Effortlessly with WPS Spreadsheet

WPS Spreadsheet offers built-in, easy-to-use tools to export your worksheets directly to PDF. Whether you want to use a quick graphical interface or run advanced VBA macros, WPS Office gives you full control over your documents.

1. Open your workbook: Launch WPS Spreadsheet and open the file you wish to convert.
2. Access the Export tool: Click on the 'Menu' button in the top left corner and select 'Export to PDF'.
3. Configure export settings: Choose 'Active sheet' as the export range and configure any desired layout options.
4. Save to the same folder: Set the save path to your current workbook's directory, enter your desired filename, and click 'Export'.

Native 'Export to PDF' feature built directly into the main interfaceHigh compatibility with Microsoft Excel formats including .xlsm and VBA macrosDynamically select active sheets, entire workbooks, or specific print areasLightweight, fast application that consumes minimal system resources

microsoft office alternative - wps office

Frequently Asked Questions

Why does ThisWorkbook.Path return an empty string in my VBA code?

This occurs if the Excel workbook is brand new and has not yet been saved to your computer. You must save the Excel file to a local drive first before running the macro.

Can I save the PDF to a different folder instead of the workbook's folder?

Yes, you can modify the VBA code to replace ThisWorkbook.Path with a hardcoded directory string, such as "C:\Users\YourName\Documents\".

What causes a run-time error when executing the PDF export macro?

Run-time errors during export are usually caused by illegal characters (such as colons, slashes, or asterisks) in the cell values used for the filename, or if a PDF with the exact same name is currently open in another program.

Does this macro work on Mac versions of Excel?

While Application.PathSeparator helps with cross-platform compatibility, Mac Excel VBA has strict sandboxing rules. You may need to grant file access permissions or use Mac-specific path structuring for the macro to run successfully.

PDF Troubleshooting