hi experts
I hope to find help in this forum
actually I have no experience to how mod this code so , I have this macro it loops the files throught folders and sub folders and add hyperlink to open the file if I need it , the problem is showing all the files in one sheet but I want arranging the files based on month in each sheet alone .
I have 12 sheets and its names JAN ,FEB .. up to DEC so it supposss brings the files for each sheet alone based on month for instance sheet JAN should brings all files in this month and FEB bring all files in FEB month and so on the rest of sheets.
Sub LISTFILES()
Dim fPath As String
Dim fType As String
Dim fname As String
Dim NR As Long
Dim AddLinks As Boolean
fPath = "C:\Users\PC WW\Documents\"
fType = "*"
If fType = "False" Then Exit Sub
AddLinks = vbYes
Application.ScreenUpdating = False
NR = 4
With ActiveSheet
.[A2] = "LIST OF FILES"
.[B2] = "Modified Date"
Call FindFilesAndAddLinks(fPath, fType, NR, AddLinks)
End With
With ActiveSheet
.Range("A:B").Columns.AutoFit
.Range("B:B").HorizontalAlignment = xlCenter
Range("B:B").Select
Selection.NumberFormat = "d-mmm-yy h:mm AM/pm"
End With
With ActiveSheet
Range("A2").Select
Selection.Font.Bold = True
Range("B2").Select
Selection.Font.Bold = True
Columns("A:A").Select
Selection.Font.Underline = xlUnderlineStyleNone
End With
Application.ScreenUpdating = True
End Sub
Private Sub FindFilesAndAddLinks(fPath As String, fType As String, ByRef NR As Long, AddLinks As Boolean)
Dim fname As String
Dim oFS As New FileSystemObject
Dim oDir
On Error Resume Next
fname = Dir(fPath & "*." & fType)
With ActiveSheet
.Range("A" & NR) = fPath
.Range("A" & NR).Select
If AddLinks Then .Hyperlinks.Add Anchor:=Selection, _
Address:=fPath, _
TextToDisplay:="FOLDER NAME: " & " " & UCase(Split(fPath, "\")(UBound(Split(fPath, "\")) - 1))
Selection.Font.Bold = True
Selection.Font.Size = 10
Selection.Font.Name = "Arial"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
NR = NR + 1
Do While Len(fname) > 0
If .Range("A" & NR) <> "" Then Debug.Print "Overwriting " & NR
.Range("A" & NR) = fname
.Range("B" & NR) = FileDateTime(fPath & fname)
.Range("A" & NR).Select
If AddLinks Then .Hyperlinks.Add Anchor:=Selection, _
Address:=fPath & fname, _
TextToDisplay:=fname
NR = NR + 1
fname = Dir
Loop
Set oDir = oFS.GetFolder(fPath)
For Each oSub In oDir.SubFolders
NR = NR + 1
Call FindFilesAndAddLinks(oSub.Path & "\", fType, NR, AddLinks)
Next oSub
End With
End Sub