Exclude system and open files when searching in folder

Question

My code searches folders and sub folders. I want to be able to exclude open files or system files when processing each file in these folders. Any help would be great!

Dim fso, oFolder, oSubfolder, oFile, queue As Collection

Set fso = CreateObject("Scripting.FileSystemObject")
Set queue = New Collection
queue.Add fso.GetFolder("folderpath")

Do While queue.Count > 0
    Set oFolder = queue(1)
    queue.Remove 1 'dequeue
    For Each oSubfolder In oFolder.SubFolders
        If UCase(oSubfolder.Name) <> "DO NOT USE" Then
            queue.Add oSubfolder 'enqueue
        Else
        End If
    Next oSubfolder
    For Each oFile In oFolder.Files
        'Process each file but exclude files such as "~xxxxxx" or thumbs.db or xxxx.tmp files
    Next oFile
Loop

are you only looking to process actual Excel files? Or also `.txt` or `.csv'? — Scott Holtzman, Aug 02 '18 at 15:21
all .xlsx files only, but when i do it, i'm getting ~xxx.xlsx files and temp and db files along with it. I don't need those files. — ThisGuyJustNeedsHelp, Aug 02 '18 at 15:23
Use [THIS](https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open/9373914#9373914) to check if the file is already open — Siddharth Rout, Aug 02 '18 at 15:25
Use [THIS](https://www.excelfunctions.net/vba-getattr-function.html) to check if it is a system file or not — Siddharth Rout, Aug 02 '18 at 15:26
and add `If oFile.Type = "Microsoft Excel Worksheet" Then` as a condition inside your `For Each oFile` loop to process. — Scott Holtzman, Aug 02 '18 at 15:27

score 1 · Answer 1 · answered Jan 24 '22 at 13:25

I think GetAttr function should help you.

This should exclude any hidden or opened files:

For Each oFile In oFolder.Files 
    If (GetAttr(oFile) And vbHidden) = 0 Then 'Process files that are not hidden
        '
        '~~> Do what you want
        '
Next oFile

Exclude system and open files when searching in folder

1 Answers1