count files in folder & subfolder macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
bra888
Posts: 3
Joined: Wed Mar 29, 2017 8:09 am

count files in folder & subfolder macro

Post by bra888 »

Hi all

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
Reference: http://www.excelguru.ca/content.php?139 ... subfolders

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?
Last edited by RoryOF on Wed Mar 29, 2017 9:22 am, edited 1 time in total.
Reason: Added [code] tags {RoryOF, Moderator]
Apache OpenOffice 4.1.1
Windows 7
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: count files in folder & subfolder macro

Post by JeJe »

There's a example in the OO Help using the Dir statement for getting the files and folders in a path.

Code: Select all

Sub ExampleDir
REM Displays all files and directories
Dim sPath As String
Dim sDir as String, sValue as String
sDir="Directories:"
sPath = CurDir
sValue = Dir$(sPath + getPathSeparator + "*",16)
Do
If sValue <> "." and sValue <> ".." Then
if (GetAttr( sPath + getPathSeparator + sValue) AND 16) >0 then
REM get the directories
sDir = sDir & chr(13) & sValue
End If
End If
sValue = Dir$
Loop Until sValue = ""
MsgBox sDir,0,sPath
End sub
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
bra888
Posts: 3
Joined: Wed Mar 29, 2017 8:09 am

Re: count files in folder & subfolder macro

Post by bra888 »

how do i use the above as a formula in a cell in openoffice calc?

My objective is not to get the files and folders in a path but the quantity of files of a certain extension located in a certain directory and it's subfolders
Apache OpenOffice 4.1.1
Windows 7
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: count files in folder & subfolder macro

Post by JeJe »

Here's a flat count for one folder

Code: Select all


Dim f As String, c As Long
f = Dir$("c:\*.txt")
Do While Len(f) <> 0
    c = c + 1
    f = Dir$
Loop
MsgBox "There are" & c & " txt files in C:\"
From here:

https://www.experts-exchange.com/questi ... vb6-0.html


With your function put this at the top of the module

Code: Select all

option VBASupport 1
OOBasic doesn't accept values for optional arguments - so change the top to

Code: Select all

 Function CountFiles_FolderAndSubFolders(strFolder As String, Optional strExt As String ) As Double
if ismissing(strExt)=true then strExt = "*.*"
It then runs without error but gives a result of 0. Maybe someone else can help further with it.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
bra888
Posts: 3
Joined: Wed Mar 29, 2017 8:09 am

Re: count files in folder & subfolder macro

Post by bra888 »

Thanks for the input :)

I tried to 'cheat' and minimize the original macro changes to the following codes and it 'worked' partially

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
What I did was to prepare a X.ods file for each folder to count all the files in that folder and return a file quantity value in the same X.ods file using the attachment

and i get the correct quantity of file in the folder of the location of the file (macro enabled path)

then in Y.ods (for data processing/calculation), I tried to get the value from X.ods in cell A7 but get 0 when open then after refreshing the link, it becomes #VALUE (macro enabled and disabled same result)

Is this another limitation for OpenOffice where a 1 file cannot obtain the value from another file that uses macro for it's calculation?
X.ods
file with macro
(10.92 KiB) Downloaded 204 times
Attachments
Y.ods
processing file sample
(9.88 KiB) Downloaded 192 times
Apache OpenOffice 4.1.1
Windows 7
Post Reply