Fix an Excel VBA Macro That Cannot Save a Range as PDF
Question details
The user needs to restore an Excel VBA macro that exports a worksheet range or worksheet to a PDF file.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A macro uses ExportAsFixedFormat to create a PDF in a specified folder, potentially including dynamically created year and month subfolders.
- Observed behavior
- The VBA PDF export no longer saves successfully, which may result from an invalid filename, unavailable destination folder, access restrictions, or invalid worksheet or range references.
Confirm that the destination drive or shared folder is connected and that you can manually create a file in it. Check the worksheet names, source range, and date value used to build the PDF filename before running the macro.
Validate the PDF filename and destination folder before exporting
Use this method when the macro fails after the output path, network drive, folder structure, or filename format has changed.
ExportAsFixedFormat requires a complete valid filename, including the .pdf extension, and the target folder must already exist and allow write access. Build each folder level carefully, especially when the path is on a mapped or shared drive.
In the VBA editor, press Alt+F11 and locate the line that assigns strPath. Verify that the drive letter and folder, such as "Z:\Production\2024\", exist and are available in File Explorer.
Before calling ExportAsFixedFormat, test strYearFolder and strMonthFolder with Dir(folderPath, vbDirectory). If either is missing, create it with MkDir only after confirming its parent folder exists.
Set strFileName to a full path ending in .pdf, for example: strFileName = strMonthFolder & Format(ws.Range("E5").Value, "mm-dd-yyyy") & " MD TEMP TREND MONITORING.pdf". Do not include invalid filename characters such as /, \, :, *, ?, ", <, >, or | in cell-based text.
Use rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False. Display strFileName in a MsgBox temporarily to confirm the final location.
Confirm the worksheet, range, and workbook can be exported
Use this method when the file path is valid but the macro still errors or exports an empty or unexpected PDF.
Add detailed VBA error handling to identify the failed operation
Use this method when the macro shows only a general error message and you need the actual VBA error number and description.
Export a Spreadsheet Range or Worksheet to PDF with WPS Office
WPS Spreadsheet provides PDF export tools and a familiar spreadsheet workspace for reviewing the target range, print settings, and output location before creating a PDF. You can also use its macro tools where supported to maintain compatible spreadsheet automation workflows.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the data to export.
- 2. Select and prepare the output area: Select the required cells, then use Page Layout options to set the print area, orientation, margins, and scaling for the PDF.
- 3. Preview the printable result: Open File > Print to review page breaks and confirm that the selected range fits correctly on the page.
- 4. Export the workbook or selection to PDF: Choose Menu > Export to PDF, select the output scope and destination folder, then click Export to create the PDF file.
Frequently Asked Questions
Why does ExportAsFixedFormat fail even though the VBA code worked before?
The destination drive may be disconnected, the folder may have been renamed or removed, permissions may have changed, or a cell value may now create an invalid filename. Check the final strFileName value and verify that its folder exists.
Can ExportAsFixedFormat create missing folders automatically?
No. ExportAsFixedFormat saves only to an existing accessible folder. Use Dir to check each folder and MkDir to create missing folders before calling the export method.
Should I use Range.ExportAsFixedFormat or Worksheet.ExportAsFixedFormat?
Use Range.ExportAsFixedFormat when only a defined cell range should become a PDF. Use Worksheet.ExportAsFixedFormat when you need the worksheet's configured print area or the entire printable sheet.
How can I see the exact VBA error during PDF export?
Add an ErrorHandler label and display Err.Number, Err.Description, and strFileName in a MsgBox. You can also press F8 in the VBA editor to step through the code and inspect path and range variables.
Why does the exported PDF contain the wrong pages or blank pages?
The worksheet print area, page breaks, scaling, or the IgnorePrintAreas argument may control the output. Review Page Layout > Print Area in Excel, then either correct the print area or set IgnorePrintAreas:=True.
