count files in folder & subfolder macro

Creating a macro - Writing a Script - Using the API

count files in folder & subfolder macro

Postby bra888 » Wed Mar 29, 2017 8:23 am

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   Expand viewCollapse view
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
bra888
 
Posts: 3
Joined: Wed Mar 29, 2017 8:09 am

Re: count files in folder & subfolder macro

Postby JeJe » Wed Mar 29, 2017 10:39 am

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

Code: Select all   Expand viewCollapse view
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
Openoffice 4.1.2
Windows 8
JeJe
 
Posts: 121
Joined: Wed Mar 09, 2016 2:40 pm

Re: count files in folder & subfolder macro

Postby bra888 » Wed Mar 29, 2017 11:05 am

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
bra888
 
Posts: 3
Joined: Wed Mar 29, 2017 8:09 am

Re: count files in folder & subfolder macro

Postby JeJe » Wed Mar 29, 2017 12:53 pm

Here's a flat count for one folder

Code: Select all   Expand viewCollapse view

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/questions/25954388/How-to-count-files-in-a-folder-having-sub-folders-vb6-0.html


With your function put this at the top of the module

Code: Select all   Expand viewCollapse view
option VBASupport 1


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

Code: Select all   Expand viewCollapse view
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.
Openoffice 4.1.2
Windows 8
JeJe
 
Posts: 121
Joined: Wed Mar 09, 2016 2:40 pm

Re: count files in folder & subfolder macro

Postby bra888 » Thu Mar 30, 2017 12:06 pm

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   Expand viewCollapse view
    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 28 times
Attachments
Y.ods
processing file sample
(9.88 KiB) Downloaded 21 times
Apache OpenOffice 4.1.1
Windows 7
bra888
 
Posts: 3
Joined: Wed Mar 29, 2017 8:09 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 9 guests