Counting files excluding thums.db

[JeroenMioch]

Hey guys,

I have some codes to count the number of files in a specific directory.
Of of the codes is to ensure Thumbs.db (hidden system file) is excluded in the count.
Im pretty sure it used to work, but i noticed that the nuber of files was incorrect so i checked
the line that excluded the thumbs file and its not doing its job.

Anyone that knows why ?

If Len(Dir("C:\Mydirectory\subdirectory\*.db", vbHidden)) = 0 Then
MsgBox "No .db files"
else
msgbox "db file found"
End If

It says there are no db files when im sure there is one..

<Edit> if i fire the same code with *.pdf as extention then the code does see the pdf file.


So why does he not see the thumbs.db even though the code specifies the file is hidden.

[NTC]

.db is not an Access file extension.... so either you are in the wrong forum or your extension should be .accdb or .mdb or something that is Access........

[CJ_London]

try using filessystemobject, it is generally faster, more flexible and less prone to potential error

[ItsMe]

Here is some sample code of using filesystemobject that might help. It just so happens that the first example I found was a reply to one of your old threads. But I believe it is what I would use in this case.
https://www.accessforums.net/access/...tml#post274051

[JeroenMioch]

.db is not an Access file extension.... so either you are in the wrong forum or your extension should be .accdb or .mdb or something that is Access........

.db is a database file and is used by Windows for thumnails
And dear sir, i am not at the wrong forum

[JeroenMioch]

Here is some sample code of using filesystemobject that might help. It just so happens that the first example I found was a reply to one of your old threads. But I believe it is what I would use in this case.
https://www.accessforums.net/access/...tml#post274051

Thanks mate, im gonna try it and get the results back to you.

[ItsMe]

Thanks mate, im gonna try it and get the results back to you.

Just be sure to read the code notes and the code before using it. That code will delete PDF files. I use something like it for temp reports and emails.

[JeroenMioch]

Im getting the error that a datatype was not defined by the user
it highlights : Dim fsoFile As New FileSystemObject.

I editted the code to test it and more importantly because i do not want to delete all pdf files in the dir
The reason i wanna exclude thumbs.db file rather then counting .pdf is because we might one day change from using pdf
to lets say Word files. Then ill have to change the codes.

Dim strFile As String
    strFile = "I:\MyDirectory\MySubdirectory\"
Dim fsoFile As New FileSystemObject
Dim objFolder As folder
Dim objFile As File
    Set objFolder = fsoFile.GetFolder(strFile)
    
        For Each objFile In objFolder.Files
        
            If InStr(objFile.Name, ".db") Then
                MsgBox "There are database files"
            Else
                MsgBox "There are no database files"
            End If
        
        Next
    
    Set objFolder = Nothing

[JeroenMioch]

Just be sure to read the code notes and the code before using it. That code will delete PDF files. I use something like it for temp reports and emails.

LOL thanks for the warning but as you see i noticed

[ItsMe]

The following is early binding where the new object is instantiated at the same time it is declared. In order to do it this way, you need to Reference the correct library so Access knows what the heck.
Dim fsoFile As New FileSystemObject

From within the VBA editor go to Tools > References and select "Microsoft Scripting Runtime". Pretty sure that is the one you want, anyway.

[JeroenMioch]

Im not at my desk right now, but im pretty sure i have that library object allready checkmarked.
Im gonna look at it offcourse.

[JeroenMioch]

Right you are, my test db didnt have that declared

It works with a little adjustment. But i havent tested it yet with empty or non existant directory's.

Dim strFile As String
    strFile = "C:\Maindir\Subdir1\Subdir2\"
Dim fsoFile As New FileSystemObject
Dim objFolder As folder
Dim objFile As File
    Set objFolder = fsoFile.GetFolder(strFile)
    
        For Each objFile In objFolder.Files
        
            If InStr(objFile.Name, ".db") Then
                ctrlGetFileCount = objFolder.Files.Count - 1
            Else
                ctrlGetFileCount = objFolder.Files.Count
            End If
        Next
    Set objFolder = Nothing
End Sub

Many thanks so far, but i have a feeling im not done yet :P

[JeroenMioch]

Empty Dir was not a problem, non existant Dir gave an error, but that was easily solved :

Dim strFile As String
    strFile = "C:\Maindir\Subdir1\Subdir2\"
    
If Len(Dir(strFile)) = 0 Then
    ctrlGetFileCount = 0
    Else
Dim fsoFile As New FileSystemObject
Dim objFolder As folder
Dim objFile As File
    Set objFolder = fsoFile.GetFolder(strFile)
    
        For Each objFile In objFolder.Files
        
            If InStr(objFile.Name, ".db") Then
                ctrlGetFileCount = objFolder.Files.Count - 1
            Else
                ctrlGetFileCount = objFolder.Files.Count
            End If
        Next
    Set objFolder = Nothing
    End If

Ill have to test the thing in the real application now, but it seems you have helped me once again.
Many thanks mate

btw, im getting better at understanding this stuff thanks to people like you who give their time to help others.

[JeroenMioch]

Strange im getting type mismatch at

Set objFolder = fsoFile.GetFolder(strFile)

I dont get the error in my test db.
Only difference is that the directory is not a constant but a variable.
Like so

strFile = "I:\MainDir\SubDir\" & Forms![frmObjects]!ctrlNameObject & "\"

[ItsMe]

I would use a debug.print line and view the results in the Immediate window. You can view the Immediate Window by using the Ctrl+G shortcut on your keyboard. Another thing to consider is to test your code using the trailing \ and also without. THe FSO object is likely to not need or even want it. However, you may need to add it (\) back when you retrieve the path from a folder object and use the path elsewhere.

strFile = "I:\MainDir\SubDir\" & Forms![frmObjects]!ctrlNameObject & "\"
debug.print strfile