Locating Excel files with a specific tab name.

[thanos_Z]

Hello,
I have a folder with over 3,000 xlsx files. These files contain several tabs with specific names. I am trying to locate which of those files do not have a tab with the name "DOWNLOAD".
I do not want to have to open all 3,000 files and identify which ones do not have this tab. Is there any way to do this with a VBA macro?
Thank you for your assistance.

 

[Fennek]

Hello,

with VBA it is possible: If the Workbook are faily small a macro can open them sequentially and check the name of the sheets. If the workbook are big, it might be faster to unzip and then check for the names.



regards

 

[thanos_Z]

I have the below code which identifies all the files and worksheet names but what if i wanted to only identify the files that do not have a worksheet with a specific name. What changes would be need on this code?

Sub FolderCrawler()
FileType = "*.xls*"
With Application.FileDialog(msoFileDialogFolderPicker)
    .InitialFileName = ThisWorkbook.Path
    .AllowMultiSelect = False
    If .Show = -1 Then
        FilePath = .SelectedItems(1) & "\"
    Else
        Exit Sub
    End If
    
End With
OutputRow = 2
ThisWorkbook.ActiveSheet.Range("A" & OutputRow) = FilePath & FileType
OutputRow = OutputRow + 1
Curr_File = Dir(FilePath & FileType)
Do Until Curr_File = ""
    Set FldrWkbk = Workbooks.Open(FilePath & Curr_File, False, True)
    ThisWorkbook.ActiveSheet.Range("A" & OutputRow) = Curr_File
    ThisWorkbook.ActiveSheet.Range("B" & OutputRow).ClearContents   'Clear any previous values
    OutputRow = OutputRow + 1
    
    For Each Sht In FldrWkbk.Sheets
        ThisWorkbook.ActiveSheet.Range("B" & OutputRow) = Sht.Name
        ThisWorkbook.ActiveSheet.Range("A" & OutputRow).ClearContents 'Clear any previous values
        OutputRow = OutputRow + 1
    Next Sht
    FldrWkbk.Close SaveChanges:=False
    Curr_File = Dir
Loop
Set FldrWkbk = Nothing
ThisWorkbook.ActiveSheet.Range("A" & OutputRow) = "---END OF FOLDER---"
End Sub

 

[Fennek]

Try it first with a 10-20 files, some with and some without the sheet:

Const Pt As String = "c:\temp\"

Sub F_en()
On Error Resume Next
Application.ScreenUpdating = False
Set WS = ActiveSheet

f = Dir(Pt & "*.xlsx")
Do While Len(f)
    With Workbooks.Open(Pt & f)
        .Sheets("Download").Activate
        If Err.Number <> 0 Then
            lr = lr + 1
            WS.Cells(lr, 1) = .Name
            WS.Cells(lr, 2) = "Download not exist"
            Err.Clear
        End If
        .Close 0
    End With
f = Dir
Loop
Application.ScreenUpdating = True
End Sub

Adopt the path first.

 

[thanos_Z]

Tried it with a test batch of 10 files for which 2 of them would be a fail. However, i only got a blank page. No results for the files that do not have that specific worksheet name.
Also to mention that some of the files may have several worksheets within it.