Excel Macro To Save Sheets As PDF - Contextures
Có thể bạn quan tâm
- Topics
- Charts
- Data Entry
- Data Validation
- Filters
- Formatting
- Formulas
- Macros
- Pivot Tables
- Sample Data
- Sample Files
- Tutorial Index
Home > Macros > Basics > PDF Excel VBA - Save As PDF FilesIn Excel 2010 and later, you can export a sheet, or a group of sheets, as a single PDF file. See how to manually export an Excel worksheet to PDF format, with the settings you need. Or, use the sample Excel VBA macros that automate the PDF conversion steps for you. |
Export Sheet as PDF - Manual Steps
Export to PDF - 3 Macros
How to Use the Macros
Macro 1 - Export Sheet as PDF
--- How the Macro Works
Macro 2 - No Prompt
Macro 3 - File Check
Modify Export to PDF Macros
Error - Could not create PDF
Get the Sample File
More Tutorials
Export Sheet as PDF - Manual StepsTo manually export the active worksheet as a PDF file, follow these steps:
Publish as PDF or XPSThe Publish as PDF or XPS window opens, where you can enter the details for the PDF file.
|
PDF Settings & Options
|
Export to PDF - 3 MacrosThere are 3 Export to PDF macros in the sections below, and the next section shows the steps for using any of these pdf converter macros, in your own Excel file.
Page Layout and Print SettingsWhen you create PDF files, manually or with a macro, the PDF document will use the page layout settings, and print settings, from the sheets that you convert to PDF.
See the page setup tips and setting information on the Excel Printing Tips and Fixes page. |
How to Use the MacrosTo use the Export to PDF macros in your own Excel spreadsheets, follow the steps below. 1) Copy Code to Excel FileFirst, copy a macro's code from this page, and paste it into a regular code module in your Excel workbook.
This short video shows the steps, and there are written steps on the Copy Macro Code page. 2) Modify Macro Code (Optional)This step is optional - you can run the code as it is, without making any changes. However, you might want to change some of the PDF export settings, to get exactly what you need in the exported file. For notes on how to modify the code, go to the Modify Export to PDF Macros section. |
3) Run the MacroWhen you're ready to run a macro, follow these steps:
Warning: If you have a two or more sheets selected, remember to ungroup them, before doing any more work in your Excel file. |
Macro 1 - Export Sheet as PDF FileThis macro code exports the active sheet (or selected sheets) in PDF format.
Export As PDF Macro CodeSee the next section, for details on how this pdf conversion macro works. |
How The Macro WorksBefore you run the macro in your spreadsheet, select the sheet(s) that you want to export to the PDF file. Active Sheet and WorkbookWhen the macro starts, it sets variables for the active sheet, and the active workbook. Those will be used to set the default file name and folder. Set wbA = ActiveWorkbook Set wsA = ActiveSheetDate/Time StampA time stamp will be added to the default name, in the format yyyymmdd_hhmm. In the format string shown below, a backslash is entered before the underscore, to indicate it is a literal character. Otherwise, Excel would interpret the underscore as the spacing character that is used in Excel number formatting. Set wbA = ActiveWorkbook Set wsA = ActiveSheet strTime = Format(Now(), "yyyymmdd\_hhmm")File PathNext, the macro gets the default path for saving the PDF document. If the active workbook has been saved, its path is used. If the active workbook has not been saved, Excel's default save folder is used. strPath = wbA.Path If strPath = "" Then strPath = Application.DefaultFilePath End If strPath = strPath & "\"Sheet NameThe name of the active sheet is cleaned up -- spaces are removed, and periods are replaced with underscores. 'replace spaces and periods in sheet name strName = Replace(wsA.Name, " ", "") strName = Replace(strName, ".", "_") |
File PathThe file path, revised sheet name, and the ".pdf" extension are combined. 'create default name for savng file strFile = strName & "_" & strTime & ".pdf" strPathFile = strPath & strFileFolder LocationThe Save As dialog box opens, with the current folder selected, or the default save folder. The folder is filtered, to show only the PDF files that it contains. At the top of the Save As window, the customized title is shown, "Select Folder and FileName to save" myFile = Application.GetSaveAsFilename _ (InitialFileName:=strPathFile, _ FileFilter:="PDF Files (*.pdf), *.pdf", _ Title:="Select Folder and FileName to save")Default File NameThe default file name is filled in, and you can overwrite it, to save the file with a different name. You can also select another folder --just browse to a different location. |
Save ButtonThen, click the Save button, or click Cancel, if you change your mind.
Confirmation MessageThen, if the file was created, the macro shows a confirmation message with the file path and name. MsgBox "PDF file has been created: " _ & vbCrLf _ & myFileClick the OK button to close the message box. |
Macro 2 - No PromptThe previous macro creates a default name with a time stamp, based on the active sheet name. It prompts you to select a folder for the saved PDF file, and you can change the default name, if you prefer something different. In the macro below, the default name is based on the values in cells A1, A2 and A3 on the active sheet. The PDF file is automatically saved in the current folder
|
Macro 3 - No Prompt - File CheckIn the macro below, the default name is based on the values in cells A1, A2 and A3 on the active sheet. The PDF file is automatically saved in the current folder, with no prompts. However, if a file with that name already exists in the current folder, a message asks if you want to overwrite the file. Click Yes or No in the message box.
NOTE: Be sure to copy the bFileExists Function too, below the main macro |
Modify Export to PDF MacrosIn the macros on this page, the code has settings for PDF files that are created from the Microsoft Excel spreadsheets. You might prefer different settings. The best way to see how to change the code, so that it uses your preferences, is to record an Excel macro, while you manually export an Excel sheet in PDF format. To do that, follow the steps below. Record a PDF Settings MacroFirst, follow these steps to start recording a macro:
|
Export as PDFNext, while the recorder is running, export the active sheet to PDF, and choose your preferred settings.
After you click the Publish button, go to the Excel Status bar, and click the Stop Recording button. |
Check Recorded Macro CodeTo see the PDF code that was recorded, follow these steps:
In the recorded macro code, you can see the export settings and options that you selected.
|
Modify the Sample CodeIn the Macros on this page, you can replace the sample code with your preferred settings. Just copy and paste from your recorded macro code.
For example, for the Quality setting, you could change:
|
Error - Could not create PDFIf you run these macros in Excel for Office 365, you might see an error:
Or, if you try to manually export a PDF file in Excel for Office 365, you could see this error:
In one of the Excel forums on the Microsoft website, someone posted the following solution to the problem.
'==================== Quoted from Microsoft Excel Forum: After hours of studying Process Monitor reports on two different computers and comparing them, I discovered that Microsoft Office apps look for "sRGB Color Space Profile.icm" in the wrong place and don't find it. By deleting the following Registry values, the problem goes away:
For the record, these values are all okay. There is nothing wrong with them. (The Color Management applet in the Windows Control Panel creates them.) The blame is entirely on Microsoft Office, i.e. this is a bug. '==================== |
Get the Sample FileTo see how the macro works, you can download the Export Excel Sheet as PDF sample file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros, if you want to run the macro. Get Monthly Excel Tips!Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe. Next, when you get my reply, click the Confirm button. I add this step to protect you from spam! More TutorialsEmail from Excel with PDF Copy Macro Code to a workbook Excel VBA Edit Your Recorded Macro Excel VBA Getting Started |
Email from Excel with PDF
Last updated: June 10, 2024 11:59 AM
Từ khóa » Visual Basic Excel Save As Pdf
-
Save Excel As PDF With VBA
-
Excel VBA Save As PDF: Step-By-Step Guide And 10 Code ...
-
Excel VBA Save As (Print) To PDF
-
Excel Macro To Save As PDF (5 Suitable Examples) - ExcelDemy
-
Save File As PDF In VBA (macro)? | MrExcel Message Board
-
VBA Save Sheet As PDF - YouTube
-
Save Excel File As PDF Using VBA (Entire Workbook Or ... - YouTube
-
Save Excel As PDF With Excel VBA
-
Convert Excel To PDF Using VBA - The Only Guide You Will Need
-
How To Use Command Button To Save Active Worksheet As PDF File In ...
-
Export Docx To Pdf With Vba - Excel - Stack Overflow
-
Excel VBA SaveAs "Read-only" PDF File - Stack Overflow
-
Workbook.ExportAsFixedFormat Method (Excel) - Microsoft Docs
-
Excel VBA Save As PDF: Step-By-Step Guide And 10 ... - Pinterest