I used macro from the following link for Microsoft Excel and it worked but when I tried in OpenOffice Calc, it doesn't work
Code: Select all
Private Function CountFiles_FolderAndSubFolders(strFolder As String, Optional strExt As String = "*.*") As Double
'Author : Ken Puls (http://www.excelguru.ca)
'Function purpose: To count files in a folder and all subfolders. If a file extension is provided,
' then count only files of that type, otherwise return a count of all files.
Dim objFso As Object
Dim objFiles As Object
Dim objSubFolder As Object
Dim objSubFolders As Object
Dim objFile As Object
'Set Error Handling
On Error GoTo EarlyExit
'Create objects to get a count of files in the directory
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFiles = objFso.getfolder(strFolder).Files
Set objSubFolders = objFso.getfolder(strFolder).subFolders
'Count files (that match the extension if provided)
If strExt = "*.*" Then
CountFiles_FolderAndSubFolders = objFiles.Count
Else
For Each objFile In objFiles
If UCase(Right(objFile.Path, (Len(objFile.Path) - InStrRev(objFile.Path, ".")))) = UCase(strExt) Then
CountFiles_FolderAndSubFolders = CountFiles_FolderAndSubFolders + 1
End If
Next objFile
End If
'Request count of files in subfolders
For Each objSubFolder In objSubFolders
CountFiles_FolderAndSubFolders = CountFiles_FolderAndSubFolders + _
CountFiles_FolderAndSubFolders(objSubFolder.Path, strExt)
Next objSubFolder
EarlyExit:
'Clean up
On Error Resume Next
Set objFile = Nothing
Set objFiles = Nothing
Set objFso = Nothing
On Error GoTo 0
End Function
When I compiled, the first error got the first line in where = "*.*" is
The objective of the macro is to create a function with the first cell being the path and the second cell being the file extension type and the return value will be the quantity of the files of the specified extension type
Example:
C:\
abc.txt
123.txt
qwe.dwg
A1=C:\
A2=txt
A3=CountFiles_FolderAndSubFolders(A1;A2)
=2
I'm not a programming expert or anything. Could anyone help?