Copying or moving an Excel-exported PDF file automatically to a specific folder location or local directory can be very easily achieved if you try to run a customized VBA macro program in Microsoft Excel, which essentially enables the user to freely select any desired file and folder location for output purpose. This capability or feature can be particularly useful for any file copying or transferring process. And this can just be simply done by creating an instance of the Windows explorer and recording both the selected path location and entered filename.

One simple applicable scenario could be that, when a user has multiple PDF files to be manually selected and copied to a specific folder location. 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 location. These retrieved user inputs from the dialog window can then be used to manipulate the file automatically for any desired individual purposes.

As shown in the sample image below, cell “D2” contains the value of default filename and cell “D3” contains the value of the default folder location, in which are to be defined later in the Excel macro file.

Pressing the “Save PDF Report” button will then show the following dialog box or window, commonly known as the “Save File Dialog”, which is to be driven by a visual basic application method called “GetSaveAsFilename”. Please read that article for more on the topic.

Whatever are the values of both the defined path location and filename provided in cell “D2” and “D3”, they will become the default values to be displayed on the dialog window. To change the default value for the filename, the user could enter any other desired filename on the “File name” input field. Meanwhile, path location can also be changed by navigating on to the folder selection panel from the dialog window.

As a side note, be aware that the method “GetSaveAsFilename” will not actually perform the action of saving for any file. It only helps to retrieve and return the filename and folder location, which are to be selected by the user from the dialog window, as a series of string value.

Now, let’s take a look at the related VBA macro code.

Sub GetFilePath_Click()

Dim FileAndLocation As Variant
Dim strPathLocation As String
Dim strFilename As String
Dim strPathFile As String

strPathLocation = Worksheets("05282017").Range("D3").Value
strFilename = Worksheets("05282017").Range("D2").Value
strPathFile = strPathLocation & strFilename

FileAndLocation = Application.GetSaveAsFilename _
(InitialFileName:=strPathLocation & strFilename, _
filefilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")

'This is the concatenated value of selected folder location and filename
MsgBox FileAndLocation

End Sub

Line# 8 – We define the value of default path location onto cell “D3” and store this value into a variable named as “strPathLocation”

Line# 9 – We define the value of default filename onto cell “D2” and store this value into a variable named as “strFilename”

Line# 10 – We concatenate the values from cell “D3” and “D2” and store this concatenated value into a variable named as “strPathFile”

Line# 12 – We invoke the “GetSaveAsFilename” method to display the dialog window, where we can actually enter our desired filename and also navigate our desired folder location.

Explanation for the parameters embedded in the “GetSaveAsFilename” method:

Application.GetSaveAsFilename ( InitialFilename , FileFilter )

InitialFilename – This is where the concatenated path location and filename will be defined.

FileFilter – This defines the type of file extension to be filtered throughout the dialog window. Notice that here the “Filefilter” value is defined as “PDF files”, which means that the dialog box will only display any files with an extension of “.PDF” or PDF files. The “FileFilter” parameter is not limited to just one extension, but it can also accept multiple file-type extensions, which are legitimately defined, such as Microsoft Excel, Access, Word, etc.

Another applicable example could be that, when an accountant has a customized VBA macro program in Microsoft Excel for tax refund processing and he/she needs to print out the completed tax form and save the file as PDF file format.  The same can also be done for a Word document to print it as a PDF.

After the VBA macro program generates the report, it will open or display a dialog window, prompting the user to enter his/her desired filename and also folder location.

Below is the related sample VBA program code:

Sub SaveAsPDF_Click()
Dim FileAndLocation As Variant
Dim strPathLocation As String
Dim strFilename As String
Dim strPathFile As String

strPathLocation = Worksheets("05282017").Range("D3").Value
strFilename = Worksheets("05282017").Range("D2").Value
strPathFile = strPathLocation & strFilename

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strPathLocation & strFilename & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub

Line# 7 – We define the value of default path location onto cell “D3” and store the value into a variable named  as “strPathLocation”

Line# 8 – We define the value of default filename onto cell “D2” and store the value into a variable named as “strFilename”

Line# 9 – We concatenate the values from cell “D3” and “D2” and store the concatenated value into a variable named as “strPathFile”

Line# 11 – We invoke the “ExportAsFixedFormat” method in order to ouput the active Excel worksheet into a specific file format, in which here is to be defined as PDF file.

Explanation for the parameters embedded in the “ExportAsFixedFormat” method:

Worksheet.ExportAsFixedFormat ( Type , Filename , Quality , IncludeDocProperties , IgnorePrintAreas , OpenAfterPublish )

Type – Set to xlTypePDF for PDF file output format

Filename – This is the concatenated value of the defined path location and filename as shown in the previous example

Quality – Set to “xlQualityStandard” by default

IncludeDocProperties – Set to “True”

IgnorePrintAreas – Set to “False”

OpenAfterPublish – Set to “False”

These parameter values can certainly be useful when setting up the attribute or property of generated PDF format. This particular example will store the PDF file on to the user-defined folder location but it can be stored to any folder location through a dialog window with input fields where the user will be asked to select or browse the desired folder location or local directory.

After the code is executed completely, the output result will look like the following image: