Hi, AlexT!
Let me see if we can achieve the goal manually.
1) Place this code within a module (Alt-F11, Insert, Module)
-----
Option Explicit
' constants
' workbooks
Const gksWorkbooks = "Workbooks"
Const gksWorkbooksTable = "WorkbooksTable"
' columns
Const gkiWorkbookgsPath = 1
Const gkiWorkbooksFile = 2
Const gkiWorkbooksCreationDate = 3
Const gkiWorkbooksModificationDate = 4
Const gkiWorkbooksErrors = 5
' declarations
Dim gI As Long, grng As Range, gsPath As String, gsFileName As String
Sub FolderAnalysis()
'
' constants
'
' declarations
Dim fso As Object, fld As Object
'
' start
' folder selection
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose the folder to be analized"
.Show
If .SelectedItems.Count > 0 Then
gsPath = .SelectedItems(1)
Else
If MsgBox("Folder not selected. Use current path?", _
vbApplicationModal + vbQuestion + vbOKCancel + vbDefaultButton2, _
"Warning"
= vbOK Then
gsPath = ThisWorkbook.Path
Else
Exit Sub
End If
End If
End With
If Right$(gsPath, 1) <> Application.PathSeparator Then gsPath = gsPath & Application.PathSeparator
' initialize
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
' .ScreenUpdating = False
End With
' range
Worksheets(gksWorkbooks).Activate
Set grng = Range(gksWorkbooksTable)
With grng
Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
End With
gI = grng.Cells(1, 1).End(xlDown).End(xlDown).End(xlUp).Row
'
' process
' files & folders
Set fso = CreateObject("Scripting.FileSystemObject"
Set fld = fso.getfolder(gsPath)
FolderAnalysisDetail fld
Set fso = Nothing
' sort
With ActiveWorkbook.Worksheets("Workbooks"
.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A:A"
, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("B:B"
, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A:AD"
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C2"
.Select
'
' end
Beep
' terminate
With Application
.Calculation = xlCalculationAutomatic
.CalculateFull
.EnableEvents = True
.ScreenUpdating = True
End With
' save
With ThisWorkbook
.Activate
DoEvents
.Save
End With
End Sub
Sub FolderAnalysisDetail(ByRef fld As Object)
'
' constants
'
' declarations
Dim fldSub As Object, fls As Object, fn As Object
Dim dCreation As Date, dModification As Date
Dim iAttribute As Integer
'
' start
'
' process
' files
On Error Resume Next
With grng
gsPath = fld.Path
For Each fn In fld.Files
gsFileName = fn.Name
dCreation = fn.DateCreated
dModification = fn.DateLastModified
' only files
iAttribute = GetAttr(gsPath & Application.PathSeparator & gsFileName)
If gsFileName <> ActiveWorkbook.Name Then ' And _
(iAttribute And vbArchive = vbArchive Or _
iAttribute And vbNormal = vbNormal) Then
' not actual
gI = gI + 1
.Cells(gI, gkiWorkbookgsPath).Value = gsPath
.Cells(gI, gkiWorkbooksFile).Value = gsFileName
.Cells(gI, gkiWorkbooksCreationDate).Value = dCreation
.Cells(gI, gkiWorkbooksModificationDate).Value = dModification
End If
Next fn
End With
On Error GoTo 0
DoEvents
' recurse subfolders
For Each fldSub In fld.SubFolders
FolderAnalysisDetail fldSub
Next fldSub
'
' end
End Sub
-----
2) Name a sheet "Workbooks" and define a named range as "WorkbooksTable" ranging from columns A:E in that sheet (or update code properly if other names used)
3) Run macro FolderAnalysis: two things, output ranges is always cleared, folder recursion is always on
Hope it helps, otherwise just advise. It's a shortened version of the Excel documenting and inspector workbook. Checked and runs Ok.
Regards!
PS: if you want the full file version for Excel, please post an email address and I'll get back to you.
