Search all files in folder for numerous strings

[marcosis123]

Hi,

I have a folder with a seperate workbook for all the branches in our company, Liverpool, Manchester etc.

In each workbook is a simple table that shows sales data, a sales number, name, email address, stock code etc.

I then have a master workbook that has a list of stock codes in it.

I need to be able to create a macro that searches through all of the workbooks and if it finds a matching value for any of the stockcodes, copy that row and paste it to a new worksheet in the master workbook with the title of the worksheet being the same name as the file name it found it in, with the rows of data throughout.

I had something similar that searched for one key phrase and returned a row, but i need it to search for any string from an entire row of strings.

Hope that makes sense and any help would be much appreciated.

Cheers

 

[JoshWolski]

Set it to loop through both the Master Stock Code range and Stock Codes of the worksheet if the location of these stock codes are consistent in each workbook.
Something Like should get you in the right direction, without knowing where all files are located or how everything is set up. This is 3 loops:
One that loop through each branches workbook, 2nd loops through all master stockcodes, 3rd loops through range of stockcodes on opened workbook.

Dim RowMatch As Long
Dim StockCode As Range
Dim StockCodeMatch As Range
Dim FSO As FileSystemObject
Dim Fldr As Folder
Dim WbFile As File


Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fldr = FSO.GetFolder(Put File Path where workbooks are located)


For Each WbFile In Fldr.Files
    If FSO.GetExtensionName(WbFile.Name) = "xls" Then
    Set Wb = Workbooks.Open(WbFile.Path)
    
    For Each StockCode In MasterWorkbook.SheetName.Range(AddRange)
        For Each StockCodeMatch In ActiveWorkbook.Sheet.Range(AddRange)
            If StockCode = StockCodeMatch Then
                RowMatch = StockCodeMatch.Row
                MatchArray = ActiveWorkbook.Sheet.Range(RowMatch, 1).EntireRow
                    With MasterWorkbook
                        Sheets.Add.Name = WbFile.Name
                        MasterWorkbook.Sheets(WbFile.Name).Range("A1") = MatchArray
                        .Save = True
                    End With
                    
        
            End If
        Next StockCodeMatch
    Next StockCode
    ActiveWorkbook.Close Savechanges:=False
Next WbFile

 

[marcosis123]

Hi Josh,

Thanks for your response. Im getting a next with for error with the Next WbFile part.

Any ideas where it needs fixing.

Also forgot to add and this might throw a spanner in the works, theyre password protected.

Hope this is ok.

Cheers

 

[marcosis123]

Set it to loop through both the Master Stock Code range and Stock Codes of the worksheet if the location of these stock codes are consistent in each workbook.
Something Like should get you in the right direction, without knowing where all files are located or how everything is set up. This is 3 loops:
One that loop through each branches workbook, 2nd loops through all master stockcodes, 3rd loops through range of stockcodes on opened workbook.

Dim RowMatch As Long
Dim StockCode As Range
Dim StockCodeMatch As Range
Dim FSO As FileSystemObject
Dim Fldr As Folder
Dim WbFile As File


Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fldr = FSO.GetFolder(Put File Path where workbooks are located)


For Each WbFile In Fldr.Files
    If FSO.GetExtensionName(WbFile.Name) = "xls" Then
    Set Wb = Workbooks.Open(WbFile.Path)
   
    For Each StockCode In MasterWorkbook.SheetName.Range(AddRange)
        For Each StockCodeMatch In ActiveWorkbook.Sheet.Range(AddRange)
            If StockCode = StockCodeMatch Then
                RowMatch = StockCodeMatch.Row
                MatchArray = ActiveWorkbook.Sheet.Range(RowMatch, 1).EntireRow
                    With MasterWorkbook
                        Sheets.Add.Name = WbFile.Name
                        MasterWorkbook.Sheets(WbFile.Name).Range("A1") = MatchArray
                        .Save = True
                    End With
                   
       
            End If
        Next StockCodeMatch
    Next StockCode
    ActiveWorkbook.Close Savechanges:=False
Next WbFile

Hi Josh,

Thanks for your response. Im getting a next with for error with the Next WbFile part.

Any ideas where it needs fixing.

Also forgot to add and this might throw a spanner in the works, theyre password protected.

Hope this is ok.

Cheers

 

[JoshWolski]

Add this reference in your library for FSO objects, if not turned on then excel will not recognize a File System Object

IF the password is always the same then modify the open workbook code to look something like this.

Modify: Set Wb = Workbooks.Open(WbFile.Path, Password:="PUTPASSWORDHERE").

Use the code below and I had forgotten an END IF section to the code.

Sub Text()

Dim RowMatch As Long
Dim StockCode As Range
Dim StockCodeMatch As Range
Dim FSO As FileSystemObject
Dim Fldr As Folder
Dim WbFile As File


Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fldr = FSO.GetFolder("C:\Users\Sloshua\Desktop\Test Folder")


For Each WbFile In Fldr.Files
    If FSO.GetExtensionName(WbFile.Name) = "xls" Then
    Set Wb = Workbooks.Open(WbFile.Path, Password:="PUTPASSWORDHERE")
    
    For Each StockCode In MasterWorkbook.SheetName.Range(AddRange)
        For Each StockCodeMatch In ActiveWorkbook.Sheet.Range(AddRange)
            If StockCode = StockCodeMatch Then
                RowMatch = StockCodeMatch.Row
                MatchArray = ActiveWorkbook.Sheet.Range(RowMatch, 1).EntireRow
                    With MasterWorkbook
                        .Sheets.Add.Name = WbFile.Name
                        .Sheets(WbFile.Name).Range("A1") = MatchArray
                        .Save = True
                    End With
                    
        
            End If
        Next StockCodeMatch
    Next StockCode
    ActiveWorkbook.Close Savechanges:=False
    End If
Next WbFile

End Sub

 

[JoshWolski]

Also depending on the files you are opening, excel obviously has many different type xls, xlsx, xlsm, xlsb

You would modify the if statement based on the file type of the excel documents you are pulling from.

If FSO.GetExtensionName(WbFile.Name) = "xls" Then '<--- update the XLS if this is not the correct file extension.