How to Copy the List of All File Names in a Folder into Excel Worksheet via VBA

Collecting all the file names in a worksheet is very important to your work. In this article, we will introduce a method to input all the file names into a worksheet by using VBA macro.

In our previous article 2 Methods to Copy the List of All File Names in a Folder into Your Excel Worksheet, we have introduced two methods to list all the file names in a folder into a worksheet. And now we have found another method. You can also use the VBA macro to finish this task. Follow the steps below and see how to use it.

Copy the List of All File Names in a Folder

1. Open the workbook that you need to input the file names.
2. And then press the shortcut keys “Alt +F11” on the keyboard to open the Visual Basic editor.
3. After that, click the tab “Insert” in the toolbar.
4. Next choose the option “Module” in the drop-down list. Therefore, you have inserted a new module in the editor.
5. In this step, copy the following codes into the new module:

Sub ListAllFileNames()
  Dim strTargetFolder As String, strFileName As String, nCountItem As Integer

  '  Initialization
  nCountItem = 1
  strTargetFolder = "C:\Users\Sample\Desktop\Sales Volume" & "\"
  strFileName = Dir(strTargetFolder, vbDirectory)

  '  Get the file name
  Do While strFileName <> ""
    If strFileName <> "." And strFileName <> ".." Then
      Cells(nCountItem, 1) = strFileName
      nCountItem = nCountItem + 1
    End If
    strFileName = Dir
  Loop
End Sub

In this macro, you need to change the target folder according to your actual need. And we will input the file names into the column A in a worksheet. If you need to input the file names into other range, you can also modify the codes.

6. After that, run this macro. You can click the button “Run Sub” in the toolbar or press the button “F5” on the keyboard to run this macro.
7. Next come back to the worksheet. You will find that all the file names in the folder have already appeared in the column A.

Compared with the other two methods in the article 2 Methods to Copy the List of All File Names in a Folder into Your Excel Worksheet, you will see that using VBA macro is very convenient. However, on the other hand, if you are not familiar with Excel VBA, you will meet with errors when modifying the codes. Therefore, you can use one of the three methods according to your actual need.

Pay Attention to Dangerous Software

You will certainly install a lot of software in your computer. And those applications can help you a lot in your work. However, some of them are dangerous applications. Once you install them in your computer, they will collect your personal information. What’s worse, your Excel files will also be damaged by them. At this moment, uninstall the dangerous software immediately. Besides, you also need to use a recovery tool to repair damaged xlsx file. Thus, you can make sure that your Excel files are safe.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word docx file problem and outlook repair software products. For more information visit www.datanumen.com