Extract All File Names, Paths, and Sizes from a Folder in Excel

How to Extract All File Names, Paths, and Sizes from a Folder in Excel Using VBA

by

It can be frustrating to manually list out all the file names, paths, and sizes from a folder, especially when there are many files to manage. Doing this manually takes up a lot of time and can lead to mistakes.

Thankfully, you can use a VBA script in Excel to handle this for you.

In this guide, I’ll walk you through a simple solution to automatically extract all the file details you need, making the whole process much quicker and easier.

VBA Code:

Sub ExtractFileInfo()
    Dim FolderPath As String
    Dim FileName As String
    Dim FileNameWithoutExt As String
    Dim FileSize As Double
    Dim i As Integer
    Dim FileDialog As FileDialog
    Dim fso As Object
    Dim fileItem As Object
    
    ' Initialize File Dialog to allow user to select folder
    Set FileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    
    If FileDialog.Show = -1 Then
        FolderPath = FileDialog.SelectedItems(1) & "\"
    Else
        MsgBox "No folder selected!", vbExclamation
        Exit Sub
    End If
    
    ' Set up the FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Clear existing data
    Cells.Clear
    
    ' Headers
    Cells(1, 1).Value = "File Name (Without Extension)"
    Cells(1, 2).Value = "File Name (With Extension)"
    Cells(1, 3).Value = "File Path"
    Cells(1, 4).Value = "File Size (KB)"
    
    ' Loop through each file in the selected folder
    i = 2
    FileName = Dir(FolderPath & "*.*")
    
    Do While FileName <> ""
        ' Get file details
        Set fileItem = fso.GetFile(FolderPath & FileName)
        FileSize = fileItem.Size / 1024 ' Convert size to KB
        FileNameWithoutExt = Left(FileName, InStrRev(FileName, ".") - 1)
        
        ' Populate data in the worksheet
        Cells(i, 1).Value = FileNameWithoutExt
        Cells(i, 2).Value = FileName
        Cells(i, 3).Value = FolderPath & FileName
        Cells(i, 4).Value = Format(FileSize, "0.00")
        
        ' Move to next row
        i = i + 1
        
        ' Get next file
        FileName = Dir
    Loop
    
    MsgBox "File information extracted successfully!", vbInformation
End Sub

Download VBA Code

Video Tutorial

Join Our Telegram Group techguruplus telegram group Join Our WhatsApp Group techguruplus whatsapp group
Nazim Khan - Author Image

Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]

Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.

Leave a Comment