Solved Macro to find text string in multiple Excel docs

March 7, 2017 at 14:37:04
Specs: Windows 7
Hi,
I need a Macro to find multiple text strings in multiple Excel docs, adding a tag column (column L), tagging the found text strings in column L with a 1, then sort by 1 to display only the tagged strings.

The search strings are the same in all documents, but I have close to 800 documents to go through. Been doing it manually, but it's time consuming.

The text strings I am looking for are these:
6'L X 30"H DRAPED TABLE
9' X 10' CARPET
BLACK DIAMOND ARM CHAIR
WAREHOUSE-CRATED-ST
SHOWSITE-CRATED-ST
INSTALL - CARPENTER LABOR - ST
INSTALL - CARPENTER LABOR - OT
INSTALL - CARPENTER LABOR - DT
INSTALL - DISPLAY LABOR - ST
INSTALL - DISPLAY LABOR - OT
INSTALL - DISPLAY LABOR - DT
INSTALL - DECORATOR LABOR - ST
INSTALL - DECORATOR LABOR - DT
INSTALL - DECORATOR LABOR - OT

The final product should display each entire row with all the columns, however only the tagged ones. Not all above items may be in all documents. If not there they need to be ignored. I tried my butt of, but keep getting the debugger, and I am just not that familiar with it.

A sample file is placed here: http://www77.zippyshare.com/v/q7wih...
Thanks for any help!


See More: Macro to find text string in multiple Excel docs

Report •

✔ Best Answer
March 8, 2017 at 06:30:55
Why didn't you comment on the typo? ;-)

I corrected this line in my previous response:

Application.ScreenUpdating = fasle

to be:

Application.ScreenUpdating = False

This is the part of the code that loops the Find to find all occurrences:

       Set sStr = .Find(myArray(aryNum), Lookat:=xlWhole)
        If Not sStr Is Nothing Then
          firstAddress = sStr.Address
          Do
            sStr.EntireRow.Hidden = False
            Set sStr = .FindNext(sStr)
          Loop While Not sStr Is Nothing And sStr.Address <> firstAddress
        End If

Reduce it to just the .Hidden = False instruction

        Set sStr = .Find(myArray(aryNum), Lookat:=xlWhole)
         If Not sStr Is Nothing Then
            sStr.EntireRow.Hidden = False
         End If

That version will move on to the next search string once the first occurrence of each search string is found.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 7, 2017 at 20:21:32
A couple of questions:

You said "The search strings are the same in all documents, but I have close to 800 documents to go through."

What are you calling a "document"? Do you have 800 Excel workbooks? If so, does each workbook only contain 1 sheet or do multiple sheets within all 800 workbooks need to be searched?

You said: "...then sort by 1 to display only the tagged strings"

Sorting by 1 will not "display only the tagged strings", it will simply group all the 1's together.

The you said: "The final product should display each entire row with all the columns, however only the tagged ones. "

Are you saying that you want to delete any row that does not contain one of the search strings?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 7, 2017 at 20:41:45
Yes, 800 workbooks each has only one tab. Grouping is fine, meant the same thing. No deleting of the other rows, just hidden I guess. Thanks a bunch.

Report •

#3
March 8, 2017 at 03:41:58
If you want only the "found" rows visible, then the 1 tag is not required. The code could hide all the rows at the start and then make the "found" rows visible.

Is that acceptable or do you need the 1 tag for some other purpose?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 8, 2017 at 05:55:13
The code posted below should do what I mentioned in Response #3: Hide all rows and then Unhide any row that contains one of the search strings in Column F.

Assuming that all 800 workbooks are in a single folder, you should be able to place this code inside a macro that opens each workbook, performs the Hide/Unhide task and then saves the workbook.

One such example of "loop through folder code" can be found here:

https://www.thespreadsheetguru.com/...

There are many other options available via a web search.

One additional comment on the code below. You might find this line a little strange:

"6'L X 30" & """" & "H DRAPED TABLE", _

The reason for all the extra double-quotes is that VBA uses double quotes to denote a Text String. When it sees the inch designation (") it assumes that the symbol is the end of a text string, not part of the Text String. VBA requires 4 double quotes in order to designate a single double quote as a Text String. Another option is to use Chr(34).

"6'L X 30" & Chr(34) & "H DRAPED TABLE"

Let me know if you have any questions.

Sub Find_Stuff()
Dim myArray As Variant
Dim sStr As Range, aryNum As Long
'Build array of search strings
  myArray = Array( _
   "6'L X 30" & """" & "H DRAPED TABLE", _
   "9' X 10' CARPET", _
   "BLACK DIAMOND ARM CHAIR", _
   "WAREHOUSE - CRATED - ST", _
   "SHOWSITE - CRATED - ST", _
   "INSTALL - CARPENTER LABOR - ST", _
   "INSTALL - CARPENTER LABOR - OT", _
   "INSTALL - CARPENTER LABOR - DT", _
   "INSTALL - DISPLAY LABOR - ST", _
   "INSTALL - DISPLAY LABOR - OT", _
   "INSTALL - DISPLAY LABOR - DT", _
   "INSTALL - DECORATOR LABOR - ST", _
   "INSTALL - DECORATOR LABOR - DT", _
   "INSTALL - DECORATOR LABOR - OT")
     Range("A1").Activate
      Application.ScreenUpdating = False
'Determine range of cells to Hide
    lastRw = Range("A" & Rows.Count).End(xlUp).Row
    Range("A2:A" & lastRw).EntireRow.Hidden = True
'Loop through Array, searching Column F for each element
'Unhide row when element found
   For aryNum = 0 To UBound(myArray)
     With Columns("F")
       Set sStr = .Find(myArray(aryNum), Lookat:=xlWhole)
        If Not sStr Is Nothing Then
          firstAddress = sStr.Address
          Do
            sStr.EntireRow.Hidden = False
            Set sStr = .FindNext(sStr)
          Loop While Not sStr Is Nothing And sStr.Address <> firstAddress
        End If
     End With
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#5
March 8, 2017 at 06:06:38
Thank you so very much. One question, is there an easy way to eliminate the duplicates when the Macro runs? I should have mentioned it, I only need each string displayed once. Sorry about that.

Report •

#6
March 8, 2017 at 06:30:55
✔ Best Answer
Why didn't you comment on the typo? ;-)

I corrected this line in my previous response:

Application.ScreenUpdating = fasle

to be:

Application.ScreenUpdating = False

This is the part of the code that loops the Find to find all occurrences:

       Set sStr = .Find(myArray(aryNum), Lookat:=xlWhole)
        If Not sStr Is Nothing Then
          firstAddress = sStr.Address
          Do
            sStr.EntireRow.Hidden = False
            Set sStr = .FindNext(sStr)
          Loop While Not sStr Is Nothing And sStr.Address <> firstAddress
        End If

Reduce it to just the .Hidden = False instruction

        Set sStr = .Find(myArray(aryNum), Lookat:=xlWhole)
         If Not sStr Is Nothing Then
            sStr.EntireRow.Hidden = False
         End If

That version will move on to the next search string once the first occurrence of each search string is found.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
March 8, 2017 at 10:03:29
This is so awesome. Thank you so much Sir! Truly appreciate your help. You just saved me days of work!

So it would be advisable to save all spreadsheets in just one folder? I guess that would eliminate having to recreate the Macro over and over again?


Report •

#8
March 8, 2017 at 11:24:57
Yes. If you wrap a "folder search" macro around the code I suggested, it can run through every file - Open/Filter/Save/Close in a given folder.

However, I don't know that I would run it against 800 workbooks all at once. Call me chicken, but I don't think I'd trust any macro or computer to not run into some sort of issue when trying make changes to 800 workbooks, one after the other after the other after the other.

50 at a time, maybe, 25 definitely. That way, if it fails, or hangs the system, or in some other way acts badly, I'd know sooner and the impact wouldn't be as great.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
March 8, 2017 at 11:25:09
Hi again,
For some reason the following does not pull:
"WAREHOUSE - CRATED - ST", _
"SHOWSITE - CRATED - ST", _

It is in my sample file. I uploaded the file with the Macro if that makes it easier.

http://www87.zippyshare.com/v/daJOC...

Sorry to be such a pain with this.

If it does not work, would it be feasible to do something where it just fills in the 1 as a tag in column L for each found item, but just once meaning no duplicates? Not sure that makes sense or not.

message edited by melstefs


Report •

#10
March 8, 2017 at 11:28:35
I wouldn't go that far. As long as don't have to recreate the Macro over and over again it'll be fine. I have not tried it, but if I leave the first file open, does the Macro show up as available in any other workbook I open subsequently?

Report •

#11
March 9, 2017 at 12:13:47
re: "I have not tried it, but if I leave the first file open, does the Macro show up as available in any other workbook I open subsequently?"

Yes and no.

Google personal macro workbook. You'll get lots of hits on how to create a workbook that you can store macros in so that they are available in all open workbooks. If you hide the workbook and store it in the xlstart folder, it will open in the background, hidden, whenever you open Excel and all macros stored in it will be available.

I have about 20 macros that I use almost daily. I have icons for them on my Excel Quick Access toolbar. All of the macros are stored in my personal.xlsb workbook so they are available with a click of an icon in any open workbook.

re: For some reason the following does not pull:
"WAREHOUSE - CRATED - ST", _
"SHOWSITE - CRATED - ST", _

I do not have access to zippyshare right now and may not until tomorrow.

I would copy those strings to the standard Excel search dialog box and see if they are found. All it takes is an extra space or hidden character in the cell for Excel be confused.You might try changing this:

Lookat:=xlWhole

to this:

Lookat:=xlPart

If there are extra characters in the cell, xlWhole won't find the string because the entire cell must match the search string. xlPart (Part stands for Partial) will find the string anywhere in the cell.

e.g.

Search String = It Is Sunny
Cell Contents = Today, It Is Sunny

xlWhole will not find Today, It Is Sunny but xlPart will because It Is Sunny makes up part of the cell contents.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
March 9, 2017 at 12:31:44
Great suggestion Sir! I'll check it out.

Regarding the Lookat: I tried the xlPart and it did not pull those two strings. There's nothing unusual about these two strings that I can see. I also deleted the - ST to see if that makes a difference, it did not.

The xlPart I think works better because sometimes there can be variances in the way items are named. i.e.
"WAREHOUSE - CRATED - ST could also be "WAREHOUSE - CRATED - OT

Warehouse crated and showsite crated are the terms that should always be contained in the description (string)

Thank you for getting back with me! If you can find a solution that would be great. It already saves me time now because I only have to look for two items instead of 7. However, I do need to do it with a tag because the Macro does not pull all of them. If you can get it to work I can eliminate that altogether and just save the file after the Macro ran.


Report •

#13
March 10, 2017 at 05:32:54
Just an update...I still don't have access to zippyshare. The issue is two-fold:

1 - I can not access zippyshare at work due to corporate security measures.

2 - I have no internet access at home because I was one of the 1.2 million people that lost power due to the high winds that hit parts of the Eastern US last week. We got power back late last night but no internet or cable as of this morning. Hopefully it will be up soon now that the power poles in my neighborhood have been replaced and the power lines restrung.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#14
March 10, 2017 at 06:17:06
By all means, take care of yourself and your family and friends. Sorry to hear about the challenges. Been through that myself far too many times. I live on the Gulf coast. Take care and good luck with everything.

Report •

#15
March 10, 2017 at 07:10:24
I feel for you!

The whole time I was picking up branches and hooking the generator to the furnace, I kept telling myself "At least I'm not sitting on top of my roof waiting for somebody in a row boat to come rescue me" and "Hey, at least I still have a roof!"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question