Excel Macro to Save Sheets As PDF - Contextures

Excel macro saves active sheet or sheets in PDF format, ... You can also select another folder --just browse to a different location. Save ... Togglenavigation Home Tips Files Data Skills ExcelVBA-SaveAsPDFFiles InExcel2010andlater,youcanexportasheet,oragroupofsheets, asaPDFfile.Thistutorialshowssamplecodefordoingthis. ExporttheActiveSheetasPDFFile HowtheMacroWorks Macro2-NoPrompt Macro3-FileCheck Error-CouldnotcreatePDF CopyCodetoWorkbook GettheSampleFile MoreTutorials ExportActiveSheetasPDFFile Thefollowingmacrocodewillexporttheactivesheet(orsheets)inPDFformat.Copythecodetoaregularcodemodule,thenselectthesheet(s)youwanttoexport,andrunthemacro. Seethesectionfurtherdown,fordetailsonhowthemacroworks. NOTE:Therearetwoadditionalmacrosbelow: CreatePDFfilewithnamefromworksheetcells-automaticallyoverwritesexistingfile,ifany CreatePDFfilewithnamefromworksheetcells-checkforexistingfile,prompttooverwriteorchoosedifferentname TheExportAsPDFCode CopythefollowingVBAcodetoaregularcodemodule,thenselectthesheet(s)youwanttoexport,andrunthemacro. SubPDFActiveSheet() 'www.contextures.com 'forExcel2010andlater DimwsAAsWorksheet DimwbAAsWorkbook DimstrTimeAsString DimstrNameAsString DimstrPathAsString DimstrFileAsString DimstrPathFileAsString DimmyFileAsVariant OnErrorGoToerrHandler SetwbA=ActiveWorkbook SetwsA=ActiveSheet strTime=Format(Now(),"yyyymmdd\_hhmm") 'getactiveworkbookfolder,ifsaved strPath=wbA.Path IfstrPath=""Then strPath=Application.DefaultFilePath EndIf strPath=strPath&"\" 'replacespacesandperiodsinsheetname strName=Replace(wsA.Name,"","") strName=Replace(strName,".","_") 'createdefaultnameforsavngfile strFile=strName&"_"&strTime&".pdf" strPathFile=strPath&strFile 'usecanenternameand 'selectfolderforfile myFile=Application.GetSaveAsFilename_ (InitialFileName:=strPathFile,_ FileFilter:="PDFFiles(*.pdf),*.pdf",_ Title:="SelectFolderandFileNametosave") 'exporttoPDFifafolderwasselected IfmyFile<>"False"Then wsA.ExportAsFixedFormat_ Type:=xlTypePDF,_ Filename:=myFile,_ Quality:=xlQualityStandard,_ IncludeDocProperties:=True,_ IgnorePrintAreas:=False,_ OpenAfterPublish:=False 'confirmationmessagewithfileinfo MsgBox"PDFfilehasbeencreated:"_ &vbCrLf_ &myFile EndIf exitHandler: ExitSub errHandler: MsgBox"CouldnotcreatePDFfile" ResumeexitHandler EndSub HowTheMacroWorks Beforeyourunthemacro,selectthesheet(s)thatyouwanttoexporttothePDFfile. Whenthemacrostarts,itsetsvariablesfortheactivesheet,andtheactiveworkbook.Thosewillbeusedtosetthedefaultfilenameandfolder. SetwbA=ActiveWorkbook SetwsA=ActiveSheet Atimestampwillbeaddedtothedefaultname,intheformatyyyymmdd_hhmm. Intheformatstringshownbelow,abackslashisenteredbeforetheunderscore,toindicateitisaliteralcharacter.Otherwise,ExcelwouldinterprettheunderscoreasthespacingcharacterthatisusedinExcelnumberformatting. SetwbA=ActiveWorkbook SetwsA=ActiveSheet strTime=Format(Now(),"yyyymmdd\_hhmm") Next,themacrogetsthedefaultpathforsavingthePDFfile.Iftheactiveworkbookhasbeensaved,itspathisused.Iftheactiveworkbookhasnotbeensaved,Excel'sdefaultsavefolderisused. strPath=wbA.Path IfstrPath=""Then strPath=Application.DefaultFilePath EndIf strPath=strPath&"\" Thenameoftheactivesheetiscleanedup--spacesareremoved,andperiodsarereplacedwithunderscores. 'replacespacesandperiodsinsheetname strName=Replace(wsA.Name,"","") strName=Replace(strName,".","_") Thefilepath,revisedsheetname,andthe".pdf"extensionarecombined. 'createdefaultnameforsavngfile strFile=strName&"_"&strTime&".pdf" strPathFile=strPath&strFile TheSaveAsdialogboxopens,withthecurrentfolderselected,orthedefaultsavefolder.Thefolderisfiltered,toshowonlythePDFfilesthatitcontains. AtthetopoftheSaveAswindow,thecustomizedtitleisshown,"SelectFolderandFileNametosave" myFile=Application.GetSaveAsFilename_ (InitialFileName:=strPathFile,_ FileFilter:="PDFFiles(*.pdf),*.pdf",_ Title:="SelectFolderandFileNametosave") Thedefaultfilenameisfilledin,andyoucanoverwriteit,tosavethefilewithadifferentname.Youcanalsoselectanotherfolder--justbrowsetoadifferentlocation. Then,clicktheSavebutton,orclickCancel,ifyouchangeyourmind. IfyouclickCancel,thevalueofmyFileis"False",andnothingmorehappens--themacroends. IfyouclickSave,thePDFfileiscreated. IfmyFile<>"False"Then wsA.ExportAsFixedFormat_ Type:=xlTypePDF,_ Filename:=myFile,_ Quality:=xlQualityStandard,_ IncludeDocProperties:=True,_ IgnorePrintAreas:=False,_ OpenAfterPublish:=False Then,ifthefilewascreated,themacroshowsaconfirmationmessagewiththefilepathandname. MsgBox"PDFfilehasbeencreated:"_ &vbCrLf_ &myFile ClicktheOKbuttontoclosethemessagebox. Macro2-NoPrompt Thepreviousmacrocreatesadefaultnamewithatimestamp,basedontheactivesheetname.ItpromptsyoutoselectafolderforthesavedPDFfile,andyoucanchangethedefaultname,ifyouprefersomethingdifferent. Inthemacrobelow,thedefaultnameisbasedonthevaluesincellsA1,A2andA3ontheactivesheet.ThePDFfileisautomaticallysavedinthecurrentfolder--youarenotpromptedtochooseafolder,andcannotchangethedefaultname. SubPDFActiveSheetNoPrompt() 'www.contextures.com 'forExcel2010andlater DimwsAAsWorksheet DimwbAAsWorkbook DimstrNameAsString DimstrPathAsString DimstrFileAsString DimstrPathFileAsString DimmyFileAsVariant OnErrorGoToerrHandler SetwbA=ActiveWorkbook SetwsA=ActiveSheet 'getactiveworkbookfolder,ifsaved strPath=wbA.Path IfstrPath=""Then strPath=Application.DefaultFilePath EndIf strPath=strPath&"\" strName=wsA.Range("A1").Value_ &"-"&wsA.Range("A2").Value_ &"-"&wsA.Range("A3").Value 'createdefaultnameforsavngfile strFile=strName&".pdf" strPathFile=strPath&strFile 'exporttoPDFincurrentfolder wsA.ExportAsFixedFormat_ Type:=xlTypePDF,_ Filename:=strPathFile,_ Quality:=xlQualityStandard,_ IncludeDocProperties:=True,_ IgnorePrintAreas:=False,_ OpenAfterPublish:=False 'confirmationmessagewithfileinfo MsgBox"PDFfilehasbeencreated:"_ &vbCrLf_ &strPathFile exitHandler: ExitSub errHandler: MsgBox"CouldnotcreatePDFfile" ResumeexitHandler EndSub Macro3-NoPrompt-FileCheck Inthemacrobelow,thedefaultnameisbasedonthevaluesincellsA1,A2andA3ontheactivesheet.ThePDFfileisautomaticallysavedinthecurrentfolder,withnoprompts. However,ifafilewiththatnamealreadyexistsinthecurrentfolder,amessageasksifyouwanttooverwritethefile.ClickYesorNointhemessagebox. Yes-thenewfileoverwritestheoldfile No-youarepromptedtochooseafolder,and/orenteradifferentfilename. NOTE:BesuretocopythebFileExistsFunctiontoo,belowthemainmacro SubPDFActiveSheetNoPromptCheck() 'www.contextures.com 'forExcel2010andlater 'checksforexistingfile 'prompttooverwriteorrename 'usesbFileExistsFunction,below DimwsAAsWorksheet DimwbAAsWorkbook DimstrNameAsString DimstrPathAsString DimstrFileAsString DimstrPathFileAsString DimmyFileAsVariant DimlOverAsLong OnErrorGoToerrHandler SetwbA=ActiveWorkbook SetwsA=ActiveSheet 'getactiveworkbookfolder,ifsaved strPath=wbA.Path IfstrPath=""Then strPath=Application.DefaultFilePath EndIf strPath=strPath&"\" strName=wsA.Range("A1").Value_ &"-"&wsA.Range("A2").Value_ &"-"&wsA.Range("A3").Value 'createdefaultnameforsavngfile strFile=strName&".pdf" strPathFile=strPath&strFile IfbFileExists(strPathFile)Then lOver=MsgBox("Overwriteexistingfile?",_ vbQuestion+vbYesNo,"FileExists") IflOver<>vbYesThen 'usercanenternameand 'selectfolderforfile myFile=Application.GetSaveAsFilename_ (InitialFileName:=strPathFile,_ FileFilter:="PDFFiles(*.pdf),*.pdf",_ Title:="SelectFolderandFileNametosave") IfmyFile<>"False"Then strPathFile=myFile Else GoToexitHandler EndIf EndIf EndIf 'exporttoPDFincurrentfolder wsA.ExportAsFixedFormat_ Type:=xlTypePDF,_ Filename:=strPathFile,_ Quality:=xlQualityStandard,_ IncludeDocProperties:=True,_ IgnorePrintAreas:=False,_ OpenAfterPublish:=False 'confirmationmessagewithfileinfo MsgBox"PDFfilehasbeencreated:"_ &vbCrLf_ &strPathFile exitHandler: ExitSub errHandler: MsgBox"CouldnotcreatePDFfile" ResumeexitHandler EndSub '============================= FunctionbFileExists(rsFullPathAsString)AsBoolean bFileExists=CBool(Len(Dir$(rsFullPath))>0) EndFunction '============================= Error-CouldnotcreatePDF IfyourunthesemacrosinExcelforOffice365,youmightseeanerror: CouldnotcreatePDF... Or,ifyoutrytomanuallyexportaPDFfileinExcelforOffice365,youcouldseethiserror: Documentnotsaved.Thedocumentmaybeopen,oranerrormayhavebeenencounteredwhensaving InoneoftheExcelforumsontheMicrosoftwebsite,someonepostedthefollowingsolutiontotheproblem. Asalways,makeabackupoftheRegistry,beforemakinganychangestoit,andtrythisatyourownrisk! '==================== QuotedfromMicrosoftExcelForum: AfterhoursofstudyingProcessMonitorreportsontwodifferentcomputersandcomparingthem,IdiscoveredthatMicrosoftOfficeappslookfor"sRGBColorSpaceProfile.icm"inthewrongplaceanddon'tfindit.BydeletingthefollowingRegistryvalues,theproblemgoesaway: Path:HKEY_CURRENT_USER\Software\Microsoft\WindowsNT\CurrentVersion\ICM\RegisteredProfiles Valuename:sRGB Path:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WindowsNT\CurrentVersion\ICM\RegisteredProfiles Valuename:sRGB Fortherecord,thesevaluesareallokay.Thereisnothingwrongwiththem.(TheColorManagementappletintheWindowsControlPanelcreatesthem.)TheblameisentirelyonMicrosoftOffice,i.e.thisisabug. '==================== HowtoCopyMacroCodetoRegularModule Thisshortvideoshowsthestepsforcopyingmacrocodetoaregularcodemodule.Forwrittensteps,gototheCopyMacroCodepage. GettheSampleFile Toseehowthemacroworks,youcandownloadtheExportExcelSheetasPDFsamplefile.Thezippedfileisinxlsmformat,andcontainsmacros.Besuretoenablemacros,ifyouwanttorunthemacro. MoreTutorials EmailfromExcelwithPDF CopyMacroCodetoaworkbook Excel VBAEditYourRecordedMacro Excel VBAGettingStarted     CopyMacroCodetoaworkbook EmailfromExcelwithPDF     Lastupdated:December9,20211:14PM