VBA to get list of file names in a folder with last modified date

Question

Hello,

I have been looking for a VBA code that list all the file names in a specific folder with their last modified date or the date when the file were moved in that folder.

I have looked for code on the internet but cant find the one which helps me.

i have the below code which works a bit.

CODE:

Sub GetFileNames()

Dim xRow As Long

Dim xDirect$, xFname$, InitialFoldr$

Dim cell As Range, fso As Object

InitialFoldr$ = "G:\S5GH\Global Finance Hub\Client Management\TORM OWNERS\Processing\PO Related Files\Cancelled PO Mails" '<<< Startup folder to begin searching from

With Application.FileDialog(msoFileDialogFolderPicker)

.InitialFileName = Application.DefaultFilePath & ""

.Title = "Please select a folder to list Files from"

.InitialFileName = InitialFoldr$

.Show

If .SelectedItems.Count <> 0 Then

xDirect$ = .SelectedItems(1) & ""

xFname$ = Dir(xDirect$, 7)

Do While xFname$ <> ""

ActiveCell.Offset(xRow) = xFname$

xRow = xRow + 1

xFname$ = Dir

Loop

End If

End With

Set fso = CreateObject("Scripting.FileSystemObject")

On Error Resume Next

For Each cell In Range(ActiveCell, ActiveCell.Offset(xRow))

cell.Offset(0, 1).Value = fso.getfile(xDirect$ & cell.Value2).DateLastModified

Next cell

Set fso = Nothing

On Error GoTo 0

End Sub

CODE ENDS:

The problem with this code is that.

1. It asks to browse the folder. (Which i dont want to do, It should be a fixed path in the macro itself)
2. I need to run it again and again to refresh.( Is it possible that when a file is moved in the folder it should reflect in excel) if not possible no problem.

Can anyone help me with the correct code.

Thanks in advance.

Answer 1

Hi Alakar

Here is the solution, use this code, It works perfect, I ran it.

Please note portions of code in RED are the ones you can change to suit your needs

Please check your folder path is correct

You must click Microsoft Scripting Runtime on VBA panel as shown below

This is the macro

''*******************************************************************

Sub GetFilesDetails()

' This macro will extract the list of the filenames from a folder as follows

' in column A= Files names

' in column B= Date Created

' in column C= Date Last Accessed

' in column D= Date Last Modified

Dim objFSO As Scripting.FileSystemObject

Dim myFolder As Scripting.Folder

Dim myFile As Scripting.File

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set myFolder = objFSO.GetFolder(“G:\S5GH\Global Finance Hub\Client Management\TORM OWNERS\Processing\PO Related Files\Cancelled\”)

Application.ScreenUpdating = False

''******************************************************************************

' these 2 code lines will clear the old data in Sheet? from columns A:D

' Row 1 is for the appropriate headers only

ThisWorkbook.Sheets("Sheet1").Range(Cells(2, 1), Cells(Rows.Count, 4)).ClearContents

R = 2

''********************************************************************

' Here we get the files details from folder and place them in the appropriate cells

For Each myFile In myFolder.Files

       ThisWorkbook.Sheets("Sheet1").Cells(R, 1).Value = myFile.Name 

       ThisWorkbook.Sheets("Sheet1").Cells(R, 2).Value = myFile.DateCreated

       ThisWorkbook.Sheets("Sheet1").Cells(R, 3).Value = myFile.DateLastAccessed

       ThisWorkbook.Sheets("Sheet1").Cells(R, 4).Value = myFile.DateLastModified

       R = R + 1

Next myFile

'''************Resizing the columns width****************

ThisWorkbook.Sheets**("Sheet1").Columns("A:D**").EntireColumn.AutoFit

Application.ScreenUpdating = True

'' Optional Alert

MsgBox "Updated"

End Sub

 *********************************************************************************

Note2

To run the macro automatically please do as follow in images

Select the Sheet where you want the filename to be listed ie. Sheet1 from the VBA panel

and access to the code of the sheet and select Worksheets and on the procedures Activate

and Call the macro as shown in picture bellow

****This will run the macro automatically every time you select the sheet1

This will be the result on Sheet1 after running the macro

Hope this will help you, Please click as answered of so. I will appreciate it

Thanks

Regards

Jeovany CV

Answer 2

hi Jeovany, 

Thanks for your effort, really appreciate it.

When i run the code, Im getting an error.

Can you see what is the problem.

Answer 3

Hi

All you have to do is type this code line at the beginning of the macro where all the Dims are

Dim R as Long

and that will do

Do let me know if you require any further help on this. Will be glad to help you.Mark question as answered if so

Regards

Jeovany

Answer 4

Hi Jeovany,

Thanks a lot.

it was the option expedite problem. code ran properly after removing it.

Answer 5

Hi Alarkar

Glad to help you.

Please click question as answered if so.

Thanks

Regards

Jeovany