fso.GetFolder(myDir).Files.Count - 1 'Why is -1 needed sometimes, not always

[Rowland Hamilton]

Folks:

Scenario: 2 departments, 1 macro for each:

Why is "-1" needed for one group of files and not the other? Because of Temporary files?:

File count accurate for Department 1 reports with:

fileCount = fso.GetFolder(myDir).Files.Count - 1

but for Department 2, I had to remove the -1

fileCount = fso.GetFolder(myDir).Files.Count

The code used to derive filepath is the same for each, just different paths are indicated in each macro, i.e.:

myDir = "C:\DATA\Department1\Sourcefiles"

versus

myDir = "C:\DATA\Sourcefiles\Department2\"

Online source indicates fso only counts visible, not hidden files, using shell object so that should be a non-factor.
Even so, I tested on folders with no hidden objects for both Department 1 and 2 reports.
I also tested on a folder with a hidden thumb.db file for Department 1 reports.

Even when I change the number of files from 1 to 2 to 3 in the Department 2 folder, it is only accurate w/o the -1 while Department 1 needs the -1.

Thank you,
Rowland

 

[Rowland Hamilton]

Can't figure out how to size this window, so had to reply again below.

 

[Rowland Hamilton]

AlphaFrog, et al: To clarify, I'm looking to amend the following code so that it ignores hidden files (vbHidden) or subtracts them from the file count, Thank you, Rowland:

Dim wbName, myDir As String
Dim fileCount, fileFlag As Integer
Dim fso As Object, myFile As Object

fileCount = 0

Workbooks.Add "C:\DATA\Templates\Department2Template.xlsx"

Set fso = CreateObject("Scripting.FileSystemObject")

myDir = "C:\DATA\Sourcefiles\Department2\"

ChDrive "C"
ChDir myDir

fileCount = fso.GetFolder(myDir).Files.Count

 

[Rowland Hamilton]

Well, this doesn't work:

fileCount = fso.GetFolder(myDir).Files.Count _
- fso.GetFolder(myDir).Files.vbHidden.Count

 

[Rowland Hamilton]

AlphaFrog et al: Found this solution to get the right count. Doesn't ignore hidden files but counts the xls, which is what works in this case, Thanks, Rowland:

Sub CountXLS()

Dim fso, count, src, folder, file
Set fso = CreateObject("Scripting.FileSystemObject")

src = "C:\DATA\Sourcefiles\Department1"
     
Set folder = fso.GetFolder(src)
count = 0
For Each file In folder.Files
If LCase(fso.GetExtensionName(file)) = "xls" Then
count = count + 1
End If
Next

Debug.Print "Count: " & count

End Sub

site for code: How to count files in directory.

 

[AlphaFrog]

This would ignore hidden files.

[COLOR=darkblue]Dim[/COLOR] wbName, myDir [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
[COLOR=darkblue]Dim[/COLOR] fileCount [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], fileFlag [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
[COLOR=darkblue]Dim[/COLOR] fso [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR], myFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]

Workbooks.Add "C:\DATA\Templates\Department2Template.xlsx"

[COLOR=darkblue]Set[/COLOR] fso = CreateObject("Scripting.FileSystemObject")

myDir = "C:\DATA\Sourcefiles\Department2\"

ChDrive "C"
ChDir myDir

[COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] myFile [COLOR=darkblue]In[/COLOR] fso.GetFolder(myDir).Files
    [COLOR=darkblue]If[/COLOR] (myFile.Attributes And vbHidden) = 0 [COLOR=darkblue]Then[/COLOR] fileCount = fileCount + 1
[COLOR=darkblue]Next[/COLOR]

 

[Rowland Hamilton]

Thanks AlphaFrog: Very nice, Rowland