Save Excel as PDF with VBA

The VBA code below saves a specified chart as a PDF. ... worksheets and save as individual PDF in same folder 'as the Excel file For Each ws ... SkiptocontentGetourFREEVBAeBookofthe30mostusefulExcelVBAmacros.AutomateExcelsothatyoucansavetimeandstopdoingthejobsatrainedmonkeycoulddo.ClaimyourfreeeBookSinceExcel2010,ithasbeenpossibletosaveExcelfilesasPDFdocuments. PDFwasthenandcontinuestobe,acommonfileformatfordistributingdocuments.ThecodeexamplesbelowprovidetheVBAoptionstoautomatethecreationofPDFsfromExcel. Theycanbeusedontheirownoraspartoflargerprocesses.RatherthangoingfromExceltoPDF,youmightwanttogotheotherway;fromPDFtoExcel. Checkoutthispostforpossiblesolutionsforthat:GetdatafromPDFintoExcel.DownloadtheexamplefileIrecommendyoudownloadtheexamplefileforthispost. Thenyou’llbeabletoworkalongwithexamplesandseethesolutioninaction,plusthefilewillbeusefulforfuturereference.Downloadthefile:0019SaveExcelasPDFwithVBA.zipContentsSavingExcelworkbooks,sheets,chartsandrangesasPDFSaveactivesheetasPDFSaveactiveworkbookasPDFSaveselectionasPDFSavearangeasPDFSaveachartasPDFNotesforsavingPDFdocumentsSelectingspecificworksheetsbeforesavingasPDFLoopingandsavingasseparatePDFsLoopthroughsheetsLoopthroughselectedsheetsLoopthroughchartsOtherPDFexportoptionsExampleusingalltheoptionsOtherfixedformatsavailableConclusionSavingExcelworkbooks,sheets,chartsandrangesasPDFThissectioncontainsthebasecodetosaveExcelasPDFfromdifferentobjects(workbooks,worksheets,rangesandcharts). FromaVBAperspective,itistheExportAsFilxedFormatmethodcombinedwiththeTypepropertysettoxlTypePDFthatcreatesaPDF.SaveactivesheetasPDFThefollowingcodesavestheselectedsheetsasasinglePDF.SubSaveActiveSheetsAsPDF() 'Createandassignvariables DimsaveLocationAsString saveLocation="C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" 'SaveActiveSheet(s)asPDF ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,_ Filename:=saveLocation EndSubSaveactiveworkbookasPDFTosaveallthesheetsfromaworkbookusethefollowingmacro.SubSaveActiveWorkbookAsPDF() 'Createandassignvariables DimsaveLocationAsString saveLocation="C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" 'SaveactiveworkbookasPDF ActiveWorkbook.ExportAsFixedFormatType:=xlTypePDF,_ Filename:=saveLocation EndSubSaveselectionasPDFSometimes,weonlywanttosaveasmallpartofaworksheettoaPDF. Thefollowingcodesavesonlytheselectedcells.SubSaveSelectionAsPDF() 'Createandassignvariables DimsaveLocationAsString saveLocation="C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" 'SaveselectionasPDF Selection.ExportAsFixedFormatType:=xlTypePDF,_ Filename:=saveLocation EndSubSavearangeasPDFThemacrobelowsavesaspecifiedrangeasaPDF.SubSaveRangeAsPDF() 'Createandassignvariables DimsaveLocationAsString DimrngAsRange saveLocation="C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" Setrng=Sheets("Sheet1").Range("A1:H20") 'SavearangeasPDF rng.ExportAsFixedFormatType:=xlTypePDF,_ Filename:=saveLocation EndSubSaveachartasPDFTheVBAcodebelowsavesaspecifiedchartasaPDF.SubSaveChartAsPDF() 'Createandassignvariables DimsaveLocationAsString DimchtAsChart saveLocation="C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" Setcht=Sheets("Sheet3").ChartObjects("Chart1").Chart 'Alternativeusingtheactivechart 'Setcht=ActiveChart 'SaveachartasPDF cht.ExportAsFixedFormatType:=xlTypePDF,_ Filename:=saveLocation EndSubNotesforsavingPDFdocumentsWhiletheFilenamepropertyisoptional,itisimportantifwewanttoknowwherethefilehasbeensavedandwhatit’scalled.IftheFilenamepropertyisnotprovided,thePDFsavesinyourdefaultfolderlocationusingtheExcelworkbook’snamewiththe.pdffileextension.Whereafilenameisprovided,butnotafilepath,thedocumentsavesinyourdefaultfolderlocationwiththenameprovided.Whenthe.pdffileextensionisnotprovided,thesuffixisaddedautomatically.IfaPDFalreadyexistsinthesavelocationspecified,itwillbeoverwritten. Itmaybenecessarytoincludefilehandlingprocedurestopreventoverwritingexistingdocumentsandhandleerrors.SelectingspecificworksheetsbeforesavingasPDFIfmorethanoneworksheetisactive,thePDFcreatedwillincludealltheactivesheets. ThefollowingcodeselectsmultipleworksheetsfromanarraybeforesavingthePDF.SubSelectSheetsAndSaveAsPDF() 'Createandassignvariables DimsaveLocationAsString DimsheetArrayAsVariant saveLocation="C:\Users\marks\OneDrive\Documents\myPDFFile.pdf" sheetArray=Array("Sheet1","Sheet2") 'Selectspecificsheetsfromworkbook,thesaveallasPDF Sheets(sheetArray).Select ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,_ Filename:=saveLocation EndSubLoopingandsavingasseparatePDFsTosavemultiplePDFsquickly,wecanuseVBAtoloopthroughsheetsorchartsandsaveeach.LoopthroughsheetsThefollowingmacroloopsthrougheachworksheetintheactiveworkbookandsaveseachasitsownPDF.SubLoopSheetsSaveAsPDF() 'Createvariables DimwsAsWorksheet 'LoopthroughallworksheetsandsaveasindividualPDFinsamefolder 'astheExcelfile ForEachwsInActiveWorkbook.Worksheets  ws.ExportAsFixedFormatType:=xlTypePDF,_ Filename:=ThisWorkbook.Path&"/"&ws.Name&".pdf" Next EndSubreportthisadLoopthroughselectedsheetsThefollowingmacroloopsthroughtheselectedworksheetsintheactiveworkbookandsaveseachasitsownPDF.SubLoopSelectedSheetsSaveAsPDF() DimwsAsWorksheet DimsheetArrayAsVariant 'Capturetheselectedsheets SetsheetArray=ActiveWindow.SelectedSheets 'Loopthrougheachselectedworksheet ForEachwsInsheetArray ws.Select ws.ExportAsFixedFormatType:=xlTypePDF,_ Filename:=ThisWorkbook.Path&"/"&ws.Name&".pdf" Nextws 'Reselecttheselectedsheets sheetArray.Select EndSubLoopthroughchartsThefollowingcodeloopsthrougheachchartontheactivesheetandsaveseachasaseparatePDF.SubLoopChartsSaveAsPDF() 'Createandassignvariables DimchtObjAsChartObject DimwsAsWorksheet Setws=ActiveSheet 'LoopthroughallchartsabdsaveasindividualPDFinsamefolder 'astheExcelfile ForEachchtObjInws.ChartObjects chtObj.Chart.ExportAsFixedFormatType:=xlTypePDF,_ Filename:=ThisWorkbook.Path&"/"&chtObj.Name&".pdf" NextchtObj EndSubOtherPDFexportoptionsWhenusingExportAsFixedFormatthereareotheroptionalsettingsavailable:'Openthedocumentafteritissaved-optionsareTrue/ False OpenAfterPublish:=False 'IncludetheExceldocumentpropertiesintothePDF-optionsareTrue/ False IncludeDocProperties:=True 'DoesthecreatedPDFadheretothePrintAreasalreadysetinthe 'worksheet-optionsareTrue/ False IgnorePrintAreas:=False 'Settheoutputqualityofthecreateddocument-optionsare 'xlQualityMinimum/xlQualityStandard Quality:=xlQualityStandard 'Thepagetostartprintingfrom.IfExcluded,willstartfromthefirstpage From:=1 'Thepagetoprintto.IfExcluded,willgotothelastpage To:=2ExampleusingalltheoptionsThecodebelowdemonstrateshowtousealltheoptionswithinasinglemacro. Theseoptionscanbeflexedtomeetyourrequirements.SubSaveAsPDFOptions() DimsaveLocationAsString saveLocation="C:\Users\marks\Documents\myPDFFile.pdf" 'Exampleusingalltheoptions ActiveSheet.ExportAsFixedFormat_ Type:=xlTypePDF,_ Filename:=saveLocation,_ OpenAfterPublish:=False,_ IncludeDocProperties:=True,_ IgnorePrintAreas:=False,_ Quality:=xlQualityStandard,_ From:=1,To:=2 EndSubOtherfixedformatsavailableTheTypepropertycanalsocreateXPSdocumentswhenitissettoxlTypeXPS,ratherthanxlTypePDF. XPSisMicrosoft’sownfixedfileformat,whichissimilartoPDF,butbasedontheXMLlanguage. Itisrarelyusedintherealworld,butisanoptionifrequired.ConclusionLearninghowtosaveExcelasPDFisagoodtimeinvestment. Eachofthesecodesnippetsonthereownarenotthatimpressive. However,whencombinedwiththeautomatede-mailprocessesanddocumentcontrol,theyarearealtimesaver.GetourFREEVBAeBookofthe30mostusefulExcelVBAmacros.AutomateExcelsothatyoucansavetimeandstopdoingthejobsatrainedmonkeycoulddo.ClaimyourfreeeBookDon’tforget:Ifyou’vefoundthispostuseful,orifyouhaveabetterapproach,thenpleaseleaveacommentbelow.Doyouneedhelpadaptingthistoyourneeds?I’mguessingtheexamplesinthispostdidn’texactlymeetyoursituation. WealluseExceldifferently,soit’simpossibletowriteapostthatwillmeeteverybody’sneeds. Bytakingthetimetounderstandthetechniquesandprinciplesinthispost(andelsewhereonthissite)youshouldbeabletoadaptittoyourneeds.But,ifyou’restillstrugglingyoushould:Readotherblogs,orwatchYouTubevideosonthesametopic. Youwillbenefitmuchmorebydiscoveringyourownsolutions.Askthe‘ExcelNinja’inyouroffice. It’samazingwhatthingsotherpeopleknow.AskaquestioninaforumlikeMrExcel,ortheMicrosoftAnswersCommunity. Remember,thepeopleontheseforumsaregenerallygivingtheirtimeforfree. Sotakecaretocraftyourquestion,makesureit’sclearandconcise. Listallthethingsyou’vetried,andprovidescreenshots,codesegmentsandexampleworkbooks.UseExcelRescue,whoaremyconsultancypartner.  TheyhelpbyprovidingsolutionstosmallerExcelproblems.Whatnext?Don’tgoyet,thereisplentymoretolearnonExcelOffTheGrid. Checkoutthelatestposts:GetdataintoPowerQuery–5commondatasourcesIntroductiontoPowerQueryVBARenameFile:5codeexamplesRemoveexcessspacesinPowerQueryCSVtoExcel:PowerAutomate&OfficeScripts7waystoremoveadditionalspacesinExcelUsingSlicerswithdynamicarrayformulasinExcelWeightedAverageinExcelPostnavigationDynamicarraysandVBAuserdefinedfunctions(UDFs) →← InterpolatewithExcel68thoughtson“SaveExcelasPDFwithVBA”HassanJattasays:Thanksforthebrilliantpost.Ifoundittobeveryeducative.ReplyApril14,2020at12:52amExcelOffTheGridsays:Great,I’mpleasedithelped.ReplyApril20,2020at10:01pmMarioPassays:OkayLadiesandGents.WhatversionofExcelVBAareyouusing?Forthelifeofme,Iusetheexactcodesnippetsillustratedandnothingworks.OnlythingIgetisRun-timeerror5:InvalidprocedurecallorargumentwhenexecutingsomethingassimpleasActiveWorkbook.ExportAsFixedFormatType:=xlTypePDF,_Filename:=NameOfFileWhenIchangethecodetoreflecttheMSsuggestedformattoActiveWorkbook.ExportAsFixedFormat(Type:=xlTypePDF,FileName:=FileName)AllIgetisacompileError“expecteda=”oraSyntaxError.WhatamIdoingwrong?Itshouldn’tbethatdifficultforheavenssake!ReplyMarch2,2021at2:03amExcelOffTheGridsays:Ijustre-testedthiscode,anditworks.TheissueislikelytobethatthefolderpathcontainedwithintheFileNamedoesnotexist.ThereforeExcelcan’tsavethefileinthatlocation.ReplyMarch4,2021at7:28amMarioPassays:Justtestedthissimplecode:SubPrintSelectionToPDF()DiminvoiceRngAsRangeDimstrfileAsString‘SettingrangetobeprintedSetinvoiceRng=Range(“A1:L21”)‘settingfilenamewithatimestamp.strfile=“invoice”&“_”&Format(Now(),“yyyymmdd_hhmmss”)&“.pdf”‘settingthefulliqualifiedname.Theresultentpdfwillbesavedwherethemainfileexists.strfile=“C:\TestFolder\”&strfileinvoiceRng.ExportAsFixedFormat_Type:=xlTypePDF,_Filename:=strfile,_Quality:=xlQualityStandard,_IncludeDocProperties:=True,_IgnorePrintAreas:=True,_OpenAfterPublish:=TrueEndSubTheendresultwas:Run-timeerror‘5’:Invalidprocedurecallorargumentstrfilevaluewas“C:\testFolder\Invoice_20210306_171147.pdf”Thefolderexisted.SetInvoiceRng=Range(“A1:L21”)referstoamockInvoicecontent.Steppingintothecode,everythingworksuntiltheinvoiceRng.ExportAsFixedformat…statementI’matalossofwordshere!Thiscodeistoosimpletofailinarun-timeerror.I’mstartingtothinkthathismustbeaVBAversionissue,wheretheychangedthestatementsyntaxorcompilefromExcel2007tocurrent.Orsomethinglikethat.March6,2021at10:25pmExcelOffTheGridsays:ItestedyourcodeanditworksperfectlyfineonExcel365.Ifyou’restillusingExcel2007,thatmightbeyourproblem.IbelievetherewasanadditionalOfficeServicePacktogetthePDFfunctionalityintoExcel2007.Ifyou’reusinganadd-intocreatePDFs,thenyouwillneedtoenableitasanexternalreferenceintheVisualBasicEditor,andthedocumentobjectmodelwillbedifferent.SupportforExcel2007finishedinOctober2017,soyou’llneedtodigaroundinsomeolderblogstotrytofindasolution.March9,2021at8:03pmMariopassays:BTWThePDFpluginisalsoinstalled.TheisversionAdd-in:PDFSuite2014Excel2007pluginPublisher:InteractiveBrandsMaltaLimitedLocation:C:\Programfiles(x86)\PDFSuite2014\addin\ExcelPlugin.dllMarch6,2021at10:47pmMarioPassays:Also,justexecutedthefollowingcodesnippetfromaboveexampleNo.1:SubSaveActiveSheetsAsPDF()‘CreateandassignvariablesDimsaveLocationAsStringsaveLocation=“C:\TestFolder\myPDFFile.pdf”‘SaveActiveSheet(s)asPDFActiveSheet.ExportAsFixedFormatType:=xlTypePDF,_Filename:=saveLocationEndSubC:\TestFolder”exists.Executionresultedin:”Run-timeerror5:Invalidprocedurecallorargument”I’mstartingtothinkthatthisinconsistencyinexecutionofsimplecode,isintentionalonthepartofMS.Theyarepushingoffallusersof‘Purchased’software,bycreatingincompatibilitybetweenolderversionsofs/wandnewerversionsofOSsandvbacompilers.Thoseonthe‘paytoplay’365versionofanythinghaveworkingcompatibility,whilethosethat‘had’workingcodenowexperienceincompatibilities.Allhellbrokelooseonmydevelenvironment,aftermylatestW10update.Ican’texplainitanyotherway.Anyonehaveanyothermoreconsolingthoughts?Theyarewelcomed!March8,2021at3:45pmExcelOffTheGridsays:Idon’tthinkthisisaploybyMicrosofttomakepreviouscodenotwork.ToomanybusinessesarerelyingonVBAforMStotrashit.MicrosoftstillallowsExcel4MacrostobeusedinExcel,whichweresupersededin1995.ButifyouareusingExcel2007,youareusingaversionofExcelwhichIdon’tthinkoriginallysupportedPDFexport.March9,2021at8:10pmMariosPassays:IhadalltheAdd-InsrelatedtoPDFenabled.Thecodestilldidnotwork.Asanticipated,Igotfedupandgotonthe‘PaytoPlay’treadmill.Alllatestversionswereinstalledwithoutfail.NOWEVERYTHINGISWORKING!IguessI’mnotastrustingasmost.Justhadtoaccepttheinevitableandgowiththeflow.WhenIspoketotheMSSupporttechassistingwiththeinstallationofOffice365andexplainedthesituation,wetestedthesamecodeonbothversionsofExcelwhiletechhadcontrolofmycomputer.v2007didnotwork,whilev365,samecodeexactly,worked.ThetechwentontoexplainthatmanychangesweremadetocompilersbetweenversionsofOSandS/W,toomanytotrackwhichchangewouldnotworkonpriorversionsofplatformsandsomefunctionalitywouldhavebeenlost.Thankyouforyourassistancethough.It’sgreattoseethatthereareothersouttherethatcanassistthoseintrouble.Itisgreatlyappreciated.Cheersandstayhealthy.ReplyMarch10,2021at11:13pmErickAsays:Thanks,justwhatIwaslookingfor.CristalclearexplanationReplyApril16,2020at12:52amExcelOffTheGridsays:Thanks,I’mgladIcouldhelp🙂ReplyApril20,2020at9:19pmAllanAndersensays:IBoughtOffice365PersonalthatIexpectedthaVBAstillwasinExcel,butIcannotfind.Ifitnotthere(AsfarIcanSee)Imustofcoursepayfor1yearandthenSTOP.ItthereanotherwaytogetVBAwithExcelinaanotherproduct.Iamveryverydisappointed,becauseitwastheonlyreasonIboughtthisproduct.ReplyApril16,2020at2:20pmExcelOffTheGridsays:TouseVBA,youneedtoturnontheDeveloperTab:Right-clickonaspaceintheribbon->ClickCustomizeRibbonEnsuretheDeveloperoptionsisclickedClickOKReplyApril20,2020at10:00pmFredesays:Thanksfortheguide.HowdoIgetthesamenameonthepdfastheworkbook?ReplyMay7,2020at12:42pmExcelOffTheGridsays:HiFredeActiveWorkbook.Namewouldgiveyouthenameoftheworkbook.Butyouwouldalsogetthe“.xlsx”ontheend.SosomethinglikethiswouldgiveyouaPDFfilename:Left(ActiveWorkbook.Name,InStrRev(ActiveWorkbook.Name,"."))&"pdf"ReplyMay9,2020at5:46pmC'Nichesays:CannnotfindANYWHEREthatcantellmehowtoSELECTSPECIFICWORKSHEETSandcreateMULTIPLEPDFs.Canyou?ReplyMay14,2020at1:25amExcelOffTheGridsays:Sure–I’veaddedasectioninthepostabove:Loopthroughselectedsheets.ReplyMay19,2020at8:38pmPatridkMurphysays:Goodbeginnerarticle.Itwouldbegreattoseeyoutakethisfurtherandactuallymanipulatethepdfaftergeneratingit.Thingslikeaddingamenu,orbookmarks,modifyingthefilesecurity,addingattachmentstothepdffile,etc.arebigwinners.InmyownexperiencethiscanbedonebutitrequirestheuseoftheMicrosoft_JScriptlibraryandPDFobject.Letmeknowifyoutakethison.ReplyMay20,2020at7:27pmExcelOffTheGridsays:Thanksforthesuggestion.IfIeverlookintoit,thenI’llcertainlybesharingithere.ReplyMay20,2020at8:33pmMarcosays:Thankyoufortheveryusefulpost!Pleasekeepgoing!ReplyJune17,2020at11:56pmMattsays:Thanksthishashelpedmesaveatonoftime!Ihaveonequestion:Iwantthefilenameofmysheettobeacellvalue(theinvoicenumber)whichIcandowith:s=Range(“G6”).Value‘‘ActiveSheet.ExportAsFixedFormat_Type:=xlTypePDF,Filename:=s,…..etc….BUT…Thepdfgoestothelocationwherethespreadsheetis.Iwouldlikeittogotoadifferentfolder.Isthispossible?ReplyJune21,2020at1:02amExcelOffTheGridsays:Readthesectioninthearticleentitled“NotesforsavingPDFdocuments”,thisexplainshowtosaveindifferentlocations.Foryourscenario:YoueitherneedtoaddthefullfilepathintoG6ouryoucouldconcatenatetogethersuchas(assumingthefolderpathisinG7):s=Range("G7").Value&Range("G6").ValueReplyJune21,2020at7:01amMattsays:Brilliant;thatwasexactlythefix.Iputthefilepathinanothercellandusedtheconcatenateyousuggested.THANKYOUSOMUCH!!ReplyJune25,2020at8:10pmLindaPeppinsays:Hi,greatarticle.I’mtryingtodosomethingsimilarbutIcan’tfigureoutwherethelines=Range(“G7”).Value&Range(“G6”).Valuefitsintothecodeandwhat,ifanything,itreplaces.ThanksReplyMarch25,2021at1:14pmJitenderModgilsays:iamusingwin1064bitbuterroronSelection.ExportAsFixedFormatType:=xlTypePDF,_Filename:=saveLocationonReplyJuly2,2020at1:14amExcelOffTheGridsays:Idon’tbelievethereisanyreasonwhyWindows1064bitwouldbehavelikethat.IsthesaveLocationvalid?That’sthemostlikelycause.ReplyJuly2,2020at9:09pmLewissays:Thanksforthearticle,Idohaveonequestion.WhenIconvertthespreadsheettoaPDFsomeofthesheetsareoversizedandarestretchedacrossmultiplepages.Isthereawaytoadjustthezoombeforeconverting?Thankyou.ReplyJuly5,2020at9:27pmExcelOffTheGridsays:Ithinkit’sbaseduponhowtheprintsettingshavebeenapplied,adjustthesettingsinthePageLayoutmenu,PageSetupandScaletoFitsections.ReplyJuly5,2020at9:33pmKirandupargudesays:HelloSir,Ifoundthisarticleveryusefull,itreallyhelpedmealot.PeoplesLikeyouaregivingopportunitytobeginnerslikeustolearnmorethings&makeusmoreproductive.ReplyJuly6,2020at5:16pmPetersays:I’musingExcel2019inW10,64bitandmakeinasublikeyourspdf’sinaloopof129worksheets.Thepdf’softhelastworksheetsaremostlycorruptedafterthefirstrunofthesubandIgetthemessage‘insufficientsystemresources’.Inthesecondrunallthepdf’sarecorruptedandExcelstucks.HowcanIsolvethismemoryproblem?ReplyJuly12,2020at7:26pmTedWhittiersays:Greatarticleasalways.IhaveaprojectwithmultiplenamedrangesthatIwanttoexporttoPDF.theyareonvarioussheetsintheworkbook.I’mabletoexportthemtodifferentPDFfilesbutI’dliketoputthemallinthesamefile.Canitbedone?ReplyJuly17,2020at5:22pmExcelOffTheGridsays:HiTed–interestingquestion:It’snotsomethingI’vetried,butI’vegottwoideaswhichmaywork:Idea#1Youcouldtrythis:–Createnamedrangescalled“Print_Area”tobethesamerangeasthenamedranges.–Selecttheworksheets–ExporttheselectedworksheetsasPDFI’venottriedit,butthatshouldbepossible.Idea#2Hereisaharderoption,butshouldalsowork.–Copyandpastthenamedrangestoimagesallonthesameworksheet.–Positiontheimagessothattheyeachexistononeworksheetwithpagebreaksintherightplaces–ExporttheworksheetasaPDF.Letmeknowifyougetanysuccesswiththem.ReplyJuly17,2020at9:33pmHappyUsersays:thankyouforthishelp!Imadeaninvocewichcompleteautomaticallytoaregisterthentoapdf,evenifitisononedrive(ituploaditperfecly,thenopenonline)PERFECT!ReplyJuly18,2020at10:20pmAlexandrasays:Hello.So,ifiweretoloopthroughallworksheetsinasingleworkbooktosaveeachworksheetasaseparatePDFfile,howwouldimakesurethateachPDFfilehasaseparate/distinctnamethatIneed?Isitpossible?ReplyJuly31,2020at1:15amExcelOffTheGridsays:TheSaveNamepropertydeterminesthenameofthefile.Justcreateastringwhichincludesusetheworksheetnameoracountertocreateauniquename.ReplyAugust2,2020at9:32pmJeannettesays:Informationprovidedisveryeducative.Thankyou.HowcanIsavedatafromexcelintoindividualfillablepdffiles?Thanks.ReplyAugust21,2020at2:18amExcelOffTheGridsays:I’mnotsurethat’spossibledirectlyinExcel.IthinkyouwouldneedtouseaspecificPDFtooltoachievethat.ReplyAugust23,2020at8:02pmRicksays:Hieveryone,Iamusingtheloopingthroughsheetsexampletooutputseveralsheetsas1PDFpersheetandlovethatIfoundthismacro.However,IwantedtoincludeavaluefromcellA11inthefilenameofeachsheetbutfoundthatonlythevaluefromthefirstsheetisbeingused.ThismeansthattheMacrooutputstheseveralsheets,eachoverwritingthethelastasthemacrogoesalong.AnyideahowtogetitworkingusingthevalueinA11(Atextstring)foreachconsecutivesheet?Hereismycodesofar:SubExport_PDF()‘CreatevariablesDimwsAsWorksheet‘LoopthroughallworksheetsandsaveasindividualPDFinsamefolder‘astheExcelfileForEachwsInActiveWorkbook.Worksheetsws.ExportAsFixedFormatType:=xlTypePDF,_Filename:=ThisWorkbook.Path&“\mypath\”&Range(“A11”).Value&“.pdf”NextEndSubReplyOctober28,2020at6:16amExcelOffTheGridsays:I’venothadchangetotestthis,butifyouchangethis:...&Range("A11").Value&".pdf"Tothis:...&ws.Range("A11").Value&".pdf"itshouldwork.ReplyOctober28,2020at10:09amMarksays:Lookingtoautomatetheprocessoffillingcustomerinfoandprintingcontracts(~15,000).TodothisIhavesetupthecontracttemplateinaworksheetandhavelookupstofillinthecustomerinfo.WhereIamstuckissavingtherange(ofthecontract)aspdfandnamingittheuniquecustomerID(topreventoverwriting).Isispossibletoloopthisprocessoffillingthecustomerinfo,savingaspdfandprintingwhilemovingdownalistofcustomerIDs?Thisisaloadedquestion,butyourinsightswouldbegreatlyappreciated!ReplyNovember4,2020at9:13pmExcelOffTheGridsays:Yesthisispossible–Loopthroughthecellscontainingthecustomerlist.Withintheloopinclude:–Calculatetheworksheetsothatitrefreshesvaluesinthecontracttemplate–SavethetemplateasPDFusingthecustomernamewithinthefilename.ThiswillbeagreatwaytolearnhowtouseVBAtoautomateyourprocesses.ReplyNovember4,2020at10:20pmTeriVavriceksays:Hello.Thiswasveryhelpful,thankyou!IwonderedifyoucouldhelpmewithaproblemIamhaving.Ihaveamacro-enabledExceltemplatethatmultipleuserswillbeaccessingfromasharednetworkdrive.WhentheyrunthemacrotosavetheExcelfileasaPDF,Iwantittosavetotheirowndesktop,sousingafilepathsuchas“C:\Users\TeriVavricek\Desktop\File.pdf”(whichworksifiIwanttosavetoMYdesktop)inthefilenamewillnotwork.Isthereawaytocodethesavelocationtobethatofthedesktopoftheusercurrentlyusingthefile?Thankyou!ReplyNovember19,2020at10:23pmExcelOffTheGridsays:ThefollowingshouldgiveyouthefilepathtotheDesktopfolderDimFileNameAsString FileName=CreateObject("WScript.Shell").SpecialFolders("Desktop")&"\File.pdf"YoucanthenusetheFileNamevariablewithinthemacro.ReplyNovember21,2020at11:01pmTeriVavriceksays:Thankyouforthequickresponse!Istillseemtobealittlestuck.Belowismyoriginalcode.Whatitaccomplishedwasexporting3sheetstoPDFonmy(TeriVavricek)desktopwiththefilenamesetasthevalueofcellN2ontheAutoUWDocumentationsheet.I’mtryingtofigureouthowtoincorporatethecodeyouprovidedintowhatIhavehere.Iwouldappreciateanyadditionalassistanceyoucanprovide.Thankyou!SubExportAsPDFAUTO()DimstrFilenameAsStringDimrngRangeAsRangeSetrngRange=Worksheets(“AutoUWDocumentation”).Range(“n2”)strFilename=rngRange.Value&Format(Now(),“yyyymmdd”)Sheets(Array(“AccountUWDocumentation”,“LossAnalysis”,“AutoUWDocumentation”)).SelectSheets(“AccountUWDocumentation”).ActivateActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=_“C:\Users\TeriVavricek\Desktop\”&strFilename&“.pdf”_,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas_:=False,OpenAfterPublish:=FalseEndSubReplyNovember23,2020at2:39pmTeriVavriceksays:IthinkIfigureditout.Noneedtorespond.ReplyNovember24,2020at2:49pmExcelOffTheGridsays:Greatnews–ThanksTeriNovember25,2020at11:49pmTeriVavriceksays:Actually,Iguessitdidn’twork.Doyouhaveanysuggestionsformyquestionfrom11/23/2020?November30,2020at8:56pmAirasays:Thankyouforthedetailedexplanation.HowaboutwhenIneedtosaveindifferentPDFstheprintareainthesameworksheet?ThankyouReplyDecember7,2020at5:21amEsthersays:HellO!!greatinformationhere.However,wouldlovetoreceivehelponthis:iwannasavethepdfasacertainname,referringtoacellintheworksheetthatcontainsaformula(referencinganothercell)Howshouldicodethis?ReplyDecember17,2020at7:40amExcelOffTheGridsays:HiEsther–justchangetheFileNameparametertosomethinglikethis:Sheets("Sheet1").Range("A1").ValueThesheetnameandcellrefenceshouldbechangedforyourscenario.ReplyDecember18,2020at8:25pmMichelLhostsays:Hi,I’mtryingtuusethisformulabutneedtorenamemypdffilewithacellvalueSubSaveAsPDF()DimsaveLocationAsStringsaveLocation=“C:\MyData\myPDFFile.pdf”ActiveSheet.ExportAsFixedFormat_Type:=xlTypePDF,_Filename:=saveLocation,_OpenAfterPublish:=False,_IncludeDocProperties:=True,_IgnorePrintAreas:=False,_Quality:=xlQualityStandard,_From:=1,To:=2EndSubWhenIaddacellvaluelikethis,itdoesn’twork&.Range(“A1”).ValueCansomebodyhelpmepls?ThxinadvanceandhappynewyearReplyJanuary9,2021at6:11amExcelOffTheGridsays:I’mguessingthesyntaxyou’reusingisincorrect.Trysomethinglikethis.Filename:=Workbooks("myWorkbook").Sheets("mySheet").Range("A1").Value,_ Changetheobjecthierarchytomeetyourspecificneeds.ReplyJanuary11,2021at10:49pmMegansays:Iloveyou!I’vegotnomacroorprogrammingexperiencebutwasthrownintoaproject17daysago.Yourpagehastaughtmesomuch(withcontextsoIactuallyunderstandHOWthecodeswork.)Thankyouforhostingthispage.ReplyJanuary26,2021at8:31pmExcelOffTheGridsays:That’sgreatnews.It’salwaysgoodtohearthatmysiteisachievingwhatIwantittodo🙂ReplyJanuary26,2021at9:55pmAndysays:DearExpert,Iwouldliketorequestforyourhelpplease.Igatheredthefollowingcodesfromyourguidanceabove:==========================DimFileNameAsStringFileName=CreateObject(“WScript.Shell”).SpecialFolders(“Desktop”)&“\File.pdf”ActiveSheet.ExportAsFixedFormat_Type:=xlTypePDF,_FileName:=FileName,_Quality:=xlQualityStandard,_IncludeDocProperties:=False,_IgnorePrintAreas:=False,_OpenAfterPublish:=True==========================Thefilesuccessfullygotgeneratedbutthefilenameis“File.pdf”.IfIwantthefilenametobereferringtocell“B1”onmyworksheet,howshouldIamendthecodes?Imaysometimesrenametheworksheetbutthecellreferencewillalwaysbe“B1”.Thanks!ReplyMarch13,2021at7:43amExcelOffTheGridsays:ToreferenceB1onaworksheetisreasonablysimple.ActiveSheet.Range("B1").Value Thereforethelineofcodewouldbecome:FileName=CreateObject("WScript.Shell").SpecialFolders("Desktop")&"\"&ActiveSheet.Range("B1").Value However,thechallengereallycomesiftheworksheetnamechanges.ThecodeaboveusestheActiveSheet,whichmeansthecodeonlyrunscorrectlyiftheworksheetcontainingthecellreferenceisalwaystheactivesheetwhenthecodeisexecuted.Butifyouwantthecodetoruninthebackgroundonasheetwhereit’salwaysthesamesheet,butmayhaveadifferentname,thenusingthesheet’scodenamemightworkbetter.Checkoutthisblogpost:https://www.wiseowl.co.uk/blog/s112/microsoft-excel-vba-worksheet-sheet-name-codename.htmHowever,iftheworksheetitselfcanchange,thenitmaybebettertousetheApplication.InputBoxtogetthecellreferenceatrun-time.Checkoutthisblogpost:https://www.thespreadsheetguru.com/blog/vba-to-select-range-with-inputboxHopefully,oneofthosewillgetyouananswersuitableforyourscenario.ReplyMarch13,2021at5:19pmAndysays:You’reagenius,andaveryhelpfulgeniustoo!Thankyousomuchforyourguidance!ReplyMarch15,2021at12:00pmDanielsays:Thankyousomuchforyourcode,superuseful!OnethingIwaslookingataddingintomycode,wasthattheUsercouldnamethefilewhenexecutingthemacro.Icurrentlyhaveitsavedtothedesktop,butwantedthemtobeabletonameit,oruseconsecutivelywithouthavingtorenametheoriginal.ThisisyourcodeIamusingsofar:SubSave_Excel_As_PDF()DimsaveLocationAsStringMyPath=Environ(“USERPROFILE”)&“\Desktop\”saveLocation=MyPath&“NewEquipmentList.pdf”ActiveWorkbook.ExportAsFixedFormatType:=xlTypePDF,_FileName:=saveLocationEndSubAnyhelpwouldbegreatlyappreciated!ReplyMarch15,2021at11:43pmExcelOffTheGridsays:YoushouldbeabletouseanApplication.InputBox.Changethis:saveLocation=MyPath&"NewEquipmentList.pdf" Forthis:saveLocation=MyPath&Application.InputBox(Prompt:="Enterfilename",Type:=2)&".pdf" Topreventoverwritingexistingfilesyouwillneedtocheckthatthefiledoesnotalreadyexist.Trythispost:https://exceloffthegrid.com/vba-code-to-copy-move-delete-and-manage-files/ReplyMarch16,2021at10:07pmRommySetiadisays:Hi,Anymechanism,howtoremovethe“printtothepdffile”buttonafterthesheetisprinted?I’mjustaddingonebuttonthename“printtothepdffile”afterIclickthisbutton,theprocesswillbeexecuted,Igetapdffileincludingthe“printtopdffile”buttonAnysuggestionforthis?ReplyMarch16,2021at2:35amExcelOffTheGridsays:HiRommy,Yes,that’ssimpleenough.Right-clickthebutton.PressCtrl+1toopentheFormatShapesettings.IntheSizeandPropertiessection,unchecktheoptionentitled“Printobject”.Thenitwon’tappearwhenprinting.ReplyMarch16,2021at9:59pmChrisBoswellsays:I’dhaveneverthoughtofapproachingitthisway.UsuallyIjustsaveasPDFandeditinAcrobat.I’llhavetoplayaroundwiththis.There’sawholenewworldofpossibilitiesformetoexplore.ReplyApril17,2021at4:35pmJeroensays:Ihavetriedoutthecodeanditworks,exceptforthefactthatmysheetisorientedlandscapeandthePDFfileisPortraiteverytime.HowcanIsolvethis?ThanksReplyMay12,2021at1:14pmJulianRobertMitchellsays:Isthereawaytomakeitprintmorethanonesheetintoasinglepdffile?ReplyJune16,2021at10:34amMuraliPachipulususays:Thanksforthepost,Iamprintingexceltopdfusingexportasfixedformat.Printingfrompages20to30.Howtostartpagenumbersfrom1inPDFgeneratedinfooter?&Pstartsfrom20&P-20isnotgivingconsistentresultsReplyOctober1,2021at8:59pmKrishnasays:Theinformationwasreallymuchuseful.Thanksalot.JusthaveonequerythatwhileconvertingmultipleexcelsheetsintoPDFfiles,cantheyalsobeprotectedwithuniquepasswords??ReplyJanuary12,2022at5:22amExcelOffTheGridsays:Excel’sPDFcreationengine,isnotadvanced.YoumayneedtousetheAdobeAcrobatreferencelibrarylinkedintoyourVBAcodetoachievethat.ReplyApril21,2022at11:30amLeaveaReplyCancelreplyYouremailaddresswillnotbepublished.Requiredfieldsaremarked*CommentName*Email*Website ΔAwardsMicrosoftMVP:AcquisitionInternational:BusinessExcellenceAwards2021SpreadsheetInstructionalExpertoftheYearWheretofindmeYouTube: Twitter:Follow@exceloffthegridBooksTellmemore…RecentPostsGetdataintoPowerQuery–5commondatasourcesIntroductiontoPowerQueryVBARenameFile:5codeexamplesRemoveexcessspacesinPowerQueryCSVtoExcel:PowerAutomate&OfficeScriptsxx