Excel VBA Save As PDF: Step-By-Step Guide And 10 Examples

If you're creating a macro to save Excel files as PDF, and those PDF files must always be saved in the same folder, you can simply hard-code the relevant ... PowerSpreadsheetsfocusesonMicrosoftExcel. However,I'vewrittenseveraltutorials(suchashereandhere)thathavetodowithPDFand,moreparticularly,withthetopicofconvertingPDFfilestoExcel. ThereasonwhyIwriteaboutPDFisrelativelystraightforward:PDFisoneofthemostwidelyusedfileformats. ThisparticularExceltutorialalsofocusesonthetopicofworkingwithExcelandPDFfiles.Moreprecisely,IexplainindetailhowyoucansaveExcelfilesasPDFusingVBA,andprovide10examplesofVBAcodethatyoucanstartusingimmediately. Infact,inthisblogpost,IgomuchfurtherthansimplyshowingyouhowtosimplyconvertExcelfilestoPDF.Amongothers,Ialso: ProvideathoroughintroductiontohowyoucanuseVisualBasicforApplicationsforpurposesofadjustingthepagesetupattributes. IntroducesomedifferentwaysinwhichyoucanspecifythefilenameofthePDFfilethatresultsaftertheExceltoPDFconversion. ExplainhowyoucansaveeachworksheetfromanExcelworkbookinaseparatePDFfile. Thefollowingtableofcontentsliststhedifferentsectionsofthisblogpost. TableofContents HowToSaveAnExcelFileAsPDFUsingVBA:TheExportAsFixedFormatMethodParameter#1:TypeParameter#2:FilenameParameter#3:QualityParameter#4:IncludeDocPropertiesParameter#5:IgnorePrintAreasParameters#6And#7:FromAndToParameter#8:OpenAfterPublishParameter#9:FixedFormatExtClassPtrHowToSaveAnExcelFileAsPDFUsingVBA:BasicCodeExamples(Examples#1And#2)HowToSaveAnExcelFileAsPDFUsingVBA:CodeExample#1HowToSaveAnExcelFileAsPDFUsingVBA:CodeExample#2HowToAdjustThePageSetupToSaveAnExcelFileAsPDFUsingVBA:ThePageSetupObjectThePageSetupObject:PropertiesHowToSaveAnExcelFileAsPDFUsingVBA:CodeExample#3HowToChangeTheFilenameWhenSavingAnExcelFileAsPDFUsingVBA:HowToWorkWithTheFilenameParameterOfTheExportAsFixedFormatMethodUse#1(Examples#4,#5,#6And#7):HowToSpecifyTheFullPathAndNameOfAPDFFileUsingTheFilenameParameterOfTheExportAsFixedFormatMethodUse#2(Example#8):HowToNameAPDFFileUsingTheApplication.GetSaveAsFilenameMethodHowToSaveEachWorksheetAsASeparatePDFUsingVBA:HowToUseLoopsWithTheExportAsFixedFormatMethod(Examples#9And#10)Row#1:DimiCounterAsIntegerRow#2:ForiCounter=1ToWorksheets.CountRow#3:WithWorksheets(iCounter).PageSetupRows#4To#11:With…EndWithBlockRows#12To#20:Worksheets(iCounter).ExportAsFixedFormatConclusion ThisExcelVBASaveasPDFTutorialisaccompaniedbyfilescontainingthedataandmacrosIuseintheexamplesbelow.Youcangetimmediatefreeaccesstotheseexamplefilesbyclickingthebuttonbelow. ThebasicVBAmethodyouuseforconvertingExcelfilestoPDFisExportAsFixedFormat.Therefore,let'sstartthisblogpostbytakingalookatit: HowToSaveAnExcelFileAsPDFUsingVBA:TheExportAsFixedFormatMethod ThemainpurposeoftheExportAsFixedFormatmethodistoexportaparticularExcelobject(suchasafile,worksheet,cellrangeorchart)toanotherfileformat,usuallyPDF. YoucanapplytheExportAsFixedFormatmethodtoseveralExcelobjects.Tobemoreprecise,you'llgenerallybeworkingwith1ofthefollowingversionsofthismethod,dependingonwhichparticularobjectyouwanttosaveasPDF. TheWorkbook.ExportAsFixedFormatmethod,whensavinganExcelworkbookasPDF. TheWorksheet.ExportAsFixedFormatmethod,ifyou'resavingaworksheetasPDF. TheRange.ExportAsFixedFormatmethod,whenworkingwithaRangeobject. TheChart.ExportAsFixedFormatmethod,ifsavingachartasPDF. ThebasicsyntaxoftheExportAsFixedFormatmethodisgenerallyasfollows: expression.ExportAsFixedFormat(Type,Filename,Quality,IncludeDocProperties,IgnorePrintAreas,From,To,OpenAfterPublish) Forthesepurposes,“expression”isavariablerepresentingaWorkbook,Worksheet,ChartorRangeobject,dependingontheparticularversionofthemethodyou'reworkingwith.Inotherwords,“expression”istheplaceholderfortheobjectthatyouwanttoactuallysaveasPDF. ThisparticularVBAmethodhas9differentparameters.Let'stakealookateachofthem: Parameter#1:Type YouusetheTypeparametertospecifythetypeoffileformattowhichtheExcelfilemustbeexportedto.Inotherwords,youdetermineinwhichfileformatthenew(resulting)fileissavedbyspecifyingitintheTypeparameter. InordertospecifythetypeoffileyouwanttoconverttheExcelfileto,youusethexlFixedFormatTypeenumeration.Thereare2typesoffileformatsyoucanchoosefrom: FileType#1:xlTypePDF,representedbythevalueof0.xlTypePDFcorrespondstoPDFfiles,thesubjectofthisExceltutorial. FileType#2:xlTypeXPS,whosevalueis1.xlTypeXPScorrespondstoXPSfiles,Microsoft'salternativetoPDF. ThepurposeofthisExceltutorialistoexplainhowyoucanuseVisualBasicforApplicationstosaveanExcelfileasPDF.Therefore,wheneverusingtheTypeparameterinthisblogpost,IonlyusexlTypePDF. Furthermore,XPSneverseemedtogainmuchtraction.Therefore,you'reunlikelytoencounter(orhavetoworkwith)manyXPSfiles. TypeistheonlyrequiredparameteroftheExportAsFixedFormatmethod. Parameter#2:Filename Asimpliedbyitsname,youusetheFilenameparameteroftheExportAsFixedFormatmethodforpurposesofspecifyingthefilenameofthenew(converted)file. WhenspecifyingtheFilenameparameter,youhave2broadoptions: Option#1:Youcanspecifythefullpathandfilenameoftheresultingfile. Option#2:Youcanomitthefullfilepathand,instead,justincludethefilename.Inthiscase,Excelsimplysavestheconvertedfileinthecurrentdefaultfolder. Filenameisastring. Parameter#3:Quality YouhavetheoptionofchoosingthequalityoftheresultingPDFfile(standardorminimum). Forthesepurposes,youusetheQualityparameteroftheExportAsFixedFormatmethod.Moreprecisely,youcanchoose1ofthe2xlFixedFormatQualityoptions: QualityOption#1:xlQualityStandard,whosevalueis0.Thisis,simply,standardfilequality. QualityOption#2:xlQualityMinimum(valueis0).Thisrepresentsminimumfilequality. Parameter#4:IncludeDocProperties TheIncludeDocPropertiesparameteroftheExportAsFixedFormatmethodallowsyoutodeterminewhetherthedocumentpropertiesareincludedintheconvertedPDFfileornot. Tomakethischoice,yousimplyneedtosettheIncludeDocPropertiesparametertoTrueorFalseasfollows: True:Includesthedocumentproperties. False:Doesn'tincludethedocumentproperties. Parameter#5:IgnorePrintAreas ByusingtheIgnorePrintAreasparameteroftheExportAsFixedFormatmethod,youcanspecifywhetherVisualBasicforApplicationsshouldignore(ornot)theprintareasthathavebeensetfortherelevantExcelfile. Tospecifywhethertheprintareasshould(orshouldn't)beignored,settheIgnorePrintAreasparametertoTrueorFalseasfollows: True:Ignoreprintareas. False:Don'tignoreprintareas. Parameters#6And#7:FromAndTo TheFromandToparametersoftheExportAsFixedFormatmethodallowyoutospecifythepagesatwhichthepublishingtoPDFshouldbeginandend.Moreprecisely: FromisthenumberofthepageatwhichVisualBasicforApplicationsstartstopublish.IftheFromargumentisomitted,VBAstartsatthebeginning. ToisthenumberofthelastpagethatVBApublishes.IfyouomittheToargument,publishinggoesonuntilthelastpage. Parameter#8:OpenAfterPublish ByusingtheOpenAfterPublishparameteroftheExportAsFixedFormatmethod,youcandeterminewhethertheconvertedPDFfileshouldbedisplayedinthePDFviewerassoonastheexportprocessiscompleted. YouspecifywhetherthefileisdisplayedornotbysettingtheOpenAfterPublishparametertoTrueorFalse,asfollows: True:DisplayfileinPDFviewerafterconversion. False:Don'tdisplayfileinPDFviewerafterconversion. Parameter#9:FixedFormatExtClassPtr TheFixedFormatExtClassPtrparameteroftheExportAsFixedFormatmethodisapointertotheFixedFormatExtclass. HowToSaveAnExcelFileAsPDFUsingVBA:BasicCodeExamples(Examples#1And#2) NowthatyouarefamiliarwiththeExportAsFixedFormatVBAmethod,let'stakealookatsomecodeexamples. Forpurposesofthisexample,IhavepreparedasampleExcelworkbook.Thisworkbookcontains2worksheets.Eachworksheetincludesatablethatlists100personsandtheircontactdetails,alongwith(i)theirfoodpreferences,and(ii)theirfavoriteanimalandthenameoftheirpet. HowToSaveAnExcelFileAsPDFUsingVBA:CodeExample#1 AsIexplainabovewhenintroducingtheExportAsFixedFormatVBAmethod,theonlyrequiredparameterisType.Therefore,strictlyspeaking,youcancreateaverysimplemacroforpurposesofsavinganExcelfileasaPDF. Thefollowingsamplemacro(namedSave_Excel_As_PDF_1)is,probably,oneofthesimplestwaytosaveanExcelworksheetasPDFusingVBA. Thismacroconsistsofasinglesimplestatement: ActiveSheet.ExportAsFixedFormatType:=xlTypePDF Thisstatement,canbeseparatedinthefollowing3items: Let'stakeacloserlookateachoftheseelements: Item#1:ActiveSheet AsIexplainwhenintroducingtheWorksheet.ExportAsFixedFormatmethodabove,thereferencetothemethodmustbeprecededbyavariablerepresentingaWorksheetobject. InthecaseofthesampleSave_Excel_As_PDF_1macroabove,theApplication.ActiveSheetpropertyisusedforthesepurposes.Moreprecisely,ActiveSheetreturnanobjectrepresentingtheactivesheetwithintheactive(orspecified)workbook. Youcan,asageneralmatter,alsoforanyofthefollowing: MakereferencetoaWorkbookobject,andusetheWorkbook.ExportAsFixedFormatmethod. UseavariablethatrepresentsaRangeobject,andusetheRange.ExportAsFixedFormatmethod. UsearepresentationofaChartobject,andworkwiththeChart.ExportAsFixedFormatmethod. Item#2:ExportAsFixedFormat TheExportAsFixedFormatmethodisthemainsubjectofthisExceltutorial.Thepurposeofthismethodistosavetherelevantobject(aworksheetreturnedbyActiveSheetintheexampleabove)asaPDFfile. Inthisparticularcase,ExportAsFixedFormatusesasingleparameter,whichis… Item#3:Type:=xlTypePDF TypeistheonlyrequiredparameteroftheExportAsFixedFormatmethod.Itspurposeistospecifythetypeoffileformattowhichtherelevantworksheetshouldbeexportedto. YouspecifytherelevantfiletypebychoosingtheappropriatevaluefromtheXLFixedFormatTypeenumeration.xlTypePDFis1ofthe2valuesinsuchenumeration. ForthereasonsthatIexplainabove,most(ifnotall)ofyourmacroswhosepurposeistosaveExcelfilesasPDFwillsettheTypeparametertobexlTypePDF(or0).Therefore,you'relikelytousethisparticularlineofVBAcodeoftenwhencreatingsuchVBASubprocedures. ExcelToPDFConversionResults:Example#1 ThesampleSave_Excel_As_PDF_1macroisverysimpleandrelativelyeasytofollow.However,thisdoesn'tmeanthatyoushouldbeusingthismacrotoconvertallofyourExcelfilestoPDF. Thereasonforthisisthat,asIshowbelow,themacro(literally)justsavestheactiveworksheettoaPDFfile.Sinceitdoesn'tmakeanyadjustmentpriortotheconversion,theresultsaren'tnecessarilythebest. Forpurposesofthisexample,IhaveexecutedtheSave_Excel_As_PDF_1macrotosave1oftheworksheetswithinthesampleExcelfilethataccompaniesthistutorialasPDF.ThefollowingimageshowstheresultingPDFfile: Asfirstglance,theresultingPDFfileslooksOK.However,thereareseveralthingsthatcanbeimproved.Thefollowingare2examples: Theresultingpagelayoutisnotaccurate.Noticehowthere's1column(correspondingtotheFavoriteFoodIngredient)missingintheimageabove.WithintheresultingPDF,thiscolumnappearsbyitselfinseparatepages.Thefollowingscreenshotshowshowthislookslike: TheconvertedPDFfileissavedautomaticallytothedefaultlocalfilelocation(inthisexample,theDocumentsfolder)andthefilenameisthesameasthatoftheoriginalExcelworkbook(inthiscase,Book1). IshowyouhowtosolveseveraloftheseissuesintheotherVBAcodeexampleswithinthisExceltutorial. Let'sstarttotacklesomeoftheseproblemsbyusingtheadditionalargumentsoftheExportAsFixedFormatmethod: HowToSaveAnExcelFileAsPDFUsingVBA:CodeExample#2 ThesampleSave_Excel_As_PDF_1macroaboveusedtheWorksheet.ExportAsFixedFormatmethodwithitsonlyrequiredparameter(Type).However,asIexplainfurtherabove,theExportAsFixedFormatmethodhas9parametersthatallowyoutofurtherspecifyhowVisualBasicforApplicationscarriesouttheconversionfromExceltoPDF. Therefore,inthissecondexample,IincludemostoftheparametersthatyoucanusewhenworkingwiththeExportAsFixedFormatmethod. Let'sstartbytakingalookattheVBAcodebehindthemacro(whichiscalledSave_Excel_As_PDF_2): Thestatementswithinthebodyofthisnewmacroareasfollows: ActiveSheet.ExportAsFixedFormat_ Type:=xlTypePDF,_ Filename:="SampleExcelFileSavedAsPDF",_ Quality:=xlQualityStandard,_ IncludeDocProperties:=False,_ IgnorePrintAreas:=False,_ From:=1,_ To:=5,_ OpenAfterPublish:=True Let'stakeacloserlookateachoftheselinesofVBAcode: Lines#1And#2:ActiveSheet.ExportAsFixedFormatType:=xlTypePDF Thisnewmacro(Save_Excel_As_PDF_2)issimplyanextensionofthefirstsamplemacroIprovideabove(Save_Excel_As_PDF_1). Thesefirst2linesarethewholebodyofthatpreviousmacro.Inthisparticularcase,theyhavethesamepurposeasthatwhichIexplainabove.Namely: TellVBAthattheExportAsFixedFormatmethodshouldbeappliedtothecurrentactiveworksheet. SetPDFasthefileformattowhichtheworksheetshouldbeexportedto. Line#3:Filename:=”SampleExcelFileSavedAsPDF” ThislineusestheFilenameparameteroftheExportAsFixedFormatmethodforpurposesofspecifyingthenameofthefinalPDFfile. AsIexplainabove,theprevioussamplemacrosimplyusedthenameoftheactiveExcelworkbooktonamethePDFfilethatwascreatedafterexecutingtheExportAsFixedFormatmethod.ByusingtheFilenameparameter,youcanchooseadifferentfilename. Inthisparticularcase,thefilenamethatI'musingisquitesimpleandonlyincludestheactualfilename.Therefore,thePDFfileissavedinthedefaultfilelocation. However,asIexplainabove,youcanusethissameparameterforpurposesofdeterminingthefullpathoftheconvertedPDFfile. Line#4:Quality:=xlQualityStandard ThislineofVBAcodedeterminesthequalityofthepublishedPDFfile.TheQualityparameteroftheExportAsFixedFormatcanbesettoeitherof2values: xlQualityStandard(or0),asinthiscase,whichspecifiesstandardquality. xlQualityMinimum(or1),whichrepresentsminimumquality. Line#5:IncludeDocProperties:=False YoucanusetheIncludeDocPropertiesparameteroftheExportAsFixedFormatVBAmethodforpurposesofdeterminingwhetherthedocumentpropertiesareincludedornot.IncludeDocPropertiescanbesettoeitherofthefollowing2values: Trueindicatesthatthedocumentpropertiesshouldbeincludedintheresultingfile. False(asinthiscase)indicatesthatthedocumentpropertiesshouldn'tbeincluded. Line#6:IgnorePrintAreas:=False ThepurposeoftheIgnorePrintAreasparameteroftheExportAsFixedFormatmethodistodeterminewhetheranyprintareasthataresetwhenpublishingshouldbeignoredornot.Justasthepreviousparameter(IncludeDocProperties),IgnorePrintAreascanbesettoeitherTrueorFalse. IfIgnorePrintAreasissettoTrue,themacroignoresanyprintareas. IfyousetIgnorePrintAreastoFalse(asinthisexample)themacrousestheprintareassetwhenpublishing. Lines#7And#8:From:=1,To:=5 YoucanusetheFromandToparametersoftheExportAsFixedFormatVBAmethodforpurposesofsettingthefirstandlastpagesthatarepublishedintheconvertedfile.Moreprecisely: Fromallowsyoutosetthepageatwhichpublishingbegins. Toallowsyoutodeterminethelastpagethatispublished. InthecaseofthesampleSave_Excel_As_PDF_2macro,IusetheFromandToparametersforestablishingthatonlypages1(From)through5(To)shouldbepublished.HadIomittedanyoftheseparameters,theeffectwouldhavebeenasfollows: IftheomittedparameterisFrom,publishingbeginsatthebeginning.Noticehow,inthecasewe'reanalyzing,I'vesetthevalueofFromto1(thebeginning).Therefore,IcanomittheFromparameterfromtheSave_Excel_As_PDF_2macrowhilecontinuingtoobtainthesameresults. IfyouomittheToparameter,publishinggoesallthewaytothelastpage.IntheSave_Excel_As_PDF_2macro,I'vesettheToparameterto5.Thisisn'tthelastpage.Therefore,ifIomittheToparameter,theconversionresultsdiffer.Insteadofstoppingthepublishingprocessatpage5(asthesampleVBAcodeabovedoes),VisualBasicforApplicationswouldonlyendpublishingatthelastpage. Line#9:OpenAfterPublish:=True ThepurposeofthislineofVBAcodeistodisplaytheresultingPDFfileinthePDFvieweroncetheconversionprocesshasbeencompleted. YoucanalsosetOpenAfterPublishtoFalse.Inthiscase,thepublishedPDFfileisn'tdisplayed. ExcelToPDFConversionResults:Example#2 Forpurposesofcomparingtheresultsobtainedbythefirst2macroexamples(thisandexample#1above),IconvertthesameExcelworksheetofthesampleworkbookusingtheSave_Excel_As_PDF_2macro. Thefollowingimageshowshowtheresultslooklike. Atfirstglance,thislooksverysimilartotheresultsobtainedwhenexecutingtheSave_Excel_As_PDF_1macroabove.However,noticesomeoftheeffectsoftheadditionalparametersthatIincludedinthissecondmacro: Thenameofthefile,asdeterminedbytheFilenameparameteris“SampleExcelFileSavedAsPDF”. Publishinggoesfrompages1through5,asspecifiedusingtheFromandToparameters. ThePDFfileisdisplayedautomaticallywhenthemacroisexecutedandimmediatelyaftertheconversionprocessiscompleted. Overall,theexplanationsandexamplesinthesectionsaboveshowmostofwhatyouneedtoknowabouttheExportAsFixedFormatVBAmethod. Youmaynotice,however,thatthereareafewthingsthatyoucan'tfixwiththeExportAsFixedFormatmethodalone.Thisincludes,amongothers,onethatImentionabove: Thepagelayoutisn'tproperlyadjusted.Therefore,theresultingPDFfileonlyshowsthefirst4columnsofthetablewithintheoriginalExcelsourcefile.Thelastcolumn(whichcorrespondstoFavoriteFoodIngredientintheexampleabove)ismissing. TherearemanywaysinwhichyoucanuseotherVBAconstructstocreatemorecomplexandsophisticatedmacrostosaveExcelfilesasPDF.Ishowyousomeoftheconstructsyoucanuseinthefollowingsections,alongwithsomefurtherexamples. Let'sstartbytakingalookathowyoucansolvetheissueImentionabove:HowtoadjustthepagelayoutoftheExcelfileyou'regoingtoconverttoPDFusingVBA. Todothis,youmustunderstand… HowToAdjustThePageSetupToSaveAnExcelFileAsPDFUsingVBA:ThePageSetupObject WithinExcel,wheneveryouneedtomanuallyadjustthepagesetupbeforeprintinganExcelfileorsavingitasPDF,yougotothePageSetupdialogbox. Wheneveryou'reworkingwithVisualBasicforApplications,youcanusethePageSetupobjectforpurposesofmodifyinganypagesetupattributes.Thereasonforthisisthat,asexplainedattheMicrosoftDevCenter(inthewebpageIlinktoabove),thePageSetupobject: “Representsthepagesetupdescription”;and Containsallofthepagesetupattributes.EachofthesepagesetupattributesisapropertyofthePageSetupobject. Asaconsequenceofthis,thelistofpropertiesthataremembersofthePageSetupobjectisquiteextensive.Let'stakealookatit: ThePageSetupObject:Properties Bymycount,thePageSetupobjecthasthe48propertiesthatIintroduceinthetablebelow. Thepurposeofthistableissimplytointroducethesepropertiesandtheirmainpurpose.Someofthesepropertiesaren'trelevantforthepurposeofthisExceltutorialonhowtosaveanExcelfileasPDFusingVBA.However,IexplainsomeofthepropertiesyoumaywanttoexplorewhenworkingwiththeExportAsFixedFormatmethodbelow. Property Purpose CenterHorizontally Specifieswhetherthesheetiscenteredhorizontallywhenprinted. CenterVertically Specifieswhetherthesheetiscenteredverticallywhenprinted. DifferentFirstPageHeaderFooter Determineswhethertheheaderorfooteronthefirstpageisdifferent. OddAndEvenPagesHeaderFooter Specifieswhethertherearedifferentheadersandfootersforoddandevenpages. CenterFooter Returnsorsetsthecentersectionofthefooter. CenterHeader Returnsorsetsthecentersectionoftheheader. LeftFooter Returnsorsetstheleftsectionofthefooter. LeftHeader Returnsorsetstheleftsectionoftheheader. RightFooter Returnsorsetstherightsectionofthefooter. RightHeader Returnsorsetstherightsectionoftheheader. CenterFooterPicture ReturnsaGraphicobjectrepresentingthepictureinthecentersectionofthefooter.UsethepropertiesofCenterFooterPicturetosettheattributesofthepicture. CenterHeaderPicture ReturnsaGraphicobjectrepresentingthepictureinthecentersectionoftheheader.UsethepropertiesofCenterHeaderPicturetosettheattributesofthepicture. LeftFooterPicture ReturnsaGraphicobjectrepresentingthepictureintheleftsectionofthefooter.UsethepropertiesofLeftFooterPicturetosettheattributesofthepicture. LeftHeaderPicture ReturnsaGraphicobjectrepresentingthepictureintheleftsectionoftheheader.UsethepropertiesofLeftHeaderPicturetosettheattributesofthepicture. RightFooterPicture ReturnsaGraphicobjectrepresentingthepictureintherightsectionofthefooter.UsethepropertiesofRightFooterPicturetosettheattributesofthepicture. RightHeaderPicture ReturnsaGraphicobjectrepresentingthepictureintherightsectionoftheheader.UsethepropertiesofRightHeaderPicturetosettheattributesofthepicture. BottomMargin Returnsorsetsthebottommargin. FooterMargin Returnsorsetsthemarginbetweenthebottomofthepageandthefooter. HeaderMargin Returnsorsetsthemarginbetweenthetopofthepageandtheheader. LeftMargin Returnsorsetstheleftmargin. RightMargin Returnsorsetstherightmargin. TopMargin Returnsorsetsthetopmargin. AlignMarginsHeaderFooter Determineswhethertoaligntheheaderandthefooterwiththemargins. Application Whenyouuseitwithanobjectqualifier,returnsanApplicationobjectrepresentingthecreatorofthatobject. BlackAndWhite Specifieswhetherthedocumentisprintedinblackandwhite. Creator Returnsa32-bitintegerindicatingtheapplicationinwhichtherelevantobjectwascreated. Draft Specifieswhetherasheetisprintedwithoutgraphics. EvenPage ReturnsorsetsthetextalignmentfortheevenpagesofanExcelworkbook(orasection). FirstPage ReturnsorsetsthetextalignmentforthefirstpageofanExcelworkbook(orasection). FirstPageNumber Returnsorsetsthefirstpagenumbertobeusedwhenasheetisprinted. FitToPagesTall Returnsorsetsthenumberofpages(tall)towhichanExcelworksheetisscaledtowhenprinted. FitToPagesWide Returnsorsetsthenumberofpages(wide)towhichanExcelworksheetisscaledtowhenprinted. Order ReturnsorsetsthevaluerepresentingtheorderthatExcelusestonumberpageswhenprintinglargeworksheets. Orientation Returnsorsetsthevaluerepresentingportraitorlandscapeprintingmode. Pages ReturnsorsetsthecountoritemnumberofthepageswithinthePagescollection. PaperSize Returnsorsetsthesizeofthepaper. Parent Returnstheparentobjectfortherelevantobject. PrintArea Returnsorsetstherangetobeprinted. PrintComments Returnsorsetsthewaycommentsareprinted. PrintErrors Returnsorsetstheconstantspecifyingthetypeofprinterrordisplayed. PrintGridlines Determineswhethercellgridlinesareprinted. PrintHeadings Determineswhetherrowandcolumnheadingsareprinted. PrintNotes Determineswhethercellnotesareprintedasendnotes. PrintQuality Returnsorsetstheprintquality. PrintTitleColumns Returnsorsetsthecolumnstoberepeatedontheleftsideofeachpage. PrintTitleRows Returnsorsetstherowstoberepeatedatthetopofeachpage. ScaleWithDocHeaderFooter Returnsorsetswhethertheheaderandfootershould(also)bescaledwiththerestoftheExcelfilewhenthesizechanges. Zoom ReturnsorsetsavaluerepresentingthepercentagebywhichExcelscalestheExcelworksheetforprinting. Let'stakealookathowyoucanusethePageSetupVBAobjecttoimprovetheresultsobtainedwhensavinganExcelfileasPDFwiththeExportAsFixedFormatmethod. HowToSaveAnExcelFileAsPDFUsingVBA:CodeExample#3 Theprevious2macroexampleswithinthisExceltutorialfocusonhowyoucanusetheExportAsFixedFormatmethodforpurposesofsavinganExcelfileasPDFusingVBA. Thisthirdexamplebuildsonthose2examples(inparticularexample#2above)toshowhowyoucanimprovetheresultsyouobtainwhencarryingoutanExceltoPDFconversionusingVBA. Let'sstartbytakingalookattheVBAcodebehindthesamplemacrowhich,inthiscase,iscalledSave_Excel_As_PDF_3: Thestatementsinthebodyofthismacroarethefollowing: WithActiveSheet.PageSetup .CenterHeader="SampleExcelFileSavedAsPDF" .Orientation=xlPortrait .PrintArea="$B$5:$F$105" .PrintTitleRows=ActiveSheet.Rows(5).Address .Zoom=False .FitToPagesTall=False .FitToPagesWide=1 EndWith ActiveSheet.ExportAsFixedFormat_ Type:=xlTypePDF,_ Filename:="SampleExcelFileSavedAsPDF2",_ Quality:=xlQualityStandard,_ IncludeDocProperties:=False,_ IgnorePrintAreas:=False,_ From:=1,_ To:=5,_ OpenAfterPublish:=True NoticethatthesecondpartoftheVBASubprocedure(theblockthatstartswith“ActiveSheet.ExportAsFixedFormat”)isexactlythesameasthebodyofmacroexample#2above(Save_Excel_As_PDF_2),whichIhaveexplainindetailthere. Therefore,inthisparticularsection,Ionlyexplain(line-by-line)thefirstsectionofthemacro.Inotherwords,theWith…EndWithblock. Let'stakealookateachoftherowsinthisWith…EndWithblock: Row#1:WithActiveSheet.PageSetup TheWithstatementallowsyoutoexecuteaseriesofstatementsonaparticularobject(ActiveSheet.PageSetupinthiscase)withouthavingtorepeattheobjectqualificationeverysingletime. Inotherwords,allofthestatementswithintheWith…EndWithblock(rows#2to#8below)makereferencetotheobjectthatappearsontheopeningstatement(ActiveSheet.PageSetup).TheWith…EndWithblockendsonrow#9,withtheEndWithstatement. Theobjectthatappearsontheopeningstatementisthepagesetupdescriptionoftheactivesheet.TheobjectisobtainedbyusingtheWorksheet.PageSetupproperty.Inthisparticularsituation,theWorksheetobjectistheactivesheet(ActiveSheet). Row#2:.CenterHeader=“SampleExcelFileSavedAsPDF” ThePageSetup.CenterHeaderpropertyallowsyoutosetwhatappearsinthecentersectionofthepageheader. Youspecifythetextthatyouwanttoappearinthissectionasastring.Intheexampleabove,thetextthatIspecifyasheaderis“SampleExcelFileSavedAsPDF”. YoucangenerallyapplythesyntaxandexplanationIprovideherefortheCenterHeaderpropertytothefollowing(roughlyequivalent)properties: ThePageSetup.CenterFooterproperty. ThePageSetup.LeftFooterproperty. ThePageSetup.LeftHeaderproperty. ThePageSetup.RightFooterproperty. ThePageSetup.RightHeaderproperty. Row#3:.Orientation=xlPortrait YoucanusethePageSetup.OrientationpropertyforpurposesofsettingthexlPageOrientationvaluethatspecifiesthepageorientation.TheOrientationpropertycantakethefollowing2values: xlPortraitor0,which(asinthisexample)representsportraitmode. xlLandscapeor1,whichrepresentslandscapemode. Row#4:.PrintArea=“$B$5:$F$105” ByusingthePageSetup.PrintAreaproperty,youcansettheprintarea. YougenerallyspecifytherelevantrangeusingA1-stylereferencesandasastring.UndertheA1-stylecellreferencingstyle,youuselettersforcolumnheadingsandnumbersforrows. InthecaseofthesamplemacroSave_Excel_As_PDF_3,IusethePrintAreapropertytospecifytherangetobeconvertedtoPDF.ThisrangeisfromcellsB5toF105. Row#5:.PrintTitleRows=ActiveSheet.Rows(5).Address ThePageSetup.PrintTitleRowspropertyallowsyoutospecifythataparticularrow(orsetofrows)shouldberepeatedatthetopofeachpage. Generally,youspecifytherowstoberepeatedasastringusingA1-stylereferences.Inthisparticularexample,Idon'texplicitlytypeA1-stylereferencesinthecode.Let'stakealookatwhat“ActiveSheet.Rows(5).Address”does: ActiveSheet.Rows(5):Here,IusetheWorksheet.RowspropertytorepresentRow5oftheactiveworksheet. Address:ThismakesreferencetotheRange.Addressproperty,whichreturnsastringrepresentingtherelevantrangereference. Inotherwords,“ActiveSheet.Rows(5).Address”alsoreturnsanA1-styleaddressasastring,whichispreciselywhatthePrintTitleRowspropertyrequires. YoucangenerallyusethesameprinciplesandsyntaxthatIexplainhereinconnectionwiththe(roughlyequivalent)PageSetup.PrintTitleColumnsproperty. Row#6:.Zoom=False ByusingthePageSetup.Zoomproperty,youcandeterminehowExcelscalestherelevantExcelworksheetforprintingor,inthiscase,conversiontoPDF. Asageneralrule,youspecifythezoomasaVariantvaluebetween10and400percent. However,noticethatinthesampleSave_Excel_As_PDF_3macro,IsettheZoompropertytoFalse.WheneverPageSetup.ZoomissettoFalse,thepropertiesthatdeterminehowExcelscalestheExcelworksheetarePageSetup.FitToPagesWideandPageSetup.FitToPagesTall.Atthesametime,ifthePageSetup.ZoompropertyisTrue,boththeFitToPagesWideandFitToPagesTallpropertiesareignored. Thisleadsusto… Row#7:.FitToPagesTall=False YoucanusethePageSetup.FitToPagesTallpropertyforpurposesofsettingthenumberofpagestalltowhichtherelevantExcelworksheetisscaledtowhenprintingor,inthecaseoftheexampleinthistutorial,convertedtoPDF. Intheexamplewe'retakingalookat,IsettheFitToPagesTallpropertytoFalse.Insuchcases,ExcelscalestheworksheetinaccordancewiththeFitToPagesWideproperty.Thisisdonein… Row#8:.FitToPagesWide=1 ThesyntaxandpurposeofthePageSetup.FitToPagesWidepropertyisroughlythesameasthatoftheFitToPagesTallpropertyabove. Inotherwords,youusetheFitToPagesWidepropertytospecifythenumberofpageswidetherelevantExcelworksheetisscaledtowhenprintingorconvertingtoPDF(asinthisexample).InthesampleSave_Excel_As_PDF_3macro,Isetthisvalueto1(pagewide). Row#9:EndWith Asanticipatedabove,thisEndWithstatementsimplyendstheWith…EndWithblockthatwe'vejustanalyzed. ExcelToPDFConversionResults:Example#3 TheSave_Excel_As_PDF_3usestheExportAsFixedFormatmethodinexactlythesamewayasthepreviousexample#2(Save_Excel_As_PDF_2),tothepointthattherelevantblockofVBAcodeisexactlythesame. AsIshowinthisexample,youcanmakeseveralchangesoradjustmentsthatinfluencetheresultingPDFfilebyusingthePageSetupobject.Let'stakealookattheresultsofexecutingtheSave_Excel_As_PDF_3macroonthesameExcelworksheetandnotedownthemaindifferences: NotetheeffectsofeachofthepropertiesofthePageSetupobjectthatthesampleSave_Excel_As_PDF_3macrouses: Thecentersectionoftheheadersays“SampleExcelFileSavedAsPDF”,asrequiredbythePageSetup.CenterHeaderpropertyinrow#2above. Theprintedareadoesn'tincludethePowerSpreadsheetslogo,thepreliminarytitleofthisblogpostorthehyperlinkedtexttothispagewhichappearinthefirst3rowsoftheExcelworksheet.ThisisadirectconsequenceofusingthePageSetup.PrintAreapropertytospecifytherangefromcellsB5toF105astheprintarea,asdoneinrow#4above. TheheaderrowoftheExceltableisrepeatedinallofthepageswithintheconvertedPDFfile.ThisisachievedbytheappropriateuseofthePageSetup.PrintTitleRowspropertyinrow#5above. AllofthecolumnsintheoriginalExceltableappearinanappropriatemanner.Inotherwords,thetablethatappearsinthePDFfilehasbeenscaledinsuchawaythatitis1pagewide.Ifyougobackup,you'llnoticethatbothpreviousexamples(macro#1andmacro#2)didn'tfittheprintareaappropriatelytothepage.Thefifthcolumn(FavoriteFoodIngredient)wasleftout(inmacroexample#2)orpublishedbyitselfinthelastpagesofthePDFfile(inmacroexample#1).ThisproblemissolvedbyusingtheZoom,FitToPagesTalland(particularly)FitToPagesWidepropertiesofthePageSetupobject.Youcangobacktorows#6,#7and#8abovetoseehowtheVBAcodelookslike. HowToChangeTheFilenameWhenSavingAnExcelFileAsPDFUsingVBA:HowToWorkWithTheFilenameParameterOfTheExportAsFixedFormatMethod Parameter#2oftheExportAsFixedFormatmethodthatIexplainaboveisFilename.YoucanusethisparametertosetthefilenameoftheresultingPDFfile.VisualBasicforApplicationsallowsyoutodoeitherofthefollowing: Specifythefullpathandfilenameofthefile. Specifyjustthefilename.Inthiscase,thefileissavedinthecurrentdefaultfolder. Inthepreviousmacroexamples,I'venotdonemuchwiththeFilenameparameter.Moreprecisely: Inthefirstmacroexample,Ididn'tevenincludetheFilenameparameter(it'soptional). Inthe(slightlymoresophisticated)macros#2(Save_Excel_As_PDF_2)and#3(Save_Excel_As_PDF_3),Ionlyincludedthefilename(withoutthepath).ThisresultedintheconvertedPDFfilesbeingsavedinthedefaultfolder(theDocumentsfolder,inmycase). TheuseoftheFilenamethatImakeinthesefirst3examplesmayworkforyouincertaincircumstances.Afterall,thematerialstructureofthosemacrosisenoughtohelpyousaveExcelworksheets,workbooks,rangesorchartsasPDFfiles. However,inseveralsituations,you'llwanttousetheFilenameparameterinaslightlydifferentwayforpurposesofcreatingmoresophisticatedmacrosthatsaveExcelfilesasPDF.Let'stakealookatsomedifferentwaysyoucanusetheFilenameparameter: Use#1(Examples#4,#5,#6And#7):HowToSpecifyTheFullPathAndNameOfAPDFFileUsingTheFilenameParameterOfTheExportAsFixedFormatMethod Ifyou'recreatingamacrotosaveExcelfilesasPDF,andthosePDFfilesmustalwaysbesavedinthesamefolder,youcansimplyhard-codetherelevantfilepathandnameusingtheFilenameparameterofExportAsFixedFormat. Takealook,forexample,attheVBASubprocedure,namedSave_Excel_As_PDF_4,whichappearsbelow. Thisis,substantially,thesameasexample#3above(Save_Excel_As_PDF_3).Notice,however,howI'vespecifiedthefullpathofthefileusingtheFilenameargument. TheconsequenceofthischangeisthattheresultingPDFfileisnolongersavedinthedefaultfolder.Instead,thePDFissavedinthefolderthatyouspecifywiththeFilenameargument.Intheexampleabove,thefileissavedtoaOneDrivefoldercalledPowerSpreadsheets. Youdon'tnecessarilyneedtohard-codeallofthedetailsintheFilenameparameter.Youcan,forexample,concatenatedifferentitemsforpurposesofbuildingamoreflexiblefilenamestructure. Thefollowingmacro(namedSave_Excel_As_PDF_5)exampleshowsthepossibilitiesyouhavewhendoingthis.Noticehowtheonlydifferencebetweenthismacroandthepreviousexample(Save_Excel_As_PDF_4)isintheFilenameparameter. Theconsequenceofincludingthestatement“ActiveWorkbook.Path&”priortotheactualfilename(“\SampleExcelFileSavedAsPDF4”)isthat,now,VisualBasicforApplicationssavestheresultingPDFfileinthesamefolderastheactiveworkbook.Inotherwords,ifyouusethisFilenamestructure,thenewPDFfileissavedinthesamefolderasthesourceExcelworkbook. ThereasonforthisisthattheWorkbook.Pathpropertyreturnsastringwiththecompletepathtotherelevantworkbook.Inthecaseoftheexampleabove,therelevantworkbookistheactiveExcelworkbook(returnedbytheApplication.ActiveWorkbookproperty). Iusetheampersandoperator(&)toconcatenatethefilepathtotheactiveworkbookandthefilename,whichissurroundedbyquotations(“”). Youcantakethisflexibilityevenfurther. Forexample,themacrobelow(Save_Excel_As_PDF_6)usesthenameoftheactiveworksheetasthenameoftheconvertedPDFfile. Noticehowtheonlydifferencewiththepreviousmacro(Save_Excel_As_PDF_5)istheconcatenationoftheWorksheet.Nameproperty(usingtheApplication.ActiveSheetpropertytoreturnthenameoftheactiveworksheet)astheactualfilenamewithintheFilenameparameter.ThefilepathcontinuestobeprovidedbytheWorkbook.Pathproperty. Asafinalexampleforthisparticularsection,let'stakealookatthefollowingmacro(Save_Excel_As_PDF_7),whichsavestheactiveExcelworksheetasaPDFnamedafterthevalueincellF5oftheactivesheet. Inthisparticularcase,themacrouses(i)theApplication.ActiveSheetandWorksheet.RangepropertiestoreturncellF5,and(ii)theRange.Valuepropertytoreturnthevalueinthatcell. Youcanachievesimilarresultsusingslightlydifferentstructures.Thisincludes,forexample,namingthe(converted)PDFfilebyconcatenatingseveralstringsoftext. Insomecases(suchaswhenconcatenatingseveralstringsoftexttospecifythePDFfilename),itmaybemoreappropriatetodeclareavariableatthebeginningoftheproceduretostoretheapplicablestring. WheneveryouuseaworksheetcellasthesourceforthePDFfilename(asintheexampleabove),makesurethatthecellcontainsnoforbiddencharacters.Ifthecellfromwhichyoutakethefilenamecontainscharacterssuchas\,/,:,^,?,“,or|,themacromaynotworkproperly. TheVBAcodesamplesthatIprovideinthissectionarejustsomeoftheexamplesofthedifferentwaysyoucanworkwith(andgenerate)theFilenameargumentoftheExportAsFixedFormatmethod.Asyoucansee,VisualBasicforApplicationsprovidessignificantflexibilityinthisregard. Inthisblogpost,IexplainhowtosaveanExcelworkbookusingVBA.Eventhoughthetopicisn'tpreciselythesameasthatofthisblogpost,someoftheprinciplesandexamplesIprovidetheremaybehelpfultoyouwhenworkingwiththeFilenameparameteroftheExportAsFixedFormatVBAmethod. Use#2(Example#8):HowToNameAPDFFileUsingTheApplication.GetSaveAsFilenameMethod YoucanusetheApplication.GetSaveAsFilenamemethodtoallowyourPDF-savingmacrotogetafilenamefromtheuser.ThisallowstheuserofyourVBAapplicationtospecifythefullpathandfilename. IcovertheApplication.GetSaveAsFilenamemethodindetailinmyExceltutorialaboutthetopic(Ilinktoitabove).Forpurposesofthismacroexample,it'senoughtoknowthattheGetSaveAsFilenamemethod: DisplaystheSaveAsdialogbox. GetsthefilenamethattheuserentersintheSaveAsdialogbox. Doesn'tactuallysaveorexportafile.Thisisthereasonwhy,asshownbelow,youmustanywayusetheExportAsFixedFormatmethodforpurposesofsavinganExcelworkbookasPDF. Thefollowingsamplemacro(Save_Excel_As_PDF_8)showshowyoucanusetheApplication.GetSaveAsFilenamemethodtoprovidetheFilenameparameterwhenusingtheExportAsFixedFormatmethod: Let'stakeacloserlookattheVBAcodebehindtheSave_Excel_As_PDF_8macrotounderstandwhat'sgoingon: First,noticethat2blocksofVBAcodelookarequitefamiliar: The2blocksofcodeIhighlightintheimageabovearesubstantiallythesameasthosethatappearinotherexamplesofVBAcodewithinthisExceltutorial.Moreprecisely: Block#1isaWith…EndWithblockthatappearsinmacroexamples#3(whereIexplaineachlineofcode),#4,#5,#6and#7. Block#2,whichusestheWorksheet.ExportAsFixedFormatmethod,issubstantiallythesameastheequivalentblocksofcodeinmacroexamples#2(whereitisexplainedindetail),#3,#4,#5,#6and#7. TheonlydifferenceisintheFilenameparameter,whichisthetopicwe'refocusingoninthissection. ConsideringthatIexplaineachofthese2blocksofVBAcodeindetailintheprevioussectionsofthisExceltutorial,wecannowgothrougheachoftheblocksofcodeintheSave_Excel_As_PDF_8macro: Block#1:DimPdfFilenameAsVariant ThepurposeofthislineistodeclareavariablecalledPdfFilename. IcoverthetopicofVBAvariablesinmoredetailhereandhere.Avariableissimplyastoragelocationthatyoupairwithaname.Youusethevariabletorepresentacertainvalue. Inthisexample,IusethePdfFilenamevariableforpurposesofstoringthefilenamethattheuserentersintotheSaveAsdialogbox(displayedbytheApplication.GetSaveAsFilenamemethodinblock#3below).ThevariableislaterusedintheFilenameparameteroftheExportAsFixedFormatinblock#3below. ThePdfFilenamevariableisoftheVariantdatatype.Variant: Isthedefaultdatatype;and Changes(theexact)typedependingontheparticularscenario. EventhoughVariantisn'tthemostefficientdatatype,it'sprobablythemostdesirableforthisparticularcase.ThereasonforthisisthattheGetSaveAsFilenamemethodcanreturndifferenttypes.Forexample: IftheusercancelstheSaveAsdialogboxdisplayedbytheGetSaveAsFilenamemethod,thereturnedvalueisaBoolean(False). IftheuserprovidesthefilenamethatshouldbeusedtosavetheconvertedPDFfile,GetSaveAsFilenamereturnsaString. Variantisabletohandlebothofthesesituationsandallowsyourmacrotobemoreflexible. Block#2:PdfFilename=Application.GetSaveAsFilename(InitialFileName:=”SampleExcelFileSavedAsPDF5″,FileFilter:=”PDF,*.pdf”,Title:=”SaveAsPDF”) ThisstatementassignsavaluetothePdfFilenamevariabledeclaredinblock#1above. ThevaluethatisassignedtoPdfFilenameisthefilenameprovidedbytheuserthroughtheSaveAsdialog.ThisSaveAsdialogboxisdisplayedbytheApplication.GetSaveAsFilenamemethod. Inthisparticularexample,theGetSaveAsFilenamemethodusesthefollowing3parameters: InitialFileName:Specifiesasuggested/defaultfilename.Inthisexample,thedefaultfilenameis“SampleExcelFileSavedAsPDF5”. FileFilter:Specifiesthefilefilteringcriteria.Intheexampleabove,theonlyfilefiltermakesreferencetoPDFfiles. Title:DeterminesthetitleoftheSaveAsdialogboxthatisactuallydisplayed.InthesampleSave_Excel_As_PDF_8macro,thetitleofthedialogboxis“SaveAsPDF”. ThefollowingscreenshotshowstheSaveAsdialogboxthatisdisplayedbytheGetSaveAsFilenamemethodthatusestheparametersabove.NoticetheeffectofeachoftheargumentsthatIlistabove: Block#3:If…ThenStatement ThethirdmainblockofVBAcodewithinthesampleSave_Excel_As_PDF_8macroisanIf…Thenstatement. Fromabroadperspective,anIf…Thenstatementdoes2things: Step#1:Testswhetheraconditionismet. Step#2:Iftheconditionismet,performsoneaction.Iftheconditionisn'tmet,performsnoaction. TheIf…Thenstatementwithintheexamplemacroabovecanbebrokendownintothe3followingsections: Section#1:IfPdfFilename<>FalseThen. TheconditiontestedbythisparticularstatementiswhetherthevariablePdfFilenameisdifferentfromFalse. Iftheconditionismet(PdfFilenameisn'tFalse),thestatementswithintheIf…Thenblockareexecuted.Thesestatementsaretheonesthatadjustthepagesetup(asexplainedinmacroexample#3above)andexporttheactiveworksheettoaPDFfile(asIexplaininmacroexamples#1and#2above). Iftheconditionisn'tmet(PdfFilenameisFalse),thestatementswithintheIf…Thenblockaren'texecuted.Insuchacase,executionofthemacroendswithouttheExcelworksheetbeingconvertedtoPDF. AsIexplainabove,iftheuseroftheVBAapplicationcancelstheSaveAsdialogbox,theGetSaveAsFilenamemethodreturnsFalse.Therefore,PdfFilenameisFalsewhenevertheusercancelstheSaveAsdialogbox.Insuchcases,youprobablydon'twanttoproceedwiththeprocessofsavingtheExcelfileasPDF. Section#2:With…EndWithStatementAndWorksheet.ExportAsFixedFormatMethod. ThesestatementsareexecutedwhentheconditiontestedbytheIf…Thenstatementabove(PdfFilename<>False)ismet. Iexplainallofthesestatementsingreatdetailwithinthepreviousmacroexamples(hereandhere). OneoftheserowsofVBAcodeis,however,notexactlythesameasinthepreviousexamples: Filename:=PdfFilename ThisisthelinethatspecifiesthevalueoftheFilenameparameteroftheExportAsFixedFormatmethod.Inotherwords,thislineistheonethatspecifiesthefilenameofthePDFfilethatresultsfromtheconversion. Inthisparticularcase,thefilenamethatisassignedtothePDFfileisthevalueofthePdfFilenamevariable.Inturn,thevalueofthePdfFilenameisdeterminedbytheApplication.GetSaveAsFilenamemethodinblock#2above. Asaconsequenceofthis,thefilenameissimplytheonethatisspecifiedbytheuseroftheapplicationbyusingtheSaveAsdialogdisplayedbytheGetSaveAsFilenamemethod. Forexample,inthesituationdisplayedinthefollowingscreenshot,ifyoupresstheSavebutton(onthelowerrightcornerofthedialogbox),thePDFfileissavedunderthefilename“SampleExcelFileSavedAsPDF5”. Section#3:EndIf. ThelastrowoftheIf…Thenstatement(andthesamplemacroingeneral)signalstheendoftheIf…Thenstatement. HowToSaveEachWorksheetAsASeparatePDFUsingVBA:HowToUseLoopsWithTheExportAsFixedFormatMethod(Examples#9And#10) Asageneralrule,wheneveryouwanttosavealloftheworksheetswithinanExcelworkbook,youcansimplyusetheWorkbook.ExportAsFixedFormatmethod.TheexplanationsIprovidethroughoutthisExceltutorial(particularlythefirstsection)aregenerallyapplicabletotheWorkbook.ExportAsFixedFormatmethod. Forexample,thefollowing(verysimple)macro(Save_Excel_As_PDF_9)istheequivalenttotheoneinexample#1above. Thedifferencebetweenthe2macrosisthat: Themacroinexample#1uses(i)theApplication.ActiveSheetpropertytoreturntheactivesheetand(ii)theWorksheet.ExportAsFixedFormatmethodtoexporttheworksheettoaPDFfile. Theexampleinthissectionuses(i)theApplication.ActiveWorkbookpropertytoreturntheactivesheetand(ii)theWorkbook.ExportAsFixedFormatmethodtoconvertthewholeworkbooktoPDF. However,whenyouusetheWorkbook.ExportAsFixedFormatmethod,alltheworksheetsareexportedtothesamePDFfile.Thismaybewhatyouwantincertainsituations. However,inothersituations,youmaywanttosaveeachworksheetwithinaworkbookinaseparatefile.YoucanachievethisbyworkingwiththeWorksheet.ExportAsFixedFormatmethodandloops. InthecontextoftheExportAsFixedFormatmethodandsavingExcelfilesasPDF,thefollowing2arethemainthingsyoumustunderstandinconnectiontoloops: Thetermlooping,inverybroadterms,makesreferencetotheexecutionofcertainstatementsseveral(morethan1)time. LoopshelpyoutosaveeachworksheetasaseparatePDFfilebecauseofthewaytheyproceed.Moreprecisely,suchaloopproceeds(veryroughly)asfollows: Step#1:ExecutesallofthestatementsthatsaveanExcelworksheetasPDF. Step#2:Movesontothenextworksheet. Step#3:Goesbacktostep#1above.Inotherwords,executes(again)allofthestatementsthatsavetheExcelworksheetasPDF. Whenproperlystructured,aloopallowsyourVBAcodetogothrougheverysingleworksheetandsavesitasaseparatePDFfile. Let'stakealookatamacrothatcombinestheExportAsFixedFormatmethodwithloopsinordertosaveeachworksheetasaseparatePDFfile: NoticehowmostoftheVBAcodeinthesampleSave_Excel_As_PDF_10macrois(oncemore)substantiallythesameasthatofpreviousmacroexamplesinthistutorial.Thefollowingscreenshotshowsthenewitems,whichIexplainfurtherbelow. Let'stakeacloserlookateachoftherowsthatcontainnewitemswithinthissamplemacro: Row#1:DimiCounterAsInteger ThisstatementdeclaresavariablecallediCounter.TheselecteddatatypeisIntegerwhich,asexplainedinthistutorial,canstoreintegersbetween-32,768and32,767. TheiCountervariableisusedasaloopcounter,atopicIexplainfurtherwhencoveringthefollowingrows. Row#2:ForiCounter=1ToWorksheets.Count Thisistheopeningstatementfortheloop.Inthisparticularexample,IuseaFor…Nextloop. Thisis,however,nottheonlytypeofloopyoucanuseforthesepurposes.Youcan,forexample,(also)usetheForEach…NextloopwhenexportingeachworksheettoaseparatePDFfile. Theinitialvalueofthecounteris1(iCounter=1).Theendvalueofthecounter,whichdetermineswhentheloopingstops,isspecifiedbytheSheets.Countproperty.ThevaluereturnedbyWorksheets.Countisthenumberofworksheets. EachtimetheVBAcodegoesthroughtheloop,thevalueoftheiCountervariableincreasesby1. Asaconsequenceoftheabove,themacroloopsanumberoftimesequaltothenumberofworksheetsintheactiveworkbook. Row#3:WithWorksheets(iCounter).PageSetup AsIexplainabove,thisistheopeningstatementoftheWith…EndWithblockthatadjuststhepagesetup.AllofthestatementswithinaWith…EndWithblock(rows#4to#10)makereferencetotheobjectthatappearsinthisstatement(Worksheets(iCounter).PageSetup). Inpreviousexamples,thisstatementmadereferencetothepagesetupsettingsoftheactivesheetbymakingreferenceto“ActiveSheet.PageSetup”insteadof“Worksheets(iCounter).PageSetup”. Thedifferencebetweenbothstatementsis,therefore,onthespecificWorksheetobjectusedwithinthesyntaxoftheWorksheet.PageSetupproperty. ActiveSheetmakesreferencetothecurrentactiveworksheet. Worksheets(iCounter)makesreferencetotheiCounter-thelementoftheWorksheetscollection.TheiCountervariableinthestatementaboveisaworksheetindexnumber.ThespecificnumberisdeterminedbyhowmanytimestheVBAcodehasgonethroughtheloop.Therefore: Onthefirstloop,iCounterisequalto1.Therefore,Worksheets(iCounter)makesreferencetothefirstworksheetintheworkbook(Worksheets(1)). Thesecondtime,iCounterisequalto2.Insuchacase,Worksheets(iCounter)istheequivalentofWorksheets(2)andreferstothesecondworksheetwithintheworkbook. …andsoon,until… Onthelastloop,iCounterisequaltothenumberofworksheetsintheworkbook.Therefore,Worksheets(iCounter)makesreferencetothelastworksheetintheworkbook. Rows#4To#11:With…EndWithBlock IexplainRows#4to#11inexample#3above. ThepurposeofthisblockistoadjusttherelevantpropertiesofthePageSetupobjectfortherelevantworksheet(asdeterminedbytheloop). Rows#12To#20:Worksheets(iCounter).ExportAsFixedFormat Iexplain,indetail,thisstatementandalltheparametersoftheExportAsFixedFormatmethodusedthereininaprevioussectionofthistutorial. Thereare,however,2rowsthatdifferfromwhatappearsinthepreviousexamples: Row#12:Worksheets(iCounter).ExportAsFixedFormat. Row#14:Filename:=ActiveWorkbook.Path&“\”&Worksheets(iCounter).Name. Inbothcases,thedifferenceisintheuseoftheWorksheetscollectionandanindex(Worksheets(iCounter))torefertotherelevantworksheet. Thereasonforthisis,asIexplainabove,relatedtotheFor…Nextloop.Moreprecisely: Thestatement“Worksheets(iCounter).ExportAsFixedFormat”exportstherelevantsheet(asdeterminedbythecurrentloopnumber)toPDF. Theuseof“Worksheets(iCounter).Name”withinthevalueoftheFilenameparametersetsthenameoftheresultingPDFfiletobeequaltothenameofthecurrentworksheet(whichdependsonthecurrentloopnumber).ThisisverysimilartotheFilenameparametervalueusedinexample#6above,wherethenameoftheactivesheetisusedtonametherelevantPDFfile.However,inthisparticularmacro(Save_Excel_As_PDF_10),theuseofaloopmeansthateachworksheetissavedunderitsownworksheetname. Conclusion AfterreadingthisExceltutorial,you'rereadytostartcreatingmacrosforpurposesofsavingExcelfilesasPDF. ThisExcelVBASaveasPDFTutorialisaccompaniedbyfilescontainingthedataandmacrosIuseintheexamplesabove.Youcangetimmediatefreeaccesstotheseexamplefilesbyclickingthebuttonbelow. InadditiontohavingaverygoodunderstandingoftheExportAsFixedFormatVBAmethod,you'vealsoseenhowyoucan: CombinetheExportAsFixedFormatmethodwiththePageSetupobjectinordertoadjustthepagesetupattributespriortotheExceltoPDFconversion. UsedifferentmethodsfordeterminingthefilenameofthePDFfileyousave. UseloopsforpurposesofsavingeachworksheetasaseparatePDFfile. Inadditiontotheabove,you'veseen10differentexamplesofVBAcodethatallowyoutosaveExcelfilesasPDFusingseveraldifferentsettingsandparameters. AswithmosttopicsrelatedtoExcelandVBA,I'venotexhaustedthetopicdespitewritingthisverycomprehensivetutorial.Youcanusetheknowledgeandideasyou'vegainedhereinordertocreatedifferentmacrosthatachievesimilarpurposes. Label {} [+] Enteryournamehere...* Enteryouremailhere...* Label {} [+] Enteryournamehere...* Enteryouremailhere...* 6Comments MostVoted Newest Oldest InlineFeedbacks Viewallcomments LoadMoreComments IpublishalotofTutorialsandTrainingResourcesaboutMicrosoftExcelandVBA.HerearesomeofmymostpopularExcelTrainingResources: FreeExcelVBAEmailCourse ExcelMacroTutorialforBeginners ExcelPowerQuery(GetandTransform)TutorialforBeginners ExcelKeyboardShortcutCheatSheet ExcelResources About Contact TermsandConditions PrivacyPolicy LimitofLiabilityandDisclaimerofWarranty AffiliateDisclosure Insert