Excel VBA search based on cell values into folders and sub-folders to get the file path and data

0 votes
Our system generates a daily report that is generated in xlm format to "C:Reports," which is divided into subfolders for the months and days and contains columns for the invoice number and the serial number.

I have to check more than 30 serial numbers every day to see if an invoice has been generated for them or not. I'm listing the serial numbers in a new workbook's column A, then copying and pasting each one into Windows Explorer one at a time to search the directory. If I find a match, I open that file, re-search for the same serial number, and copy the invoice number from column A to my worksheet.
Jan 19 in Others by Kithuzzz
• 27,740 points
31 views

1 answer to this question.

0 votes

This will create a listing of all the daily files on one sheet. You could use this with a VLookup from your list of invoices.

Option Explicit

Sub process_folder()

    Dim wb As Workbook, ws As Worksheet
    Set wb = ThisWorkbook
    
    ' results sheet
    Set ws = wb.Sheets(1)
    ws.UsedRange.Clear
    ws.Range("A1:D1") = Array("Serial No", "Invoice", "Path", "Workbook")
       
    ' create FSO Filesystem object
    Dim fso As Object, ts As Object, regEx As Object, txt As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'Opens the folder picker dialog to allow user selection
    Dim myfolder, myfile As String
    Dim parentfolder As String, oParent, rng As Range
    Dim iRow As Long, r As Long, n As Long
   
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\"
        .Title = "Please select the reports folder"
        .Show
        .AllowMultiSelect = False
        If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
            MsgBox "You did not select a folder"
            Exit Sub
        End If
        parentfolder = .SelectedItems(1) 'Assign selected folder to ParentFolder
    End With
    Set oParent = fso.getFolder(parentfolder)
    
    ' build collection of files
    Dim colFiles As Collection
    Set colFiles = New Collection
    Call GetFiles(oParent, "xlsm", colFiles)
    
    'Loop through all files in collection
    Application.ScreenUpdating = False
    iRow = 2
    For n = 1 To colFiles.Count
        myfile = colFiles(n)
    
        ' open file
        Set wb = Workbooks.Open(myfile, ReadOnly:=True)
        
        ' copy Column A and B
        Set rng = wb.Sheets(1).UsedRange.Resize(, 2)
        r = rng.Rows.Count
        ws.Cells(iRow, 1).Resize(r, 2) = rng.Value2
        wb.Close

        ' folder and file name
        ws.Cells(iRow, 3).resize(r) = fso.getParentFolderName(myfile) ' path
        ws.Cells(iRow, 4).resize(r) = fso.getFileName(myfile) ' no path
              
        iRow = iRow + r
        
    Next
    Application.ScreenUpdating = True
    MsgBox colFiles.Count & " Files process", vbInformation

End Sub

Sub GetFiles(oFolder, ext, ByRef colFiles)

    Dim f As Object
    For Each f In oFolder.Files
        If f.Name Like "*." & ext Then
            colFiles.Add oFolder.Path & "\" & f.Name
        End If
    Next
    
     ' call recursively fro subfolders
    For Each f In oFolder.subfolders
        Call GetFiles(f, ext, colFiles)
    Next
     
End Sub
answered Jan 19 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

Excel VBA: Trying to read all files in folder based on cell input and output to another cell

Your array has no capacity for data ...READ MORE

answered Jan 24 in Others by narikkadan
• 51,240 points
37 views
0 votes
1 answer

Uipath(RPA) : read data from the PDF file and write to Excel file

If you want to use UiPath and ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 51,240 points
154 views
0 votes
1 answer

Excel function to dynamically SUM UP data based on matching rows and columns

Excel 365 for MAC should have the BYCOL function, Given: Your ...READ MORE

answered Jan 21 in Others by narikkadan
• 51,240 points
41 views
+1 vote
1 answer

How to use VBA in Excel for Google Search?

Try this: Private Const LicenseRegistration As String = ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 51,240 points
316 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,382 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
110 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 51,240 points
175 views
0 votes
1 answer

How to get the excel file name / path in VBA

Use FullName, for example: strFileFullName = ThisWorkbook.FullName ...READ MORE

answered Dec 23, 2022 in Others by narikkadan
• 51,240 points
60 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP