As per the above code I am getting the the images name in an incrementing order, however the path where the images are being placed are in numeric incrementation.
Kindly suggest how I can keep the same formatting.
Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!
It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo
Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!
Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!
Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.
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 80% 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.
30-day free trial with full features, 30-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 80% 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
30 days free trial without any limitation, full money back in 30-day without any reasons.
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!