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
- Open the workbook that you need to input the file names.
- And then press the shortcut keys “Alt +F11” on the keyboard to open the Visual Basic editor.
- After that, click the tab “Insert” in the toolbar.
- Next choose the option “Module” in the drop-down list. Therefore, you have inserted a new module in the editor.
- 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.
- 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.
- 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.
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