How to Save a PDF File with a Specific User-Defined Path in ...

A simple VBA macro program script will be able to open or display a dialog window, asking the user to enter both the filename and folder ... HowtoSaveaPDFFilewithaSpecificUser-DefinedPathinExcelVBA CopyingormovinganExcel-exportedPDFfileautomaticallytoaspecificfolderlocationorlocaldirectorycanbeveryeasilyachievedifyoutrytorunacustomizedVBAmacroprograminMicrosoftExcel,whichessentiallyenablestheusertofreelyselectanydesiredfileandfolderlocationforoutputpurpose.Thiscapabilityorfeaturecanbeparticularlyusefulforanyfilecopyingortransferringprocess.AndthiscanjustbesimplydonebycreatinganinstanceoftheWindowsexplorerandrecordingboththeselectedpathlocationandenteredfilename. Onesimpleapplicablescenariocouldbethat,whenauserhasmultiplePDFfilestobemanuallyselectedandcopiedtoaspecificfolderlocation.AsimpleVBAmacroprogramscriptwillbeabletoopenordisplayadialogwindow,askingtheusertoenterboththefilenameandfolderlocation.Theseretrieveduserinputsfromthedialogwindowcanthenbeusedtomanipulatethefileautomaticallyforanydesiredindividualpurposes. Asshowninthesampleimagebelow,cell“D2”containsthevalueofdefaultfilenameandcell“D3”containsthevalueofthedefaultfolderlocation,inwhicharetobedefinedlaterintheExcelmacrofile. Pressingthe“SavePDFReport”buttonwillthenshowthefollowingdialogboxorwindow,commonlyknownasthe“SaveFileDialog”,whichistobedrivenbyavisualbasicapplicationmethodcalled“GetSaveAsFilename”.Pleasereadthatarticleformoreonthetopic. Whateverarethevaluesofboththedefinedpathlocationandfilenameprovidedincell“D2”and“D3”,theywillbecomethedefaultvaluestobedisplayedonthedialogwindow.Tochangethedefaultvalueforthefilename,theusercouldenteranyotherdesiredfilenameonthe“Filename”inputfield.Meanwhile,pathlocationcanalsobechangedbynavigatingontothefolderselectionpanelfromthedialogwindow. Asasidenote,beaware thatthemethod“GetSaveAsFilename”willnotactuallyperformtheactionofsavingforanyfile.Itonlyhelpstoretrieveandreturnthefilenameandfolderlocation,whicharetobeselectedbytheuserfromthedialogwindow,asaseriesofstringvalue. Now,let’stakealookat therelatedVBAmacrocode. SubGetFilePath_Click() DimFileAndLocationAsVariant DimstrPathLocationAsString DimstrFilenameAsString DimstrPathFileAsString strPathLocation=Worksheets("05282017").Range("D3").Value strFilename=Worksheets("05282017").Range("D2").Value strPathFile=strPathLocation&strFilename FileAndLocation=Application.GetSaveAsFilename_ (InitialFileName:=strPathLocation&strFilename,_ filefilter:="PDFFiles(*.pdf),*.pdf",_ Title:="SelectFolderandFileNametosave") 'Thisistheconcatenatedvalueofselectedfolderlocationandfilename MsgBoxFileAndLocation EndSub   Line#8–Wedefinethevalueofdefaultpathlocationontocell“D3”andstorethisvalueintoavariablenamedas“strPathLocation” Line#9–Wedefinethevalueofdefaultfilenameontocell“D2”andstorethisvalueintoavariablenamedas“strFilename” Line#10–Weconcatenatethevaluesfromcell“D3”and“D2”andstorethisconcatenatedvalueintoavariablenamedas“strPathFile” Line#12–Weinvokethe“GetSaveAsFilename”methodtodisplaythedialogwindow,wherewecanactuallyenterourdesiredfilenameandalsonavigateourdesiredfolderlocation. Explanationfortheparametersembeddedinthe“GetSaveAsFilename”method: Application.GetSaveAsFilename(InitialFilename,FileFilter) InitialFilename–Thisiswheretheconcatenatedpathlocationandfilenamewillbedefined. FileFilter–Thisdefinesthetypeoffileextensiontobefilteredthroughoutthedialogwindow.Noticethatherethe“Filefilter”valueisdefinedas“PDFfiles”,whichmeansthatthedialogboxwillonlydisplayanyfileswithanextensionof“.PDF”orPDFfiles.The“FileFilter”parameterisnotlimitedtojustoneextension,butitcanalsoacceptmultiplefile-typeextensions,whicharelegitimatelydefined,suchasMicrosoftExcel,Access,Word,etc. Anotherapplicableexamplecouldbethat,whenanaccountanthasacustomizedVBAmacroprograminMicrosoftExcelfortaxrefundprocessingand he/sheneedstoprintoutthecompleted taxformandsavethefileasPDFfileformat. ThesamecanalsobedoneforaWorddocumenttoprintitasaPDF. AftertheVBAmacroprogramgeneratesthereport,itwillopenordisplayadialogwindow,promptingtheusertoenterhis/herdesiredfilenameandalsofolderlocation. BelowistherelatedsampleVBAprogramcode: SubSaveAsPDF_Click() DimFileAndLocationAsVariant DimstrPathLocationAsString DimstrFilenameAsString DimstrPathFileAsString strPathLocation=Worksheets("05282017").Range("D3").Value strFilename=Worksheets("05282017").Range("D2").Value strPathFile=strPathLocation&strFilename ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=_ strPathLocation&strFilename&".pdf"_ ,Quality:=xlQualityStandard,IncludeDocProperties:=True,IgnorePrintAreas_ :=False,OpenAfterPublish:=False EndSub Line#7–Wedefinethevalueofdefaultpathlocationontocell“D3”andstorethevalueintoavariablenamed as“strPathLocation” Line#8–Wedefinethevalueofdefaultfilenameontocell“D2”andstorethevalueintoavariablenamedas“strFilename” Line#9–Weconcatenatethevaluesfromcell“D3”and“D2”andstoretheconcatenatedvalueintoavariablenamedas“strPathFile” Line#11–Weinvokethe“ExportAsFixedFormat”methodinordertoouputtheactiveExcelworksheetintoaspecificfileformat,inwhichhereistobedefinedasPDFfile. Explanationfortheparametersembeddedinthe“ExportAsFixedFormat”method: Worksheet.ExportAsFixedFormat(Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, OpenAfterPublish) Type–SettoxlTypePDFforPDFfileoutputformat Filename–Thisistheconcatenatedvalueofthedefinedpathlocationandfilenameasshowninthepreviousexample Quality–Setto“xlQualityStandard”bydefault IncludeDocProperties–Setto“True” IgnorePrintAreas–Setto“False” OpenAfterPublish–Setto“False” TheseparametervaluescancertainlybeusefulwhensettinguptheattributeorpropertyofgeneratedPDFformat.ThisparticularexamplewillstorethePDFfileontotheuser-definedfolderlocationbutitcanbestoredtoanyfolderlocationthroughadialogwindowwithinputfieldswheretheuserwillbeaskedtoselectorbrowsethedesiredfolderlocationorlocaldirectory. Afterthecodeisexecutedcompletely,theoutputresultwilllooklikethefollowingimage: AbouttheauthorJesusBujaue 4thoughtson“HowtoSaveaPDFFilewithaSpecificUser-DefinedPathinExcelVBA” LeaveaReplyCancelreplyYouremailaddresswillnotbepublished.Requiredfieldsaremarked*Comment*Name* Email* Website RecentPosts Excel HowtoSplitSpreadsheetsinExcelUsingVBA 0Comments Subs HowtoExitaSubinVBAWhenYouEncounterErrors 0Comments VBA Guide:TheDocumentObjectModelinVBA 0Comments VBA HowtoUsePasteSpecialinVBA(Examples) 1Comment Loops Guide:DoUntilLoopsinVBA 0Comments PopularPosts 1 FileandFolders FindandListallFilesandFoldersinaDirectory 43Comments 2 Excel ExcelVBA,FindandListAllFilesinaDirectoryanditsSubdirectories 33Comments 3 Excel VBAExcel,WritingtoaTextFile 26Comments 4 Excel ExcelVBAOpenFileDialog 25Comments 5 Excel ListAllFilesinaFolderandCreateHyperlinkstoEachFile,ExcelVBA 23Comments 6 Automation VBAWord,SplitWordFileintoMultipleFiles(EveryXPages) 19Comments 7 DataValidation ExcelVBADropDownListsUsingDataValidation 18Comments 8 Excel ExcelVBA,SaveRange/CellsasJPEG 17Comments Ourprivacypolicy