How to Copy PDF File Names into Excel (9 Efficient Methods)

We can copy the names of the PDF files in a folder in various ways. A general presentation of an Excel sheet with PDF file names looks like the picture below.

Method 1 – Using the Copy as Path Feature and Excel Formula to Copy PDF File Names

Steps:

• Go to the folder that contains some files including PDF

• Select a file and choose Copy path in the top ribbon.

• Select an Excel cell and paste this Path name by pressing Ctrl + V.

• You can use this Copy Path feature for multiple files, too, by selecting all the PDF files in your folder and clicking on Copy Path.

• Paste these names into the Excel sheet by pressing Ctrl + V.

• These are file locations, not their names.
• To extract their names, we will use the following formula in cell C5:

The formula uses RIGHT, SUBSTITUTE, and REPT functions to remove the File Path part from B5. They also leave behind 50 spaces.

• Use the Fill Handle to AutoFill the lower cells.

Method 2 – Applying the Find & Replace Feature to Copy PDF File Names

Steps:

• Copy the PDF File Paths using the Copy Path feature as discussed in Method 1.

• Select the range of these file paths, go to Find & Select, and select Replace.

• In the Find and Replace dialog box, type or copy ‘C:\Users\user\Desktop\blog 127\pdf files\’ in the Find what section and keep the Replace with section empty. The text will correspond to the file path on your PC, so you may need to change it.
• Select Replace All.

• This will return the PDF file names in the Excel sheet.

Read More: How to Copy and Paste from PDF to Excel

Method 3 – Using the Google Search Bar to Copy PDF File Names in Excel

Steps:

• Go to the folder that contains the PDF files.
• Copy the Folder Link.

• You can copy this by right-clicking and selecting Copy Address.

• Open the browser and paste the link into the search bar.
• Press the Enter button
• All the files in the corresponding folder link will appear.
• Select the PDF file names and press Ctrl + C to copy them.

• Paste in the Excel sheet. We pasted them as Values for convenience.

Method 4 – Implementing a User-Defined Function to Copy PDF File Names

Steps:

• Go to the Developer tab and select Visual Basic.

• The VBA editor will appear. Select Insert and Module to open a VBA Module.

• Insert the following code in the VBA module.

Function CopyPDFNames(ByVal mn_Folder_Path As String) As Variant
Dim mn_Result As Variant
Dim mn_K As Integer
Dim mn_FileName As Object
Dim mn_FSObj As Object
Dim mn_FolderName As Object
Dim mn_FileNames As Object
Set mn_FSObj = CreateObject("Scripting.FileSystemObject")
Set mn_FolderName = mn_FSObj.GetFolder(mn_Folder_Path)
Set mn_FileNames = mn_FolderName.Files
ReDim mn_Result(1 To mn_FileNames.Count)
mn_K = 1
For Each mn_FileName In mn_FileNames
mn_Result(mn_K) = mn_FileName.Name
mn_K = mn_K + 1
Next mn_FileName
CopyPDFNames = mn_Result
End Function

We created the UDF CopyPDFNames by declaring some Variant, Integer, and Object variables. The CreateObject function here creates a File System Object that helps to define the Folder Path. A For Each Next Loop is used to return the name of each file in that Folder Path.

• Copy the File Path into the Excel sheet. The process of copying the file address link is shown in Method 3.

• Go back to your sheet and use the following formula in cell B5, then press Enter.

The UDF returns the file names of the folder path in B12 with their extensions as a row array. The INDEX and ROW functions are used to return the array elements one by one when copying the formula in the below cells. Here, we are making any error out of consideration by the IFERROR function. The IF and RIGHT functions help to copy only the PDF file names from the folder. As you can see, the B5 cell remains empty as the first file of that folder is not a PDF file.

• Use the Fill Handle to AutoFill the lower cells. You will see the PDF files now.

Method 5 – Applying a User-Defined Function to Copy Files and Search by the PDF Extension

Steps:

• Open a VBA module.
• Use the following code in the module.

Function CopyPDFNamesExt(ByVal mn_Folder_Path As String, mn_File_Extension As String) As Variant
Dim mn_result As Variant
Dim mn_K As Integer
Dim mn_FileName As Object
Dim mn_FSObj As Object
Dim mn_FolderName As Object
Dim mn_FileNames As Object
Set mn_FSObj = CreateObject("Scripting.FileSystemObject")
Set mn_FolderName = mn_FSObj.GetFolder(mn_Folder_Path)
Set mn_FileNames = mn_FolderName.Files
ReDim mn_result(1 To mn_FileNames.Count)
mn_K = 1
For Each mn_FileName In mn_FileNames
If InStr(1, mn_FileName.Name, mn_File_Extension) <> 0 Then
mn_result(mn_K) = mn_FileName.Name
mn_K = mn_K + 1
End If
Next mn_FileName
ReDim Preserve mn_result(1 To mn_K - 1)
CopyPDFNamesExt = mn_result
End Function

We created the UDF CopyPDFNamesExt by declaring some Variant, Integer and Object variables. The CreateObject function here creates a File System Object that helps to define the Folder Path. A For Loop is used to return the name of each file in that Folder Path. It also creates an option to search the files by their extension.

• Go back to the Excel sheet and use the following formula, and press Enter. Follow Method 3 to see the process of copying the Folder Link.

The UDF has two input cell references: one is for returning the PDF file names and another one is for searching them by extensions. The UDF returns the file names of the folder path in B5 with their extensions as a row array. The INDEX and ROW functions are used to return the array elements one by one when copying the formula in the below cells. We will see the first file of the pdf files folder after pressing the ENTER button.

• Drag the Fill Icon down to AutoFill the lower cells by file names.

• As we want to copy only the PDF files, we need to type pdf in B6 and hit Enter.

Read More: How to Copy from PDF to Excel Table

Method 6 – Copying File Names by Excel VBA Without a User-Defined Function

Steps:

• Follow the steps of Method 4 to open a VBA module.
• Use the following code in the module.

Sub CopyWithoutUDF()
Dim mn_FSObj, mn_FolderName, mn_FileName As Object
Dim mn_File_Dialog As FileDialog
Dim mn_File_Path As String
Dim mn_K As Integer
Set mn_File_Dialog = Application.FileDialog(msoFileDialogFolderPicker)
If mn_File_Dialog.Show = -1 Then
    mn_File_Path = mn_File_Dialog.SelectedItems(1)
End If
Set mn_File_Dialog = Nothing
If mn_File_Path = "" Then Exit Sub
Set mn_FSObj = CreateObject("Scripting.FileSystemObject")
Set mn_FolderName = mn_FSObj.GetFolder(mn_File_Path)
ActiveSheet.Cells(4, 2) = "Folder Name"
ActiveSheet.Cells(4, 3) = "File Name"
ActiveSheet.Cells(4, 4) = "File Extension"
mn_K = 4
For Each mn_FileName In mn_FolderName.Files
    mn_K = mn_K + 1
    ActiveSheet.Cells(mn_K, 2) = mn_File_Path
    ActiveSheet.Cells(mn_K, 3) = Left(mn_FileName.Name, InStrRev(mn_FileName.Name, ".") - 1)
    ActiveSheet.Cells(mn_K, 4) = Mid(mn_FileName.Name, InStrRev(mn_FileName.Name, ".") + 1)
Next
End Sub

Here, we created a Macro named CopyWithoutUDF. The code has similar functions and variables as we used to create the UDF in Methods 4 and 5. Except it defines the position of the cells where the Folder, File, and Extension names will be stored by the ActiveSheet.Cells property.

• Go back to your sheet and run the Macro.

• The Macro opens the directory, so select the folder whose file names are going to be copied.
• Click OK.

• We will see the detailed information about the file names in our Excel sheet.

• To copy the PDF file names only, use a Filter to keep them. Select the Header Row (B4:D4) and press Ctrl + Shift + L.

• Click on the drop-down icon next to the File Extension column and uncheck txt and docx.

• You will be able to see the PDF file names.

Method 7 – Copying PDF File Names with Excel Functions and the Name Manager

Steps:

• Copy the file location link following Method 3.
• Paste the location in the Excel sheet and type “\*” after the location.

• Create a Named Range for cell B5. Select Formulas and go to Name Manager.
• In the Name Manager window, click on New.

• Give a name for this Named Range. We named it PDFFileList.
• Use the following formula in the Refers to: section and click OK.

The sheet name here is excel functions and the file path link is in cell B5.

• Close the Name Manager window and use the following formula in B8, then press the Enter button. The formula is similar to the one where we used a User-Defined Function.

The formula will only copy the PDF file names. As the first file is not a PDF file, the B8 cell will remain empty.

• Use the Fill Handle to AutoFill the lower cells with the PDF file names.

Method 8 – Copy PDF File Names with Hyperlinks

Steps:

• Follow Method 7 to store the file location in B5 with slash and wild card symbols (\*).
• Create a Named Range for this cell.
• Use the following formula and press Enter. You will see the first file from the folder.

The name of the Named Range here is PDFList.

• Apply the Fill Handle to AutoFill the lower cells with other file names.

• Click on any of the file names to open it. We clicked on PDF doc1.pdf and it opened the file.

• If you want to display only the names of the PDF files, type pdf right to the wild card symbol in cell B5. This will show you the PDF files only. But you may not be able to access these files this time.

Note:

The methods described in Methods 7 and 8 may not work in the older versions of Excel. You should also check if Macros 4.0 is enabled from Options >> Trust Center >> Trust Center Settings >> File Block Settings. 

Method 9 – Copying Both PDF Files and Subfolder Names

Steps:

• Here we have one subfolder in our observing folder pdf files. We will bring out the file’s name in it in the Excel sheet.

• Search for a Full Stop (.) in the Search Bar of the folder.
• Select all the files and the folder by pressing Ctrl + A and copy them as a Path.

• Go back to the Excel sheet and press Ctrl + V to paste the names of the files and folders.

You can see the Subfolder Reports and the PDF files of the Subfolder (named PDF doc5) are marked respectively.

• Apply a Find & Replace command to extract the file names only as in Method 2.

Download the Practice Workbook

Related Articles

• Copy Table from PDF to Excel with Formatting
• How to Copy from PDF to Excel and Keep Columns

<< Go Back to Import PDF to Excel | Importing Data in Excel | Learn Excel