Create a Folder and Export an Excel Worksheet to PDF with VBA
Question details
The user needs one VBA process that creates a folder if necessary and exports an Excel worksheet as a PDF in that folder.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automate folder creation and worksheet PDF export from a single macro.
- Observed behavior
- The desired result is a PDF saved in the specified folder, whether the folder already exists or must be created.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) in a local folder or accessible network location, and enable macros if prompted. Select the worksheet you want to export and make sure you have permission to create files in the workbook's folder.
Use one VBA macro to create the folder and export the worksheet
Run this macro when you want to save the selected worksheet as a PDF in a subfolder beside the workbook.
The macro checks for a single-level subfolder named PDF Exports, creates it if needed, and saves the workbook's active worksheet as Worksheet.pdf. Change the folder and file names in the code if needed.
In Excel, open the saved workbook, select the worksheet to export, and press Alt+F11. In the VBA editor, select Insert > Module.
Paste this code into the module: Option Explicit Sub ExportWorksheetToPDF() Dim folderPath As String Dim filePath As String Dim ws As Worksheet On Error GoTo ErrorHandler If ThisWorkbook.Path = "" Then MsgBox "Save the workbook before running this macro.", vbExclamation Exit Sub End If Set ws = ThisWorkbook.ActiveSheet folderPath = ThisWorkbook.Path & Application.PathSeparator & "PDF Exports" filePath = folderPath & Application.PathSeparator & "Worksheet.pdf" EnsureFolderExists folderPath ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, _ Quality:=xlQualityStandard, IgnorePrintAreas:=False, OpenAfterPublish:=False MsgBox "PDF exported to: " & filePath, vbInformation Exit Sub ErrorHandler: MsgBox "Could not create the folder or export the PDF: " & Err.Description, vbExclamation End Sub Private Sub EnsureFolderExists(ByVal folderPath As String) If Dir(folderPath, vbDirectory Or vbHidden Or vbSystem) = "" Then MkDir folderPath ElseIf (GetAttr(folderPath) And vbDirectory) = 0 Then Err.Raise vbObjectError + 1000, , "A file already exists at the folder path." End If End Sub
Return to Excel, select the worksheet, then open Developer > Macros, select ExportWorksheetToPDF, and click Run. Find Worksheet.pdf in the PDF Exports folder beside the workbook.

Export spreadsheets to PDF with WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets and exporting them to PDF. Its Excel format support helps you move existing files into WPS, though it does not fix or guarantee compatibility with Excel VBA macros.

Frequently Asked Questions
How can I export a different worksheet?
Select that worksheet in the workbook before running Developer > Macros > ExportWorksheetToPDF. The macro exports the workbook's active worksheet.
What happens if the export folder already exists?
The helper checks the PDF Exports path and uses the existing folder. If a file occupies that path instead, the macro reports an error.
Can I run the macro from a button?
Yes. In Excel, use Developer > Insert to add a button, then assign ExportWorksheetToPDF when prompted.
Why does the PDF contain only part of my worksheet?
The macro respects a defined print area. In Excel, check Page Layout > Print Area and clear or change it before running the macro if you want a different range.




