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

The article will show you how to copy PDF file names into Excel. Sometimes we may require classifying the files in a folder by their Extension and storing them in a document. Doing this with Excel’s features and functions can save us a lot of time if the folder has lots of files in it. Here, we’ll be focusing on PDF files. Let’s stick to this article to understand how to copy PDF file names into an Excel workbook.

How to Copy PDF File Names into Excel: 9 Efficient Approaches

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.

The article contains 7 efficient methods on this topic. Let’s go through them.

1. Using ‘Copy As Path’ Feature and Excel Formula to Copy PDF File Names

The general approach to copy the names of PDF files is to use the Copy As Path feature of the windows operating system. Please follow the instructions below.

Steps:

• First, have a look at the folder that contains some files including PDF

• Next, copy one of the PDF files as Path.

• After that, select an Excel cell and paste this Path name by pressing CTRL+V.

• You can use this Copy Path feature for multiple files too.
• For that reason, select the PDF files of your folder and click on Copy Path.

• Similarly, paste these names into the Excel sheet by pressing CTRL+V.

• You can see that these are file locations, not their names. To extract their names, we will use the following formula in cell C5. This will show us the PDF file name of the corresponding path.

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

• After that, use the Fill Handle to AutoFill the lower cells.

Thus you can copy PDF file names into Excel by using the Copy Path feature and formulas.

2. Applying Excel Find & Replace Feature to Copy PDF File Names

We can also gain the same output as Method 1 by using the Find & Replace feature of Excel. Let’s go through the process below for a better understanding.

Steps:

• First, follow the steps to copy the PDF File Paths using the Copy Path feature as we discussed in Method 1.

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

• Thereafter, 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.
• Later, select Replace All.

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

Thus you can copy the PDF file names using Copy Path and Find & Replace features.

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

3. Using Google Search Bar to Copy PDF File Names in Excel

Another efficient way to copy any file names as well as PDF file names into Excel is to apply the Google Search Bar. The advantage of this method is that you will get detailed information about these files like when they have been modified or their sizes. Please follow the description below.

Steps:

• First, go to the folder that contains these PDF
• Next, copy the Folder Link.

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

• Thereafter, open the browser and paste this link into the search bar.
• Next, press the ENTER button
• After that, all the files in the corresponding folder link will appear.
• Later, select the PDF file names and press CTRL+C to copy them.

• Next, just paste them in the Excel sheet. I pasted them as Values for convenience.

Thus, you can copy PDF file names in an Excel sheet using the google search bar.

4. Implementing User Defined Function to Copy PDF File Names

We can also use Microsoft Visual Basic for Applications (VBA) to copy PDF file names in Excel. Here, we will create a User Defined Function (UDF) which will return the PDF files in the corresponding folder.

Steps:

• First, go to the Developer Tab and then select Visual Basic.

• After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.

• Now, type 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

Here, 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.

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

• Next, go back to your sheet and type the following formula in cell B5, and 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.

• After that, use the Fill Handle to AutoFill the lower cells. You will see the PDF files now.

Thus you can copy PDF file names using a UDF by VBA.

5. Applying User Defined Function to Copy Files and Search by PDF Extension

We can follow a similar process to Method 4 to copy PDF files in the Excel sheet. But this time, we will create an option where users can search the files in the folder by the file extension. Please go through the following instructions.

Steps:

• First, follow the steps of Method 4 to open a VBA module.
• After that, type 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

Here, 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.

• Next, go back to the Excel sheet and type 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.

• Thereafter, drag the Fill Icon downwards 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.

Thus you can copy PDF file names in Excel by searching them with the pdf extension.

Read More: How to Copy from PDF to Excel Table

6. Copying File Names by Excel VBA Without User Defined Function

This method will return the files in a folder without the help of a UDF that we used in Methods 4 and 5. We will use a simple VBA code to open the Directory and select our desired folder to copy the items in it. Let’s have a look at the procedure below.

Steps:

• First, follow the steps of Method 4 to open a VBA module.
• Next, type 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.

• After that, go back to your sheet and run the Macro.

• Later, the Macro opens the directory and we should select the folder whose file names are going to be copied.
• After that, just click OK.

• Thereafter, we will see the detailed information about the file names in our Excel sheet.

• To copy the PDF file names only, we will use a Filter to keep them. For that purpose, select the Header Row (B4:D4) and press CTRL+SHIFT+L.

• After that, click on the drop down icon beside the File Extension column and uncheck txt and docx.

• Finally, you will be able to see the PDF file names

Thus you can copy the PDF file names in Excel without using a UDF in VBA.

7. Copying PDF File Names by Excel Functions and Name Manager

If you don’t want trouble using VBA, you can combine Excel functions to copy PDF file names. Here’s the description below.

Steps:

• First, copy the file location link following the process of Method 3.
• After that, paste it in the Excel sheet and type “\*” beside the location.

• Next, we will create a Named Range for cell B5. For that purpose, select Formulas >> Name Manager.
• Thereafter, in the Name Manager window, click on New.

• Later, give a name for this Named Range. Here, I named it PDFFileList.
• After that, type 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.

• Thereafter, close the Name Manager window and type the following formula in B8 and press the ENTER button. Notice that the formula is similar to the one where we used 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.

• Next, use the Fill Handle to AutoFill the lower cells with the PDF file names.

Thus you can copy PDF file names using Excel functions and Named Manager.

8. Copy PDF File Names with Hyperlinks

We can also copy the PDFfile names or any file type with their respective hyperlink in the Excel sheet. The advantage is that you can open a file automatically once the names are stored in the sheet.

Steps:

• First, follow Method 7 to store the file location in B5 with slash and wild card symbols (\*). Also, create a Named Range for this cell.
• After that, use the following formula and press ENTER. You will see the first file in pdf filesfolder.

The name of the Named Range here is PDFList.

• Later, apply the Fill Handle to AutoFill the lower cells with other file names.

• Click on any of the file names to open so you can understand how this works. Here I clicked on PDF doc1.pdf and it opened the file.

• If you want to display only the names of the PDFfiles, just type pdfright to the wild card symbol in cell B5. It will show you the PDFfiles only. But you may not be able to access these files this time.

Thus you can copy the PDFfiles and also open them while necessary.

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. 

9. Copying Both PDF Files and Subfolder Names

Here, I’ll show you an additional method to help you on a similar matter. Sometimes, you may need the files in a subfolder too. In order to copy the files in a subfolder or subfolders, please go through the following description carefully.

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.

• First, search with a Full Stop (.) in the Search Bar of the folder.
• After that, select all the files and the folder by pressing CTRL+A and copy them as Path.

• Next, just go back to your 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.

• Finally, apply a Find & Replace command to extract the file names only. We discussed the process in Method 2.

By following this approach, you can copy the PDF file names and subfolder files simultaneously in the Excel sheet.

Download Practice Workbook

Conclusion

In the end, we can surmise that you will learn the best possible ways to copy PDF file names in Excel after reading this article. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box.

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