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

 

[AlphaFrog]

Can you list the file names with fso for each folder to see where the missing file is?

[COLOR=darkblue]With[/COLOR] CreateObject("Scripting.FileSystemObject\")
    [COLOR=darkblue]With[/COLOR] .GetFolder("C:\DATA\Department1\Sourcefiles")
        Debug.Print
        Debug.Print .Name & "  File Count: " & .Files.Count
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] f [COLOR=darkblue]In[/COLOR] .Files
            c = c + 1
            Debug.Print c & " " & f.Name
        [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    c = 0
    [COLOR=darkblue]With[/COLOR] .GetFolder("C:\DATA\Sourcefiles\Department2\")
        Debug.Print
        Debug.Print .Name & "  File Count: " & .Files.Count
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] f [COLOR=darkblue]In[/COLOR] .Files
            c = c + 1
            Debug.Print c & " " & f.Name
        [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] With

 

[Firefly2012]

Is the location of the file from which the macro is run either of these folders? A file lock file is created in the same directory when a file is opened so this could account for differences.

I must say having tested on my W7/Excel2010 set up with a variety of folder options, AlphaFrog's code displayed hidden files as well as visible ones no matter what my Windows setting (but I have no way of confirming that against XP for example).

 

[Rowland Hamilton]

Firefly2012:

I need to test AlphaFrog's code, but the macro is in my Personal.xlsb workbook. Using Excel 2010 professional on Windows XP. It starts with no file open> then opens a template file> then opens the source file folders indicated. Both define mydir and use fso while on the same type of document but in different folders.
Order of events leading up to the fso code are:

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

Both templates are in same folder.

Thank you,
Rowland

 

[tweedle]

Here's another analysis version that compares fso vs Dir. (Use a new workbook)
fso (ver 5.8.76) does include System/Hidden files (Win7/Offc07).

Option Base 1
Sub CompareFileMethods()


    Dim fsoFiles(), dirFiles()
    Dim f As Scripting.File


    myDir = GetFolder("C:\")
    If myDir = "" Then Exit Sub    'cancelled


    Set fso = CreateObject("Scripting.FileSystemObject")


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


    ReDim fsoFiles(2, filecount)
    idx = 1
    For Each f In fso.GetFolder(myDir).Files


        If f.Attributes <> 32 Then Debug.Print f.Name, f.Attributes
        Select Case f.Attributes
            Case Is = Volume
                attDesc = "Volume"
            Case Is = Directory
                attDesc = "Directory"
            Case Is = Archive
                attDesc = "Archive"
            Case Is = Hidden
                attDesc = "Hidden"
            Case Is = Hidden + System
                attDesc = "Hidden-System"
            Case Is = Hidden + Archive
                attDesc = "Hidden-Archive"
            Case Is = Hidden + System + Archive
                attDesc = "Hidden-System-Archive"
            Case Is = Normal
                attDesc = "Normal"
            Case Is = ReadOnly
                attDesc = "ReadOnly"
            Case Is = System
                attDesc = "System"
            Case Else
                attDesc = "Different Combination " & f.Attributes
        End Select


        fsoFiles(1, idx) = f.Name
        fsoFiles(2, idx) = attDesc
        idx = idx + 1
    Next
    Set fso = Nothing


    fileCountDir = 0
    retval = Dir(myDir & "\" & "*.*")
    Do
        If retval <> "" Then
            fileCountDir = fileCountDir + 1


            ReDim Preserve dirFiles(fileCountDir)
            dirFiles(fileCountDir) = retval


            retval = Dir()
            DoEvents
        End If


    Loop While retval <> ""


    Sheet1.Range("A:B").ClearContents
    Sheet1.Range("A1").Resize(idx - 1, 2) = WorksheetFunction.Transpose(fsoFiles())
    Sheet1.Range("C1").Resize(fileCountDir, 1) = WorksheetFunction.Transpose(dirFiles())
End Sub


Function GetFolder(strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

 

[Firefly2012]

Well, I think AlphaFrog's/Tweedle's code is definitely the way to go to see what's in that file that you are not expecting - it will be very interesting to hear what is the spurious 'extra' file! Please do keep us updated

 

[Rowland Hamilton]

Alphafrog, et al:

My printer is on the fritz, can I convert this code so it shows me on screen until I can fix my printer (which won't even print screenshots or take scans until I fix the ink cartridge issues):

With CreateObject("Scripting.FileSystemObject\")
    With .GetFolder("C:\DATA\Department1\Sourcefiles")
            Debug.Print
        Debug.Print .Name & "  File Count: " & .Files.Count
        For Each f In .Files
            c = c + 1
            Debug.Print c & " " & f.Name
        Next
    End With    
    c = 0
    With .GetFolder("C:\DATA\Sourcefiles\Department2\")
        Debug.Print
        Debug.Print .Name & "  File Count: " & .Files.Count
        For Each f In .Files
            c = c + 1
            Debug.Print c & " " & f.Name
        Next
    End With
End With

Thank you,
Rowland

(aside: just looked up The Katzenjammer Kids possible origin for the expression "on the fritz".)

 

[AlphaFrog]

Debug.Print prints to the Immediate Window in VBA and not a hardware printer.

From the VBA menu; View\ Immediate Window (Ctrl+G)

 

[Rowland Hamilton]

Oh, I need the immediate window according to Greg Chapman at:
Office VBA—Basic Debugging Techniques

 

[Rowland Hamilton]

AlphaFrog:

How can I subtract hidden files from my count?

Sorry, saw your message after I started typing. Found the problem is one has a hidden thumb.db file after all and the other doesn't. I changed my folder view to show hidden files but it didn't show the thumb so I thought it wasn't there (saw it on another computer, though).

Thank you,
Rowland