I used this “get file names” macro trick under these scenarios:

• have to rename all files in a folder with specific names
• have to resize picture files with a specific dimension in batch mode for a website

VBA macro code

💡Press Alt + F11 to open VBA editor in Excel

Sub GetFileName() Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Dim objShell As Object Dim objFolder As Object Dim objFile As Variant Dim i As Integer Dim bone As Workbook Set bone = ActiveWorkbook bone.Sheets("Result").Activate 'Create an instance of the FileSystemObject Set objShell = CreateObject("Shell.Application") 'Get the folder object Set objFolder = objShell.Namespace("C:\the folder path") 'loops through each file i = 1 For Each objFile In objFolder.items 'File Name Cells(i + 1, 1).Value = objFolder.GetDetailsOf(objFile, 0) 'File Size Cells(i + 1, 2).Value = objFolder.GetDetailsOf(objFile, 1) 'Dimensions Cells(i + 1, 3).Value = objFolder.GetDetailsOf(objFile, 31) 'Height Cells(i + 1, 4).Value = objFolder.GetDetailsOf(objFile, 164) 'Width Cells(i + 1, 5).Value = objFolder.GetDetailsOf(objFile, 162) 'Horizontal Resolution Cells(i + 1, 6).Value = objFolder.GetDetailsOf(objFile, 161) 'Vertical Resolution Cells(i + 1, 7).Value = objFolder.GetDetailsOf(objFile, 163) i = i + 1 Next objFile Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub

Explanation

GetFileName is this macro name, change it as you like, just keep in mind that no space in between the words of the name

Sub GetFileName()

Pre-setting in order to run the macro faster: make the calculation function in manual way and turn off the screen update

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim as Dimension, to declare variable names and their types, change the names as you like, just keep in mind to keep the same names throughout the macro

Dim objShell As Object
Dim objFolder As Object
Dim objFile As Variant
Dim i As Integer

bone is the name of the workbook in which you trigger this macro, change the name as you like

Dim bone As Workbook
Set bone = ActiveWorkbook

I pre-named one worksheet in this workbook to be ‘Result’ which will be the sheet to consolidate all the file names fetched by the code

bone.Sheets(“Result”).Activate

Create an instance of the FileSystemObject

Set objShell = CreateObject(“Shell.Application”)

Get the target folder object, copy and paste your target folder path in between the double quote

Set objFolder = objShell.Namespace(“C:\the folder path”)

Start to loop through each file in the folder

i = 1
For Each objFile In objFolder.items

All these are the data that will be fetched by the code, just add ‘ at the beginning of each code to turn it into an explanation if it is not necessary to fetch

For example, cells(i+1,1)=cell(A2), the first file name will be put in the cell A2, then its file size will be put in cell B2

‘get file mame
Cells(i + 1, 1).Value = objFolder.GetDetailsOf(objFile, 0)

‘get file Size
Cells(i + 1, 2).Value = objFolder.GetDetailsOf(objFile, 1)

‘get picture dimensions
Cells(i + 1, 3).Value = objFolder.GetDetailsOf(objFile, 31)

‘get picture Height
Cells(i + 1, 4).Value = objFolder.GetDetailsOf(objFile, 164)

‘get pictre Width
Cells(i + 1, 5).Value = objFolder.GetDetailsOf(objFile, 162)

‘get horizontal resolution
Cells(i + 1, 6).Value = objFolder.GetDetailsOf(objFile, 161)

‘get vertical resolution
Cells(i + 1, 7).Value = objFolder.GetDetailsOf(objFile, 163)

i = i + 1
Next objFile

Turn the calculation back to be automatic and turn back on the screen update

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

• files
• for each...next loop
• macro