How to Export Excel Worksheet as PDF to Teams or SharePoint via VBA
Question details
The user wants to export an Excel worksheet as a dated PDF and save it directly to a Microsoft Teams or SharePoint folder using VBA.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the process of generating a dated PDF report from a worksheet and directly saving it to a shared team folder without sending it as an Outlook email attachment.
- Observed behavior
- Saving files directly to a SharePoint or Teams HTTPS URL using the VBA ExportAsFixedFormat method frequently fails or causes runtime errors.
Ensure that your target Microsoft Teams or SharePoint document library is synchronized to your local hard drive via OneDrive so that a local directory path is available.
Update VBA Macro to Save PDF via a Local Synchronized Folder
Modify your VBA script to bypass the HTTPS URL and save the exported PDF directly to the locally synced folder path.
When attempting to save files to SharePoint or Teams directly through VBA, using an HTTPS URL often results in a path error. To resolve this, you must sync the target SharePoint or Teams folder to your local machine using OneDrive. Once synced, you can use the local file path (or a UNC network path) in your VBA script to successfully export the PDF.
Open File Explorer, navigate to the SharePoint or Teams folder that you have synchronized to your PC, click the address bar, and copy the exact local directory path.
In your Excel workbook, press ALT + F11 to launch the Visual Basic for Applications (VBA) editor. Open the module containing your export macro.
Declare a string variable for your file path. Combine the copied local folder path with the workbook's base filename and append the current date using the Format function (e.g., Format(Date, "yyyy-mm-dd")).
Use the ActiveSheet.ExportAsFixedFormat method, setting Type:=xlTypePDF and Filename:=YourFilePath, to automatically generate and save the PDF into your synchronized folder.
Easily Export Spreadsheets to PDF with WPS Office
WPS Spreadsheet features robust built-in PDF conversion tools and excellent VBA/Macro support, allowing you to easily automate tasks and save reports directly to any local or synced cloud folders.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file you wish to export.
- 2. Select Export to PDF: Navigate to the 'Menu' or 'Tools' tab on the ribbon and click on 'Export to PDF'.
- 3. Save to Your Synced Folder: Choose your locally synchronized SharePoint or Teams folder as the destination directory, set your options, and click 'Export'.
Frequently Asked Questions
Why do I get a runtime error when saving a PDF to a SharePoint URL in VBA?
The VBA ExportAsFixedFormat method does not natively support writing directly to an https:// web path in many Windows environments. You must synchronize the SharePoint document library to your computer using OneDrive and target the resulting local file path instead.
How do I append the current date to my PDF filename using VBA?
You can use the built-in VBA Format function. Combine your base filename string with the current date formatted to your preference, such as: BaseName & "_" & Format(Date, "yyyy-mm-dd") & ".pdf".
Can I run this VBA macro on an unsaved workbook?
If your macro relies on properties like ThisWorkbook.Name or ThisWorkbook.Path to determine the PDF's save location or filename, you must save the Excel file at least once first. Otherwise, the macro may return blank values or default temporary names.
