How to quickly list the picture names/file names of a folder to Excel cells?
In this tutorial, I will provide some tricks on quickly listing all picture names or file names of a specific folder in an Excel sheet as the screenshot shown.
In Excel, there is no built-in function that can automatically list all picture names of a folder you choose in a cell of a sheet, but here I have a VBA code which can help you quickly solve this job.
1. In Excel, then press + keys to open Microsoft Visual Basic for Applications window.
2. In popping window, click Insert > Module to create a new Module script.
3. Copy below code and paste them to the new Module script window.
Sub PictureNametoExcel() 'UpdatebyExtendoffice Dim I As Long Dim xRg As Range Dim xAddress As String Dim xFileName As String Dim xFileDlg As FileDialog Dim xFileDlgItem As Variant On Error Resume Next xAddress = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Select a cell to place name list:", "Kutools For Excel", xAddress, , , , , 8) If xRg Is Nothing Then Exit Sub Application.ScreenUpdating = False Set xRg = xRg(1) xRg.Value = "Picture Name" With xRg.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 End With xRg.EntireColumn.AutoFit Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker) I = 1 If xFileDlg.Show = -1 Then xFileDlgItem = xFileDlg.SelectedItems.Item(1) xFileName = Dir(xFileDlgItem & "\") Do While xFileName <> "" If InStr(1, xFileName, ".jpg") + InStr(1, xFileName, ".png") + InStr(1, xFileName, ".img") + InStr(1, xFileName, ".ioc") + InStr(1, xFileName, ".bmp") > 0 Then xRg.Offset(I).Value = xFileDlgItem & "\" & xFileName I = I + 1 End If xFileName = Dir Loop End If Application.ScreenUpdating = True End Sub
4. Press F5 key, then a dialog pops out for you to select a cell to place the picture names.
5. Click OK to continue selecting the folder you want to list the picture names in Browse dialog.
6. Click OK, now all picture names in the selected folder have been list in Excel.
Note: In the VBA code, you can jpg/png/… in the code string If InStr(1, xFileName, ".jpg") + InStr(1, xFileName, ".png") + InStr(1, xFileName, ".img") + InStr(1, xFileName, ".ioc") + InStr(1, xFileName, ".bmp") > 0 Then to the file extension you need, for example, if you want to list file names of the file in doc format, only change the code string to If InStr(1, xFileName, ".doc") > 0 Then.
If you usually need to list one or all formats’ file names of a folder in Excel cell in your daily work, the code is complex and no easy to handle, but, with below way, you can quickly solve this job.
Do You Want To Have A Pay Raise and Much Time To Accompany With Family?
Office Tab Enhances Your Efficiency By 50% In Microsoft Office Working Right Now
Unbelievable, working at two or more documents is easier and faster than working at one.
Compareed with well-known browsers, the tabbed tool in Office Tab is more powerful and more efficient.
Reduce hundreds of mouse-clicks and keyboard typing every day for you, say goodbye to the mouse hand now.
If you usually work at multiple documents, Office Tab will be a great time-saver for you.
30-day free trial, no credit card required.
Here is a utility – Filename List in Kutools for Excel, which can
>> list all file names in all file formats of a folder in a new sheet;
>> list the file names in one or several file formats you specify to Excel;
>> list file names including subfolders and hidden files and folders to Excel.
300 advanced tools of Kutools for Excel solve 80% Excel tasks in seconds, pull you out of the thousands of mouse-clicks.
Easily deal with 1500 working scenarios, no need to waste time for searching solutions, have much time to enjoy your life.
Improve 70% productivity for 110000+ highly effective people every day, of course including you.
No longer to be tormented by painful formulas and VBA, give your brain a rest and joyful working mood.
60-day free trial with full features, 60-day money back without reasons.
A Better Body Creates A Better Life.
After free installing Kutools for Excel, please do as below:
1. Enable the workbook you want to list the file names, then click Kutools Plus > Import/Export > Filename List.
2. In the Filename List dialog, firstly, click to select a folder that you want to list its file names, then you can check the checkboxes of Include files in subdirectories and Include hidden files and folders as you need.
3. Then select the file format you want to list the names in Files type section.
4. Choose the size unit as you need from the drop down list in File size unit section, if you want to create a link to the file names, check Create hyperlinks option.
5. Click Ok, a new sheet will be created to list the file names and some file information.
Tip: if you want to list all sheet names in Excel, you can apply Kutools for Excel’ Create List of Sheet Names utility, it will create a new sheet to list all linkable sheet names.
Kutools for Excel:200 + useful handy tools, simplifying the complicated tasks in Excel into a few clicks.
How To Make You Stand Out From Crowd, Easily Work And Leisurely Enjoy Life？
Kutools for Excel will averagely improve your 70% efficiency every day, make you have much time to accompany with family and have fun in life.
300 advanced tools solve 80% Excel problems, you no longer struggle to search solutions, more easily complete work.
Easily handle 1500 Excel scenarios, save at least 1 hour for you every day.
Become an Excel expert in 5 minutes, gain peoples’ recognition and opportunity for promotion.
One-click utilities handle batch processing, let you avoid thousands of mouse-clicks, say good-bye to mouse hand.
Get rid of rote memorizing formulas and complex VBA, give a rest to your brain.
110000+ high efficiency officers and 300+ world renowned companies’ choice.
Only take $39 but much worth than $4000 training of other people
60 days free trial without any limitation, full money back in 60-day without any reasons.
Recommended Productivity Tools
Office Tab - Tabbed Browsing, Editing, Managing Of Documents In Microsoft Office 2019 - 2003 And Office 365
A Professional Add-in for Accelerating Excel 2019-2007, shrink hours tasks to seconds
This add-in includes dozens of professional groups, with 300+ options will automate most of your daily tasks in Excel, and increase your productivity at least by 50%. Such as groups of one-click optons and batch conversions.
Now it's your chance to speed up yourself with Kutools for Excel!
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 10 months agoI WAS HAVING THE SAME PROBLEM TANNER, IN THE SCRIPT WHERE IT HAS THE DIFFERENT FILE EXTENSIONS ITS GOING TO GRAB,YOU HAVE TO CAPITALIZE THE FILE EXTENSION ITS LOOKING FOR, FOR EXAMPLE, YOU SAID YOUR FILES ARE ".png", WELL IF YOU CAPITALIZE IT I.E ".PNG" IT MAY WORK.
To post as a guest, your comment is unpublished.· 10 months agoIs it possible that there is something wrong with the code? The file explorer is showing my folder full of images (.png) as empty.
To post as a guest, your comment is unpublished.· 10 months agoIs it possible something is wrong with the code? Because it shows folders full of images (.png) as empty when it pulls up the box to select the photos.