Loop through a list of files searching for files which contain a tab calle "Data_Table"

[morph81]

Hi

I was hoping someone might be able to help me this.

I've got the macro which loops through a list of files within an excel column, but I need to then have the macro loop through the opened files' sheet tabs to see if it contains a sheet specifically contains "Data Table".

If such a tab is found I need it to be marked as found in the cell next to the file name.

The code I have is below, currently it seems to be opening each listed file but a) is only looking at worksheet 1 (how do you specify ALL worksheets?), and also does not add the text "Data Table", instead the document remains open.

I'm pretty new to this so sorry if the code is bizarre...

Sub DataTable_Loop()

Dim ThisFileName, FileToOpen As String



ThisFileName = ActiveWorkbook.Name

Range("a1").Select

Do While ActiveCell <> ""

FileToOpen = ActiveCell

Workbooks.Open(Filename:=FileToOpen).RunAutoMacros Which:=xlAutoOpen

ActiveWorkbook.Worksheets.Select


If Worksheets(1).Name = "Data_Table" Then
WBFound = True
'WBName = wb.Name
If WBound = True Then

ActiveWorkbook.Close

Windows(ThisFileName).Active

ActiveCell.Offset(0, 1) = "Data_Table"

End If

'Exit For

Else

ActiveWorkbook.Close

'Windows(ThisFileName).Active

'ActiveCell.Offset(0, 1) = "None"


End If

'Exit For

Windows(ThisFileName).Activate

ActiveCell.Offset(1, 0).Range("a1").Select
Loop
End Sub

 

[AlphaFrog]

Sub DataTable_Loop()
    
    Dim cell As Range, wsTS As Worksheet
    
    Application.ScreenUpdating = False
    
    For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        If cell.Value <> "" Then
            If Len(Dir(cell.Value)) > 0 Then
                Workbooks.Open(Filename:=cell.Value).RunAutoMacros Which:=xlAutoOpen
                On Error Resume Next
                    Set wsTS = ActiveWorkbook.Sheets("Total_Sheets")
                On Error GoTo 0
                If Not wsTS Is Nothing Then
                    cell.Offset(0, 1) = "Data_Table"
                    Set wsTS = Nothing
                Else
                    cell.Offset(0, 1) = ""
                End If
                ActiveWorkbook.Close SaveChanges:=False
            Else
                cell.Offset(0, 1) = "File not found"
            End If
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[morph81]

Wow - Thanksks AlphaFrog, that was such a quick response.

I tested the macro, and it seems to running through the list of files which are in column A of the main workbook, but it isn't then adding "Data Table in column B against the related file within the list...

Is this possible?

 

[AlphaFrog]

Is column B "File not found" for each file or is column B just blank?

If the files in column A have a full path e.g. C:\Temp\MyFile.xls, then it should work.

If the files in column A are just a file name e.g. MyFile.xls, then you may have to define within the macro the path to look for the files.

Sub DataTable_Loop()
    
    Dim cell As Range, wsTS As Worksheet
    
    Application.ScreenUpdating = False

    [COLOR="Red"]ChDir "C:\Temp"[/COLOR]
    
    For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        If cell.Value <> "" Then
            If Len(Dir(cell.Value)) > 0 Then
                Workbooks.Open(Filename:=cell.Value).RunAutoMacros Which:=xlAutoOpen
                On Error Resume Next
                    Set wsTS = ActiveWorkbook.Sheets("Total_Sheets")
                On Error GoTo 0
                If Not wsTS Is Nothing Then
                    cell.Offset(0, 1) = "Data_Table"
                    Set wsTS = Nothing
                Else
                    cell.Offset(0, 1) = ""
                End If
                ActiveWorkbook.Close SaveChanges:=False
            Else
                cell.Offset(0, 1) = "File not found"
            End If
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[morph81]

The files are listed with the full path c:\ etc.

It's just that nothing appears in column B.

I purposely added tabs named Data_Table but none seem to be registering in col B

Thanks again!

 

[morph81]

AlphaFrog

Ignore my previous post it works - I just needed to change the name to look for from "Total_Sheets" to "Data_Table".

THANK YOU SO MUCH!!!

 

[AlphaFrog]

Sorry about that. I had some test files in a folder that had sheets called Total_Sheets and I missed correcting it in the final macro.

 

[morph81]

Hey AlphaFrog

Cheers for your help - it worked wonderfully, however I did find there was a significant amount of files with links which meant I had to keep an eye of the running macro as it required me to click the continue dialouge box requesting whether to update links.

I thought that maybe opening as a read file would be a simple work around...? But the edit links option still pops up....? Any ideas?

Thanks

Sub DataTable_Loop()
    
    Dim cell As Range, wsTS As Worksheet
    
    Application.ScreenUpdating = False
    
    For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        If cell.Value <> "" Then
            If Len(Dir(cell.Value)) > 0 Then
                Workbooks.Open(Filename:=cell.Value, [COLOR=#ff0000]ReadOnly:=True[/COLOR]) .RunAutoMacros Which:=xlAutoOpen
                On Error Resume Next
                    Set wsTS = ActiveWorkbook.Sheets("REQUEST_TABLE")
                On Error GoTo 0
                If Not wsTS Is Nothing Then
                    cell.Offset(0, 1) = "Data_Table"
                    Set wsTS = Nothing
                Else
                    cell.Offset(0, 1) = ""
                End If
                ActiveWorkbook.Close SaveChanges:=False
            Else
                cell.Offset(0, 1) = "File not found"
            End If
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub