In this article you will learn how to get the files information from a folder like File Name, File Type, File size, Last modify date etc. in excel sheet. We will use the file system object to create this.
To use the file system object you need to add “Microsoft Scripting Runtime” Reference. To add this Reference go to Visual basic editor > Tools > References > Microsoft Scripting Runtime
We have taken the folder path on excel worksheet Sheet1 cell H1. After clicking the Get Information button, you will get all the available files information in excel as given in below image.
Below is the VBA code get this information in Excel-
Option Explicit Sub Get_Files_Information() Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("Sheet1") Dim fso As New FileSystemObject Dim fo As Folder Dim f As File Set fo = fso.GetFolder(sh.Range("H1").Value) Dim last_raw As Integer For Each f In fo.Files last_raw = sh.Range("A" & Application.Rows.Count).End(xlUp).Row + 1 sh.Range("A" & last_raw).Value = f.Name sh.Range("B" & last_raw).Value = f.Type sh.Range("C" & last_raw).Value = f.Size / 1024 sh.Range("D" & last_raw).Value = f.DateLastModified Next MsgBox "Done" End Sub
Click here to download this Excel workbook.
Watch the step by step video tutorial: