Looping through files

[UnsolvedCoding]

I have some old code to do this but its crude and slow.

In a folder are over 400 workbooks and each of the workbooks has tabs that start with io. I need to get into each workbook, find the right tab, pull the info from a set of cells and then close the workbook and repeat.

Because the workbooks were copied over to a folder and are not originals I don't need to worry about screwing something up.

What is the most efficient way to loop through them and extract the cell data?

This is what I have so far -

Option Explicit

Public directory As String
Public fileName As String
Public sheet As Worksheet
Public i As Integer
Public j As Integer


Sub Extract()

On Error GoTo Error_Handler

Application.ScreenUpdating = False

directory = "C:\Users\Desktop\Copys of IO List\"
fileName = Dir(directory & "*.xl??")
    
Do While fileName <> ""

j = 1

Workbooks.Open (directory & fileName)

For Each sheet In Workbooks(fileName).Worksheets

            ' Only check sheets that starts with IO
300:    If Left(UCase(Workbooks(fileName).Worksheets(j).Name), 2) = "IO" Then


'Copy columns A, C F and I to this workbook

1100:   End If
    
    j = j + 1
    
Next sheet

Workbooks(fileName).Close

fileName = Dir()

Loop

Application.ScreenUpdating = True

1300:   Exit Sub

Error_Handler:

End Sub

 

[SkipVought]

Hi,

So not sure what specific questions you have.

Does your current code execute, opening each workbook in the folder and looping through each sheet? So no problem there, right?

Then the 4 columns:

Is this a single table with headings? Same headings/data types in all 400 workbooks?

How will the data in these 4 columns be mapped to the unspecified destination? And what is this destination?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[UnsolvedCoding]

Yes it will open the workbook and look for the right sheets based off the left 2 characters of the tab name. The headings will always be the same for each column but the amount of information for each column can change.

Workbook names, worksheet names, column size - all of it can change. Only the tab starting with IO and the column headers will be consistent.

Ideally I would like to simply copy the needed tabs over to the workbook with the code.

It has taken me a while to make sense of what I hear at work involving computers. There is much talk of bugs and questions about Raid.
Therefore I have come to the logical conclusion that the only way to have a properly functioning computer is to regularly spray it with Raid bug killer.

 

[UnsolvedCoding]

Hey Skip,

I was able to make this work.

Option Explicit

Public directory As String
Public fileName As String
Public sheet As Worksheet
Public i As Integer
Public j As Integer
Public X As Integer


Sub Extract()

On Error GoTo Error_Handler

'Application.ScreenUpdating = False

directory = "C:\Users\Desktop\Copys of IO List\"
fileName = Dir(directory & "*.xl??")
    
Do While fileName <> ""

j = 1

Workbooks.Open (directory & fileName)

For Each sheet In Workbooks(fileName).Worksheets

    If Workbooks(fileName).Worksheets(j).Name = "IOList Base" Then GoTo 1000
    
    ' If the sheet starts with IO we have gone to far
    If Left(UCase(Workbooks(fileName).Worksheets(j).Name), 2) = "IO" Then
    
    Workbooks(fileName).Worksheets(j).Copy after:=ThisWorkbook.Sheets("Sheet1")

1000:   End If
    
    j = j + 1
    
Next sheet

Workbooks(fileName).Close

fileName = Dir()

Loop

Application.ScreenUpdating = True

1300:   Exit Sub

Error_Handler:

End Sub

It has taken me a while to make sense of what I hear at work involving computers. There is much talk of bugs and questions about Raid.
Therefore I have come to the logical conclusion that the only way to have a properly functioning computer is to regularly spray it with Raid bug killer.

 

[SkipVought]

Copy columns A, C F and I to this workbook" and "Ideally I would like to simply copy the needed tabs over to the workbook with the code" are two different things???

So you really want a workbook with 400 tabs? REALLY?

What will you be doing with this data once assembled?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[UnsolvedCoding]

I have to collect the information in Columns A, D (earlier mistake to say C) and F, I to add to a default list, then it will be sort to remove duplicates and used to update our error checking with data entry.

A and D are paired as well as F and I. These columns will create a library of past entries so that we can isolate out potential future problems.

It has taken me a while to make sense of what I hear at work involving computers. There is much talk of bugs and questions about Raid.
Therefore I have come to the logical conclusion that the only way to have a properly functioning computer is to regularly spray it with Raid bug killer.

 

[UnsolvedCoding]

I also have been trying this in the loop

Do While fileName <> ""

j = 1

Workbooks.Open (directory & fileName)

For Each sheet In Workbooks(fileName).Worksheets

    If Workbooks(fileName).Worksheets(j).Name = "IOList Base" Then GoTo 1100
    
    ' If the sheet starts with IO we have gone to far
    If Left(UCase(Workbooks(fileName).Worksheets(j).Name), 2) = "IO" Then
    
        X = Workbooks(fileName).Worksheets(j).Cells(Rows.Count, "D").End(xlUp).Row + 1
    
        i = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row + 1
    
            ' Copy the desired information
            Workbooks(fileName).Worksheets(j).Range("A" & X - 7 & ":A " & X & ", D" & X - 7 & ":D " & X).Copy
            
            ThisWorkbook.Sheets("Sheet1").Activate

            ActiveSheet.Paste Destination:=ThisWorkbook.Worksheets("Sheet1").Range("A" & i)

1100:   End If
    
    j = j + 1
    
Next sheet

Workbooks(fileName).Close

fileName = Dir()

Loop

It has taken me a while to make sense of what I hear at work involving computers. There is much talk of bugs and questions about Raid.
Therefore I have come to the logical conclusion that the only way to have a properly functioning computer is to regularly spray it with Raid bug killer.

 

[SkipVought]

'
    Do While Filename <> ""
    
        With Workbooks.Open(directory & Filename)
        
            For Each Sheet In .Worksheets
            
                If Sheet.Name <> "IOList Base" Then
                
                    If Left(UCase(Sheet.Name), 2) = "IO" Then
                    
                        Sheet.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                
                    End If
                    
                End If
                                
            Next Sheet
            
            .Close
        
        End With
        
        Filename = Dir()
    
    Loop

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]