Help with counting and displaying files in a folder

[nquadr]

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.

[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
    Next

objWorkbook.SaveAs(strFileName)

'Clean up
objExcel.Quit
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.

[nquadr]

Thanks

[WetCoastLife]

you're welcome. Can you mark as solved if this is what you needed?