I am trying to count both .xls files and .txt files in a folder. This is my current code that will bring back .xls files and display the comment showing what is in the folder.
Sub GetFilesAndCountRMS()
Dim Cell As Range
Dim File As Variant
Dim Files As Object
Dim Folder As Variant
Dim n As Long
Dim Note As Comment
Dim Text As String
With CreateObject("Shell.Application")
For Each Cell In Range("A1", Cells(Rows.count, "A").End(xlUp)) 'Change when move to Audit sheet
Set Folder = .Namespace("P:\Consolidated RM\RMS Exports\" & Cell.Value)
If Not Folder Is Nothing Then
Set Files = Folder.Items
Files.Filter 64, "*.xls"
Cell.Offset(0, 3).Value = Files.count - 1
Else
Cell.Offset(0, 3).Value = "Folder not found."
End If
Text = ""
Set Note = Cell.Offset(0, 3).Comment
If Not Note Is Nothing Then Note.Delete
Set Note = Cell.Offset(0, 3).AddComment
Note.Shape.TextFrame.AutoSize = True
For Each File In Files
Text = File & vbLf
n = Note.Shape.TextFrame.Characters.count + 1
Note.Shape.TextFrame.Characters(n, Len(Text)).Insert Text
n = n + Len(Text)
Next File
Next Cell
End With
End Sub
Bookmarks