VBA & Macro Problems

How to Save a PDF in the Active Workbook Folder Using a VBA Macro

Phi Hung Vo Oct 10, 2026 869 views

Question details

The user needs a VBA macro to save the active Excel workbook as a PDF in its own directory, but the PDF is incorrectly saving to the default Documents folder.

How to Save a PDF in the Active Workbook Folder Using a VBA Macro

Product: Excel
Device & OS: not provided
Scenario: Automating the export of an Excel workbook to a PDF file in the same directory using VBA.
Observed behavior: Using ActiveWorkbook.Path causes the PDF to save in the Documents folder instead of the current folder where the workbook resides.

Before you start

Ensure your Excel workbook has been saved at least once to a specific folder on your drive. An unsaved, brand-new workbook does not have a valid folder path, which will cause the macro to default to your system's Documents folder.

Solution 1Recommended

Correctly Construct the PDF Output Path in VBA

Combine ActiveWorkbook.Path with the file name and use ExportAsFixedFormat to ensure the PDF is saved in the correct directory.

When a workbook is saved, ActiveWorkbook.Path returns the folder location without a trailing backslash. If you pass only the path without properly concatenating the filename, Excel cannot save it correctly and will revert to the default save location. You must explicitly construct the full path string before passing it to the Export function.

Check if the workbook is saved

Add a validation step in your VBA code: `If ActiveWorkbook.Path = "" Then MsgBox "Please save the workbook first." Exit Sub End If`. This prevents errors with newly created files.

Build the path string

Declare a string variable and construct the path using the path separator: `Dim pdfPath As String` then `pdfPath = ActiveWorkbook.Path & "\" & "OutputName.pdf"`. You can also dynamically use the workbook's name by replacing the extension.

Execute the Export method

Use the `ExportAsFixedFormat` method on the ActiveSheet or ActiveWorkbook, setting the `Type` to `xlTypePDF` and the `Filename` to your generated `pdfPath` variable.

Correctly Construct the PDF Output Path in VBA

VBA Code Example: ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveWorkbook.Path & Application.PathSeparator & "MyFile.pdf"

Powerful Spreadsheet Editor

Easily Run Macros and Export to PDF with WPS Office

WPS Office provides excellent support for VBA macros, allowing you to seamlessly automate tasks like saving worksheets as PDFs. It is fully compatible with Excel files, macros, and standard VBA syntax.

1. Open your macro workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the code.
2. Access the VBA Editor: Navigate to the 'Tools' tab and click on 'Macros', or simply press Alt+F11 to open the VBA development environment.
3. Run your PDF export code: Paste your corrected ExportAsFixedFormat VBA code and click 'Run'. WPS will seamlessly generate the PDF in the target folder.

Fully compatible with Microsoft Excel (.xlsx, .xlsm)Built-in VBA editor to write and execute macros easilyNative support for high-quality PDF exporting without pluginsLightweight, fast, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

Why does ActiveWorkbook.Path return an empty string?

If the current workbook has just been created and never saved to a disk, it does not have a physical location on the hard drive. Therefore, ActiveWorkbook.Path will return an empty string until you perform a 'Save As' operation.

Can I save a specific range as a PDF instead of the whole sheet?

Yes. Instead of applying ExportAsFixedFormat to the ActiveSheet, you can apply it directly to a specific Range object. For example: Range("A1:D10").ExportAsFixedFormat Type:=xlTypePDF.

How do I handle path separator differences between Mac and Windows in VBA?

Instead of hardcoding a backslash (\) in your string concatenation, use Application.PathSeparator. This ensures the macro creates valid file paths automatically regardless of whether it is running on a Windows or macOS operating system.

Excel & Data Problems