Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Fast Search Multiple Workbooks with Wilcard

0

I'm trying to use the Fastsearch multiple workbook macro and would like to use a wilcard in the search box. Eg, search for any records containing FOR*. Is this possible? When i try it doesn't return any records so i assume it is not treating the wildcard properly.

Also, is it possible to insert the filename into the dashboard of returned results so i can from which file the results are coming from?

Answer
Discuss

Discussion

Hi yani1shu,
Welcome to the forum. Don's the one here who answers questions like yours. The rest of us have little idea of what you are talking about and would need to see your actual code in order to help. So, if you don't want to wait for Don please upload your workbook but bear in mind that this is a public place.
Variatus (rep: 4889) Jan 16, '21 at 7:57 pm
Yani1shu.
I've gven an answer (and file) below but please be sure to read the version wiich includes Revision 2 (my full answer, awaiting any alternative Don may suggest)
John_Ru (rep: 6102) Jan 17, '21 at 7:17 am
JR, Like operator seems like a good choice.  And to OP, yes please at least post a link to what you're talking about next time so it will be easier for others to help)
don (rep: 1989) Jan 17, '21 at 12:57 pm
@Don- Thanks. It would be good if a question (raised from a tutorial page) could have its link added automatically to the draft question, for keeping or deletion by the user, there've been a few where I've searched to see if a tutorial is the source of the query . What does your mention of OP stand for in this context please?
John_Ru (rep: 6102) Jan 17, '21 at 1:14 pm
OP= Original Poster and just means the person who made the first post, at least I think lol. For similar tutorials, there is a little section to the left of the tutorial with the title "Excel Tutorials" and that should, if I remember correctly, take the title of the forum question and return search results from the tutorials on TeachExcel.
don (rep: 1989) Jan 17, '21 at 3:36 pm
Add to Discussion

Answers

0
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:

  1. wildcards can be used
  2. 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.

Discuss

Discussion

That code worked great. 

One more question. Currently, for some reason the field i'm searching on (searchfield=3), the value in that field isn't being returned to the Dashboard. 

How do i upload a file on this forum?  
yani1shu (rep: 2) Jan 17, '21 at 1:39 pm
Yani1shu. 
Thanks. If the code worked, kindly select the answer. 
If you have a supplementary question then you can add a file to a question. Files can be added to Answers too but not discussions. Screenshot can't be uploaded. 
John_Ru (rep: 6102) Jan 17, '21 at 1:43 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login