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


    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.

5 Years
Discussion Span
Last Post by WetCoastLife

here is a simple script that can be called from command prompt without being a sub or funtion with 2 passed in arguments. This assumes you have Excel installed on the system.

First, create a VBS file with the following code:

On Error Resume Next
Dim fso, folder, files,strPath, strFileName, intRowCount

'file path to list contents
strPath = Wscript.Arguments.Item(0)

'Excel file to save as
strFileName = Wscript.Arguments.Item(1)

'Create Excel 
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Add()

'Get Files
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(strPath)
Set files = folder.Files

    'create header
    objExcel.Cells(1, 1).Value = "File Names"
    objExcel.Cells(1, 1).Font.Bold = TRUE
    objExcel.Cells(1, 2).Value = "Source:"
    objExcel.Cells(1, 2).Font.Bold = TRUE
    objExcel.Cells(1, 3).Value = strPath

    'add Files
    intRowCount = 2
    For each file In files
        objExcel.Cells(intRowCount, 1).Value = file.Name
        intRowCount = intRowCount + 1


'Clean up
Set objExcel = Nothing
Set fso = Nothing

Then save the file and when called, it has 2 passed in parameters. First is location of folder to list, second is where to save the outputted Excel spreadsheet.
Here is an example assuming you saved script as FileCounter.vbs and want to list contents of C:\Temp and save the file in the same folder:

FileCounter.vbs "C:\temp\" "C:\temp\File_List.xls"

Hope this helps. Let me know if you have any further questions.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.