Results 1 to 5 of 5
  1. #1
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368

    Extracting number of files found by module

    Hey guys,



    I found a module on the net wich extracts files from a recursive directory.
    The module works great and when i call the files with debug.print or
    messagebox
    Code:
    debug.print vFile
    and
    Code:
    Msgbox vFile
    then it lists
    the files found.

    However, i want the number of files, not the names of the files.
    Now i tried a couple of things but none of them work.
    Foreinstance i tried SQL listing the files in a table, and then counting the number from there would be easy.
    But as i said, that doesnt work.

    Any ideas would be great

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    More code around your posted statement would help. No idea if you get this from a calculated query field or what, or if it's a string, array....
    If it's a concatenated string that you build in a loop, add a counter in the loop. If it's an array, count the array members by looping the elements using LBound/UBound.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    Right you are, ill post the codes below

    Code:
    Dim colFiles As New Collection
        RecursiveDir colFiles, "C:\Photos", "*.jpg", True
    
    Dim vFile As Variant
    For Each vFile In colFiles
    Debug.Print vFile
    Next vFile
    


    Outputs (example) :

    C:\Photos\2006-10-28\IMG_2851.JPG
    C:\Photos\2006-10-28\IMG_2852.JPG
    C:\Photos\2006-11-04\IMG_2853.JPG
    C:\Photos\2006-11-04\IMG_2854.JPG
    C:\Photos\2006-11-04\IMG_2855.JPG



    Module :

    Code:
    Public Function RecursiveDir(colFiles As Collection, _
                                 strFolder As String, _
                                 strFileSpec As String, _
                                 bIncludeSubfolders As Boolean)
    
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
    
    'Add files in strFolder matching strFileSpec to colFiles
        strFolder = TrailingSlash(strFolder)
        strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
            colFiles.Add strFolder & strTemp
            strTemp = Dir
    Loop
    
    If bIncludeSubfolders Then
    'Fill colFolders with list of subdirectories of strFolder
            strTemp = Dir(strFolder, vbDirectory)
    Do While strTemp <> vbNullString
    If (strTemp <> ".") And (strTemp <> "..") Then
    If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                        colFolders.Add strTemp
    End If
    End If
                strTemp = Dir
    Loop
    
    'Call RecursiveDir for each subfolder in colFolders
    For Each vFolderName In colFolders
    Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
    Next vFolderName
    End If
    
    End Function
    
    
    Public Function TrailingSlash(strFolder As String) As String
    If Len(strFolder) > 0 Then
    If Right(strFolder, 1) = "\" Then
                TrailingSlash = strFolder
    Else
                TrailingSlash = strFolder & "\"
    End If
    End If
    End Function
    

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A collection has a count property: colFiles.Count
    Last edited by Micron; 01-21-2016 at 11:43 AM. Reason: removed signature

  5. #5
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    Super, works like a charm
    Thanks mate !

Please reply to this thread with any new information or opinions.

Similar Threads

  1. extracting pdf files question
    By slimjen in forum Programming
    Replies: 3
    Last Post: 03-11-2015, 08:45 AM
  2. Replies: 5
    Last Post: 01-08-2015, 12:40 PM
  3. Saving Module Code to Text Files
    By ioMatt in forum Modules
    Replies: 2
    Last Post: 07-02-2011, 08:18 AM
  4. Number of Records Found by a Query?
    By Xiaoding in forum Queries
    Replies: 3
    Last Post: 03-05-2010, 03:34 PM
  5. Module not found
    By nooby in forum Modules
    Replies: 1
    Last Post: 12-02-2009, 02:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums