I have the following code that I would like to count files in a folder and return their name.
Function CountFilesInFolder(strFolder As String) As Long
Dim noOfFiles As Long
Dim FileName As String
FileName = Dir(strFolder)
noOfFiles = 0
Do While FileName <> ""
FileName = Dir
'ActiveSheet.Cells(noOfFiles, 1).Value = FileName
noOfFiles = noOfFiles + 1
Loop
CountFilesInFolder = noOfFiles
Exit Function
End Function
However when I uncomment ActiveSheet.Cells(noOfFiles, 1).Value = FileName
I get an error, not sure what I am doing wrong.
Also, is there a way to code this without using a function, ideally I would like to run it as a procedure, but I could not get it to work. I am new to VBA, so any help is greatly appreciated.