Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    looking up for a cell in multiple files

    Hello-I have Excel 2010 and would like to know if there is a way to accomplish the following goal. I have an excel file that I would like to put a formula to search other excel files in other folder or sub folders. Once it finds the value I need, I need it to return the value of the cell next to it. The difficult part is that I need to search a folder which could contain multiple sub folders and files. For example, I have a folder called "Vendor" within each vendor I have another folder for each fiscal year and within that folder I have a file called Invoice.xls. For each sub folder in the "Vendor" folder, there will always be a file called Invoice.xls. Any help would be great. Thanks.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Seba,

    In this workbook the code is initiated by clicking the "Get Files" button. A form will open and ask you to enter the path and the search criteria. Once entered, it will search all .xls, .xlsx, and .xlsm files in the folder and subfolders beneath it. Within each workbook it will search all worksheets for the search criteria you are looking for and return the value of the adjacent cell to its right.

    SearchExcel1.png

    HTH
    Maud

    Code:
    Dim iRow
    
    Sub ListFiles()
    'LISTFILES AND LISTMYFILES MODIFIED FROM
    'http://excelexperts.com/VBA-Tips-List-Files-In-A-Folder
        iRow = 2
        '--------------------------------------------------------------------
        'CLEAR SEARCH RESULTS
        LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).row
        Range(Cells(2, 1), Cells(LastRow + 1, 4)).ClearContents
        '--------------------------------------------------------------------
        'DEFAULT PATH FROM HIDDEN SHEET
        Call ListMyFiles(Worksheets("Settings").[a1].Value, True)
    End Sub
    
    Sub ListMyFiles(mySourcePath, IncludeSubfolders)
        Set MyObject = New Scripting.FileSystemObject
        Set mySource = MyObject.GetFolder(mySourcePath)
        Dim cellnum As String
        Application.ScreenUpdating = False
        '--------------------------------------------------------------------
        'FIND EXCEL FILES ONLY, APPLY SEARCH CRIERIA, DISPLAY MATCHES ONLY
        For Each myfile In mySource.Files
            If Right(myfile.Name, 3) = "xls" Or Right(myfile.Name, 3) = "XLS" Or Right(myfile.Name, 4) = "xlsx" _
            Or Right(myfile.Name, 4) = "XLSX" Or Right(myfile.Name, 4) = "xlsm" _
            Or Right(myfile.Name, 4) = "XLSM" Then 'DETERMINE IF EXCEL FILES
                Workbooks.Open Filename:=myfile
                With Workbooks(myfile.Name)
                num = .Worksheets.Count
                For I = 1 To num
                .Worksheets(I).Activate
                On Error Resume Next
                cellnum = .ActiveSheet.Cells.Find(What:=SearchExcel.TextBox2, After:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Address
                If cellnum = "" Then GoTo skip
                ThisWorkbook.Activate
                Cells(iRow, 1).Value = myfile
                Cells(iRow, 2).Value = .Worksheets(I).Name
                Cells(iRow, 3).Value = SearchExcel.TextBox2.Value
                Cells(iRow, 4).Value = Workbooks(myfile.Name).Worksheets(I).Range(cellnum).Offset(0, 1).Value
                cellnum = ""
                iRow = iRow + 1
    skip:
                Next I
                Windows(myfile.Name).Close
                End With
            End If
        Next
        If IncludeSubfolders Then 'SEARCH SUBFOLDERS FOR SAME CRITERIA
            For Each MySubFolder In mySource.SubFolders
                Call ListMyFiles(MySubFolder.Path, True)
            Next
        End If
        SearchExcel.Hide
        Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-11-03 at 21:28.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maudibe

    ..so my favourite Excel file format .xlsb is completely ignored???

    zeddy

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Zeddy,

    Thanks for picking up on that but somehow I don't think Seba is into binary files. But If so, Seba, change the If statement to:

    Code:
    If UCase(Right(myfile.Name, 3)) = "XLS" Or UCase(Right(myfile.Name, 4)) = "XLSX" Or UCase(Right(myfile.Name, 4)) = "XLSM" _
            Or UCase(Right(myfile.Name, 4)) = "XLSB" Then 'DETERMINE IF EXCEL FILES

  5. #5
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Maudibe-One quick question. So I want the search to look up all the values say in column B. Instead of giving it a search criteria, I would like it to look at all values in column B in the spreadsheet where the coding goes. Once it finds the value I want it to return the value in column d. I think it would make more sense if I explain what I am trying to do. So I have a summary worksheet (which would be where the coding goes) that I need to match invoice number from individual files (thus the search), but I don't want it to return the invoice number, instead I want it to return the date paid which is not in the summary worksheet. For example, say the coding looks at the summary worksheet for invoice number 3 and finds it in a file. I need it to return the value in that row, but from column d which would be the date paid. Any help would be great or I am stuck having to look at 1500 files.

    Note: When I used the code above, I got a few files that say something about having to recalculate formulas from a prior excel version and it would ask me to save it or not which would stop the macro and it would continue until I answer it. How can the code be alter to say no to the option of saving the file. Also, Is there a way to write it so when it finds the matching value it stops searching. Thanks again.
    Last edited by Seba; 2013-11-05 at 01:37.

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Seba,

    Nope, you are not stuck at looking at 1500 files; the code will do that for you. But before I amend the workbook, I want to make sure I know what you want.

    If I understand you correctly, you would like to use each of the values in column B (invoice numbers) of your Summary workbook as the search criteria. For each of these invoice numbers , it will search the files in a folder and its subfolders for the invoice number in column B and if a match is found, it will return the date paid which is in Column D for each. Back in the Summary sheet, it will place the date in the same row as the invoice number but in column D. Is this the general workflow?

    I will need to know a couple of things:
    1. Are the files named by invoice number or does the file name give an indication of which invoice it contains? Or is it really just Invoice.xls as stated in your original post?
    2. In the Invoice files, does Column B also contain the invoice number?
    3. Will the invoice number always present on the same row in each of the files?
    4. Will the search invoice number always be in the same format as the matching Invoice number in the invoice files?
    5. will the date paid will be on the same row as the invoice number but in column D in the Invoice files?
    6. Are all the invoices setup exactly the same? I need to know where to match and where to grab.
    7. Is there only one invoice in each invoice file?
    8. On what row in column B in the summary workbook do the invoice numbers start?

    If you could send me a sample of a summary sheet and an invoice, it would greatly help. The code would then be "Turn Key" for your project

    Maud
    Last edited by Maudibe; 2013-11-05 at 01:43.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maud-Thanks for getting back to me. I am going to see if I can send sample of both the summary workbook and invoice. I think your take on the general workflow is correct. I want to get a code to look at the summary sheet column d value and then search column B for each invoice file and give me the value in column D of the invoice file. I then want to plug this value in column E in the summary sheet. I am going to try to get a sample, but I might not be able to.

    The answers below are for your questions.
    1. Invoice.xls only.
    2. Yes.
    3. Yes.
    4. No sure what you mean by format? It could be text or general.
    5. Yes.
    6. Yes.
    7. Yes. It is a unique value.
    8. Row 2


    Thanks for all your help.

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Seba,

    I am almost finished. Just a couple things more. On the Summary sheet, you want the search invoice number now from column D and the result placed in column E? Since the invoice number is always located in the came cell on the Invoice sheets (question 3), what is the row (cell Bx)? Will the invoice always be on the first sheet of the Invoice workbook if there are more than one? The format of the invoice number is referring to:
    ex. 00501 VS. 501

    I don't think will need the sample, but thanks

    Maud

    UPDATE: Completed. Just need the cell address of the invoice numbers in the Invoice files.
    Last edited by Maudibe; 2013-11-05 at 23:03.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Maud-The answer to number three should be no (Sorry). However, the invoices number can be found in the same column in the invoice file. The invoice file(s) could have multiple worksheets. Format for the invoice number can change. For example, 5563, 5593-01, ph003, Katie01, Vendor 001, etc.

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    With multiple worksheets will it always be on worksheet 1? Will the format for each invoice number be the same between the summary sheet and the invoice file? For example, the summary sheet lists A501 but the invoice sheet lists the invoice number as A-501.
    Last edited by Maudibe; 2013-11-06 at 06:17.

  11. #11
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No. It could be in worksheet 2 or 3 or more. Yes the format on how the number was enter will be the same, but the format of the cell I am not too sure. Thanks.

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Well, it looks like you are attempting to do this with a vlookup in another post. Let me know if you were unsuccessful.

    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •