Selected Answer
Yani1shu
REVISION 1- just realised I've only answered the second part of your question (below) REVISION 2- Have fixed that in the revised Answer below.
I assume you are referring to Don's tutorial Fast Search Multiple Workbooks in Excel with a File Picker - VBA Macros. If so, I've done quick hacks so that:
- wildcards can be used
- the workbook name appears under the column Workbook in the output data array.
Item 1 is done in Sub Data_Search(wbMaster As Workbook, wbSlave As Workbook) by changing the comparison If (dataArray(i, searchField) = searchValue) Then to use the Like operator (plus LCase to convert both strings to lowercase and give a case-insensitive string comparison, so Name and "j??" will return records from tutorial files test1.xlsx and test2.xlsx for Joe and Jim but not John) - see the bit in bold in the code extract below:
For i = 1 To UBound(dataArray, 1)
'Check if the value in the row equals our search value (including any wildcards)
If LCase(dataArray(i, searchField)) Like LCase(searchValue) Then
I've done item 2 by replacing the third value to be captured (from the field "Workbook" in the searched files) with the name of each file. Near the end of the routine Sub Data_Search(wbMaster As Workbook, wbSlave As Workbook), I've simply added the bits in bold below to the loop that writes the column data into the output array...
For k = 1 To UBound(dataArray, 2)
'Add values to the array that will be used to put data into the Dashboard.
If k = 3 Then ' test and replace Workbook field with file name
datatoShowArray(j, k) = wbSlave.Name
Else: datatoShowArray(j, k) = dataArray(i, k)
End If
Next k
It's crude but it works- try it in the attached file.
Hope this helps but, as Variatus says, Don may have a much better way of amending his file.