Workbook data search

[billyshears]

Lets say I have 100 different workbooks. All of them are setup the same but have different data.

Is there a way to have one excel sheet, search information within the 100 worksheets and return an answer?

For instance, if cell A1 in each worksheet was the name of a person and Cell A2 was their age

Can my open workbook search all the closed workbooks and give me the average age of a person named Jim?

 

[baitmaster]

Yes, but you'd need to do have all your workbooks together in a specific folder, then create a VBA script that opens each file in turn, pulls out the required data, and performs the task you want of it - or at least creates data tables on which to perform calculations later

This will take time, and you don't want to have to repeat the loop, so if you want multiple criteria then you should pull everything out at once

If you provided the folder name, and the specific sheets / cells you want to pull data from, plus what you want to do with it, it should be easy enough to write some code that demonstrates it

 

[billyshears]

Yes, but you'd need to do have all your workbooks together in a specific folder, then create a VBA script that opens each file in turn, pulls out the required data, and performs the task you want of it - or at least creates data tables on which to perform calculations later

This will take time, and you don't want to have to repeat the loop, so if you want multiple criteria then you should pull everything out at once

If you provided the folder name, and the specific sheets / cells you want to pull data from, plus what you want to do with it, it should be easy enough to write some code that demonstrates it

The files are all saved in same file. Do you need the names of each individual file?

 

[baitmaster]

No, just the folder name. VBA can be told to loop through EVERY file in that folder and perform actions on it. If you are looking for specific data that is always in the same place, it will be easy to drag that data out and place it in your main file. For example this code works on every file in "test folder" located next to this file, and pulls the file name plus values from some fixed ranges. Adapt it to suit your needs

Sub importFromAllFilesInFolder()

Dim srcFolder As String, sourceFileNames As String
Dim counter As Integer, i As Integer
Dim srcRange As Range
'Application.ScreenUpdating = False ' commented out to keep screen active for demo purposes

srcFolder = ThisWorkbook.Path & "\test folder\"
sourceFileNames = Dir(srcFolder & "*.xls*")
counter = 1

Do While sourceFileNames <> ""
    
    With Workbooks.Open(srcFolder & sourceFileNames)

        For i = 1 To .Sheets("sheet1").Range("srcCount").value
            counter = counter + 1
            ThisWorkbook.Sheets("Sheet1").Cells(counter, 1).value = .Name
            ThisWorkbook.Sheets("Sheet1").Cells(counter, 2).value = .Sheets("sheet1").Range("srcData").Cells(i, 1).value
            ThisWorkbook.Sheets("Sheet1").Cells(counter, 3).value = .Sheets("sheet1").Range("srcData").Cells(i, 2).value
            ThisWorkbook.Sheets("Sheet1").Cells(counter, 4).value = .Sheets("sheet1").Range("srcData").Cells(i, 3).value
            ThisWorkbook.Sheets("Sheet1").Cells(counter, 5).value = .Sheets("sheet1").Range("srcData").Cells(i, 4).value
        Next I

        .Close
    End With
    sourceFileNames = Dir
Loop

'Application.ScreenUpdating = True

End Sub

 

[billyshears]

Another thought...would it be easier if I named the cells or range within the other workbooks so it wouldnt matter what cell? it should just find the named range/cell in that workbook no matter what cells they were correct?

No, just the folder name. VBA can be told to loop through EVERY file in that folder and perform actions on it. If you are looking for specific data that is always in the same place, it will be easy to drag that data out and place it in your main file. For example this code works on every file in "test folder" located next to this file, and pulls the file name plus values from some fixed ranges. Adapt it to suit your needs

Sub importFromAllFilesInFolder()

Dim srcFolder As String, sourceFileNames As String
Dim counter As Integer, i As Integer
Dim srcRange As Range
'Application.ScreenUpdating = False ' commented out to keep screen active for demo purposes

srcFolder = ThisWorkbook.Path & "\test folder\"
sourceFileNames = Dir(srcFolder & "*.xls*")
counter = 1

Do While sourceFileNames <> ""
    
    With Workbooks.Open(srcFolder & sourceFileNames)

        For i = 1 To .Sheets("sheet1").Range("srcCount").value
            counter = counter + 1
            ThisWorkbook.Sheets("Sheet1").Cells(counter, 1).value = .Name
            ThisWorkbook.Sheets("Sheet1").Cells(counter, 2).value = .Sheets("sheet1").Range("srcData").Cells(i, 1).value
            ThisWorkbook.Sheets("Sheet1").Cells(counter, 3).value = .Sheets("sheet1").Range("srcData").Cells(i, 2).value
            ThisWorkbook.Sheets("Sheet1").Cells(counter, 4).value = .Sheets("sheet1").Range("srcData").Cells(i, 3).value
            ThisWorkbook.Sheets("Sheet1").Cells(counter, 5).value = .Sheets("sheet1").Range("srcData").Cells(i, 4).value
        Next I

        .Close
    End With
    sourceFileNames = Dir
Loop

'Application.ScreenUpdating = True

End Sub

 

[baitmaster]

That would work in theory

named ranges are harder to work with in separate workbooks, because you need to know what worksheet they are on - you don't when they are in the same workbook. It's possible to work that out though by looking at it's "refersto range", it's just slightly more complex. It's also vulnerable to potential error if you use move/copy to duplicate a worksheet containing the named range, and thus duplicate that name within the workbook

You could also use Excel's Find (Ctrl & F) feature if you have a unique string to search for. I'm currently helping someone else with Find, see this thread for detailed code example: http://www.mrexcel.com/forum/excel-...sheet-text-value-return-other-cell-sheet.html

Either approach would probably work just fine, subject to a little coding. Combining the code I gave you above with the Find code in the other thread would probably answer most of your question