[AccessD] A2003:Counting Files in a folder

Brilliant

Many thanks Jurgen - works a treat
 
Darren
-----------------
T: 1300 301 731

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: Friday, 27 June 2008 2:14 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A2003:Counting Files in a folder



The first function below is passed a file path and name and it returns a file
name with an incrementing 2 digit numeric suffix.  My application creates a file
of a type, say 'Fax.doc'.  The function is passed that parameter and it returns
a file name 'Fax-04.doc' if it finds 'Fax-01.doc', 'Fax-02.doc' and
'Fax-03.doc'.  Since the application names the files during the file creation
process, there are no decimals inserted in the name by a user so there is a
guarantee that there is one and only one period prior to the file extension.
Below this function is an untested attempt at a function that meets your exact
inquiry.


Public Function fnFileSuffix(strFilePathAndFileName As String) As String
    On Error GoTo ErrorHandler

    Dim lngI As Long
    Dim strExtension As String
    Dim strName As String

    lngI = InStr(strFilePathAndFileName, ".")
    If lngI Then
        strName = Left$(strFilePathAndFileName, lngI - 1)
        strExtension = Mid$(strFilePathAndFileName, lngI + 1)
        lngI = 1
        Do Until Dir(strName & Format$(lngI, "-00.") & strExtension) = ""
            lngI = lngI + 1
        Loop
        strFilePathAndFileName = strName & Format$(lngI, "-00.")
        fnFileSuffix = strFilePathAndFileName & strExtension
    Else
        MsgBox "improper file name"
    End If

ExitRoutine:
    On Error Resume Next
    Exit Function
ErrorHandler:
    With Err
        Select Case .Number
            Case Else
                MsgBox .Number & vbCrLf & .Description, vbInformation, "Error -
" & _
                  "fnFileSuffix"
        End Select
    End With
    'Resume 0
    Resume ExitRoutine
End Function


It might be written (untested)...


Public Function EnumerateFiles(strFileSpec as String) As Long
    Dim str As String
    Dim lngI As Long

    str = Dir(strFileSpec, vbDirectory)
    Do While Len(str)
        lngI = lngI + 1
        str = Dir()
    Loop
    EnumerateFiles = lngI
End Function


The calling sub could be something like:


Private Sub FileCount
    Debug.Print EnumerateFiles("\\OurServer\OurClient\Files\XML\*.xml") & " xml
files"
    Debug.Print EnumerateFiles("\\OurServer\OurClient\Files\PDF\*.pdf") & " pdf
files"
    Debug.Print EnumerateFiles("\\OurServer\OurClient\Files\TXT\*.txt") & " txt
files"
End Sub


Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com

> From: darren at activebilling.com.au
> To: accessd at databaseadvisors.com
> Date: Fri, 27 Jun 2008 10:06:43 +1000
> Subject: [AccessD] A2003:Counting Files in a folder
>
> Hi team
>
>
>
> Does anyone have an example of counting how many files there are of a
particular
> type in a folder?
>
> EG I want to count all the PDF, TXT and XML files in 3 corresponding folders
>
> EG the folders are named for the file type they hold
>
> Folder 1 = \\OurServer\OurClient\Files\XML\*.xml
>
> Folder 2 = \\OurServer\OurClient\Files\PDF\*.pdf
>
> Folder 3 = \\OurServer\OurClient\Files\TXT\*.txt
>
>
>
> The Air code I have is
>
> See how many file types there are for client X (In the Example above there are
3
> but it may be 2 or 1 even 4)
>
> So in the example above using 3 file types
>
>
>
> Start a loop for file types and count the number of files in that folder type
>
> Loop 1 = xml
>
> See how many files are in folder .\XML
>
> Report the file count move to next file type
>
> Loop 2 will = pdf
>
> See how many files are in folder .\PDF
>
> Report the file count move to next file type
>
> Loop 3 will = txt
>
> See how many files are in folder .\txt
>
> Report the file count move to next file type
>
> Then exit the loop
>
>
>
> Many thanks in advance
>
> Darren
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com