Macro to delete rows based on multiple criteria.

only list the .gdb name and not all of the files

Does this work for you

Please Login or Register  to view this content.

Thank you, this ran but only produced the headers and no list within it.

It's supposed to list files in the folder and subfolders with an extension of .gdb
Maybe I misunderstood?

Sorry no, I want it to list all files in folders and subflowers, but for the .gdb I only want it to list the .gdb name itself, not all the files under it (TIMESTAMP, ATX, SPX, GDBTABLX, etc)

So in the folders and subfolders there would be a mixture of xlsx, shp, tif, and a gdb. I hope that makes sense!

Hi,

just scan the folders with FSO and list the files with VBA function Dir …

I think that's what my second example is doing? But for the geodatabase I dont want it to list all of the files, I only want it to list that theres a geodatabase there.

As there is no second example ! And if it does what I described so you did not ever need to create this thread, already solved …

I have posted here. It doesn't do what I want yet, it does most of what I want. It lists all the files, but that means it also lists all the files under the geodatabase and I want it to exclude those. Just list the geodatabase name but not all the files under it.

Sub List_File()

Dim PathSpec As String
PathSpec = "" 'Specify a folder
If (PathSpec = "") Then PathSpec = SelectSingleFolder 'Browse for Folder to select a folder

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject") 'Late Binding
If (fso.FolderExists(PathSpec) = False) Then Exit Sub 'folder exist or not?

Application.ScreenUpdating = False 'Disable Screen Updating to speed up macro

Dim MySheetName As String
MySheetName = "Files" 'Add a Sheet with name "Files"
AddSheet (MySheetName)

Dim FileType As String
FileType = "*" '*:all, or pdf, PDF, XLSX...
FileType = UCase(FileType)

Dim queue As Collection, oFolder As Object, oSubfolder As Object, oFile As Object
Dim LastBlankCell As Long, FileExtension As String

Set queue = New Collection
queue.Add fso.GetFolder(PathSpec) 'enqueue

Do While queue.Count > 0
Set oFolder = queue(1)
queue.Remove 1 'dequeue

For Each oSubfolder In oFolder.SubFolders 'loop all sub-folders
queue.Add oSubfolder 'enqueue
'...insert any folder processing code here...
Next oSubfolder

LastBlankCell = ThisWorkbook.Sheets(MySheetName).Cells(Rows.Count, 1).End(xlUp).Row + 1 'get the last blank cell of column A

For Each oFile In oFolder.Files 'loop all files
FileExtension = UCase(Split(oFile.Name, ".")(UBound(Split(oFile.Name, ".")))) 'get file extension, eg: TXT
If (FileType = "*" Or FileExtension = FileType) Then
With ThisWorkbook.Sheets(MySheetName)
.Cells(LastBlankCell, 1) = oFile.Name 'File Name
.Cells(LastBlankCell, 2) = FileExtension 'File Extension
End With
LastBlankCell = LastBlankCell + 1
End If
Next oFile
Loop

'Cells.EntireColumn.AutoFit 'Autofit columns width
Application.ScreenUpdating = True

End Sub

So same as post #6 : just use FSO to scan folders but do not use it to list files !
As for a specific type of files the easy faster way is the Dir function as explained in VBA help …

Anyway you can list files with FSO but you need to check each file extension according to the FileType
like your initial post code does if you well read it and update at least the search FileType …

Originally Posted by jeaves

[…] list all of the files in a folder and subfolder
[…] as I just need to know if the .gdb is there then delete them

Just update the root folder path of GetFolder method in this VBA demonstration :

PHP Code: 

Sub DeepScanDeleteGDB(oFold As Object)
         Dim Obj As Object
    For Each Obj In oFold.Files
        If LCase(Obj.Name) Like "*.gdb" Then Obj.Delete True
    Next
        For Each Obj In oFold.SubFolders:  DeepScanDeleteGDB Obj:  Next
End Sub

Sub DemoSlow1()
    DeepScanDeleteGDB CreateObject("Scripting.FileSystemObject").GetFolder("D:\Tests4Noobs")
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

This VBA demonstration may be faster :

PHP Code: 

Dim oFSO As Object

Sub DeepScanGDBDelete(oFold As Object)
    Dim P$, oSub As Object
        P = oFold.Path & "\*.gdb"
        If Dir(P) > "" Then oFSO.DeleteFile P, True
        For Each oSub In oFold.SubFolders:  DeepScanGDBDelete oSub:  Next
End Sub

Sub Demo2()
    Const P = "D:\Tests4Noobs"
    Set oFSO = CreateObject("Scripting.FileSystemObject")
     If oFSO.FolderExists(P) Then DeepScanGDBDelete oFSO.GetFolder(P) Else Beep
    Set oFSO = Nothing
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

If you need to just list the .gdb files within a worksheet rather than delete them
then paste this VBA demonstration only to the worksheet module :

PHP Code: 

Sub DeepScanGDB(oFold As Object)
    Dim F$, S$(), R&, oSub As Object
        F = Dir$(oFold.Path & "\*.gdb")
     If F > "" Then
        ReDim S(1 To oFold.Files.Count, 0)
        Do:  R = R + 1:  S(R, 0) = oFold.Path & "\" & F:  F = Dir$:  Loop Until F = ""
        Cells(Rows.Count, 1).End(xlUp)(2).Resize(R) = S
     End If
        For Each oSub In oFold.SubFolders:  DeepScanGDB oSub:  Next
End Sub

Sub Demo3()
        Const P = "D:\Tests4Noobs"
        UsedRange.Clear
        Application.ScreenUpdating = False
    With CreateObject("Scripting.FileSystemObject")
        If .FolderExists(P) Then DeepScanGDB .GetFolder(P) Else Beep
    End With
        Application.ScreenUpdating = True
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄