Gather data from specific sheets that have different names

[Joe9238]

Hi,
I have a code that will go through other workbooks and gather data from a specific sheet called 'client' which each workbook has. Some of the sheets now have an addition to their name called 'client quote'. Would it be possible to gather the data from any sheets that contain the word client or client quote without having it panic and fail?

This is what I have so far:

Option Explicit


Public Sub GatherData()


Dim codes As Range, code As Range
Dim folder As String, fileName As String

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & ""
.Title = "Please select the folder containing CLIENT QUOTE workbooks" This part selects the folder which the files are in.
.Show
If .SelectedItems.Count = 0 Then Exit Sub
folder = .SelectedItems(1) & ""
End With

With ActiveSheet
.Range("A1:D1").Value = Array("Quoted By", "Quoted On", "Client Name", "Email Address")
Set codes = .Range("A2", .Cells(Rows.Count, "A").End(xlUp)) 'codes in column z starting in A2
End With

For Each code In codes
fileName = Dir(folder & "*" & code.Value & "*.xlsx*")
If fileName <> vbNullString Then
code.Offset(0, 0).Value = GetCellValue(folder & fileName, "QUOTE", "B8") This is where I need it to say 'look for quote OR client quote'
code.Offset(0, 1).Value = GetCellValue(folder & fileName, "QUOTE", "B9")
code.Offset(0, 2).Value = GetCellValue(folder & fileName, "QUOTE", "B12")
code.Offset(0, 3).Value = GetCellValue(folder & fileName, "QUOTE", "B14")
End If
Next
End Sub

Private Function GetCellValue(ByVal workbookFullName As String, sheetName As String, cellsRange As String) As Variant


Dim folderPath As String, fileName As String
Dim arg As String

'Make sure the workbook exists

If Dir(workbookFullName) = "" Then
GetCellValue = "File " & workbookFullName & " not found"
Exit Function
End If

folderPath = Left(workbookFullName, InStrRev(workbookFullName, ""))
fileName = Mid(workbookFullName, InStrRev(workbookFullName, "") + 1)

arg = "'" & folderPath & "[" & fileName & "]" & sheetName & "'!" & Range(cellsRange).Address(True, True, xlR1C1)
Debug.Print arg

'Execute Excel 4 Macro with argument to closed workbook

GetCellValue = ExecuteExcel4Macro(arg)

End Function



Additionally, is there any way I can get it to search all the sub folders within the folder too?
Thanks in advance

 

[Joe9238]

Ignore this. I found a way around it.