Checking specific text string in excel files stored in a folder

Hi,
I need to write a code that can do the following:
1) prompt user to select a particular folder
2) search for a particular text string, for e.g., ABCD in all the
excel files in that folder (the number of files in the folder will
vary and so does the names of the files). Is it possible to specify
the string that we want to search on the fly when macro is run?
3) create a simple report that contains the filename in one column and
yes / no in the second column that tell me which file names contain
that keyword (it will be great if the path of the file can be recorded
in the 3rd column that we can click to open the file).

Currently we do this manually and I am looking for a way to automate
the same.

Any help on this will be much appreciated. Thanks.
0
AG
3/16/2010 3:51:46 AM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
815 Views

Similar Articles

[PageSpeed] 37

One thing I forgot to mention is once the user selects the primary
folder then there will be subfolders that contain the excel files. The
name and number of subfolders and the excel files inside them both
vary. Capturing the path to the excel files will really help us.
Thanks.
0
AG
3/16/2010 4:00:48 AM
Hi,
can someone please help me with the above problem? Many thanks.

0
AG
3/17/2010 12:54:44 AM
Maybe you could use Chip Pearson's addin:
http://www.cpearson.com/Excel/FolderTree.aspx

Get your list (in column A) and use a formula like:
=countif(a1,"*abcd*")>0
in column B



AG wrote:
> 
> Hi,
> I need to write a code that can do the following:
> 1) prompt user to select a particular folder
> 2) search for a particular text string, for e.g., ABCD in all the
> excel files in that folder (the number of files in the folder will
> vary and so does the names of the files). Is it possible to specify
> the string that we want to search on the fly when macro is run?
> 3) create a simple report that contains the filename in one column and
> yes / no in the second column that tell me which file names contain
> that keyword (it will be great if the path of the file can be recorded
> in the 3rd column that we can click to open the file).
> 
> Currently we do this manually and I am looking for a way to automate
> the same.
> 
> Any help on this will be much appreciated. Thanks.

-- 

Dave Peterson
0
Dave
3/17/2010 2:25:26 AM
Thank you Dave. It helped a lot.
0
AG
3/18/2010 12:39:08 AM
Reply:

Similar Artilces:

Convert Cell Text to Matching Hyperlink?????
We have an Excel spreadsheet that contains information to scanned images being populated into our engineering database. In the spreadsheet there is information as to the creation time and date, size, title and other related information. One of the columns is the name and location of the actual TIFF image file. This field is just text. What we would like to do is convert the cell from just text stating the location and filename to a hyperlink pointing to the specific file. For example, the cell contents state : G: \folder2007\subfolder0320\00001234.tif We would like to still have the tex...

Large amount of cell text
Hi, We have a case where we have received records from a Hotmail subpoena. Microsoft has put the records in an Excel spreadsheet with all emails for a six month period being contained in a single column and tens of thousands of rows. The problem I am having is that some of the cells that contain the text from the email messages are very long, and makes reading of the cells difficult. An attempt to save to or publish to a different format for easier reading causes truncation of the text in those particular cells. Word Wrap, Autofit, nor simply double-clicking on the cell margin d...

merge MSN Bill Pay & Checking Acct
I want to merge my checking account with MSN Bill Pay but only see that I can merge MSN Bill Pay with my checking account (i.e., you lose all of your checking account information). Does any one have any suggestions? -- cheers, Tom G ...

VBA solution to paste text into megred cells
I do realize that merged cells are bad news. I'm stuck with an Excel 2003 worksheet, which contains this dreaded feature. Users are complaining that on trying to paste text into merged cells gives Excel Warnings: "Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste anyway?" And: "Cannot change part of a merged cell." Interestingly, though if the user hits F2, which places the cursor in the formula bar, then the paste operation works! I'm usually reasonably of Ok with VBA; but this ...

populate Keywords with a batch file?
I'd like to know how to create a batch file that can store a key word (including a trailing comma). I'd open an appropriate word.doc, run the batch file, and that keyword and comma would be inserted in the Keywords panel, either preceding any existing keywords there, or appended to any existing keywords there. So if I had multiple batch files, each embedded with their own unique keyword and comma, I could invoke certain ones as desired, one after another, progressively populating my document's keywords panel with multiple keywords all strung together with comma d...

Authorizing checks with RMS
I recently removed PCCharge and setup my syetm to work with a preferred merchant provider to allow native processing of credit & debit cards. Since check verification/authorization was handled by PCCharge to Telecheck, check authorization is now not being done. How is check authorization accomplished using a preferred provider? Thanks, Richard Unfortunately, it's not. PC Charge is the only option for check processing in RMS as far as I know. TPI or some other vendor may support it through an add-in, but RMS as shipped has no way of doing both checks and debit... -- -- Gle...

Easiest way to create forms with Excel?
Hi all - apologies if this is a FAQ question, was unable to find good answers there. My organization often needs to distribute various 'forms' designed in Excel. People will fill them in, (often print a copy and) send the filled-in sheets back to us. There are a lot of problems with this: people will modify stuff where they're not supposed to, will leave 'mandatory' options blank, and the process of copying/pasting data for further processing once we get the filled in sheet is laborious. For various reasons we need to stay with Excel (i.e. Access, or redoing everything a...

Outlook (and Excel) stops working after MSXML installation
I installed MSXML 4.0 as per the "critical updates" message on my W98 today. After re-boot, when I try to open Outlook I get message "Unable to open your defauilt mail folder - the information store could not be opened" and then "Would you like to open your default file system folder instead?" - this is no good and I can't find anything. I can't open Excel either, I get messages about Visual Basic and when I close it, I get message "This process will stop the debugger". I now have a shortcut on my desktop "Microsoft MXL 4.0 parser&...

How can I delete PART of a .wmv file?
I have recorded an interview. Unfortunately, I didn't properly turn the recording off and I captured an additional 3 hours of my daily life that I don't want to archive with this recording. How can I do that? You need an audio editor A nice piece of freeware like http://audacity.sourceforge.net/ will do -- http://thewelltemperedcomputer.com "kenyadee" wrote: > I have recorded an interview. Unfortunately, I didn't properly turn the > recording off and I captured an additional 3 hours of my daily life that I > don't want to archive ...

Check names not working for multiple address books / contacts
Hello! I have upgraded from outlook 2003 to outlook 2007, my contacts are stored in pst. I used to be able to search for a name via check names and it displayed me all the results from global address list and from my contacts, but now it only does one. It does not matter which one I set in the checking order, whichever is top will be the one that provides the result. I would like it to show me both results, how would I go about to set that up? Thank you Deana More accurate and complete information would be helpful. The Global Address List is only available to those who c...

How do I create Check list
1.I am new to excel and want to create a simple check list. I would like to have the first column check boxes and then the following columns discription and notes. Can someone give me a step by step on how to do this. I have created spread sheets but am not familiar with check boxes. 2.Also, Can someone tell me the difference between Access and Excel? Geek 1) Not sure what you're aiming to do with the checkboxes, but if you right click on the toolbar area, you will get a list of available toolbars. You want either the 'forms' toolbox or the 'controls' toolbox. Genera...

Excel; tabbing through protected worksheet
I have a protected worksheet with several sections of merged cells. When selecting cells prior to protecting I select in the order I want the user to tab. When leaving the first merged cell by tabbing, and the user is moved to the next cell tabbing from that cell bring you back to the previous merged cell. I am thinking that Excel thinks it needs to go to next row of the merged cell. Can I get over this hurddle and convince Excel to continue moving forward? Easy workaround. Remove the merged cells which cause no end of problems and limit functionality...............one ...

Check text field value against underlying table or query of combo box
Hello, I have a form on which the user can enter new records or update existing ones. If the user enters a new record on the form, I would like to have an 'After Update' event on one of the text fields, which is the item# to check if the value entered already exists in the underlying table, tbl_item. How can I call the table from the 'After Update' event of the text field and check for that value in the table? Thanks in advance. Regards, A. Crawford antcraw@swbell.net wrote: >I have a form on which the user can enter new records or update >existing ones. >If the us...

Public folders problems after migration to e2k3
Just did an migration from 2000 to e2k3 and Outlook clients can't access public folders. Just gives a generic error about not being able to open the folder. In OWA I can open the folders and see all the contents. I also get this error message in my Application log about FreeBusy and am thinking they might be related. Source:MSExchangeFBPublish Category: General Event IT:8197 Error initializing session for virtual machine $Mail$. The error number is 0x8004011d. Make sure Microsoft Exchange Store is running. I have seen others have had problems with Calendar Connector, and an unin...

Preventing multiple check boxes from being checked
I have a worksheet with over 20 rows of check boxes with 5 check boxes in each row. (from the forms toolbox) I would like to prevent the user from checking more than one box in each row. Is this possible? BTW, this forum has been a great help. I'm so glad that expert Excel users are so willing to share their wealth of knowledge. Thanks again. Jae Put them in a group box, again from the forms toolbar. Drag the group box onto the sheet, then drag the checkboxes in. -- HTH RP (remove nothere from the email address if mailing direct) "Jae" <Jae@discussions.micro...

ShellExecute() multiple files to open to one instance
I am opening DWG files to AutoDesks TrueView. When i loop to open these files, a new instance is created for each file i open. Is there a way to create a process and send files to it only? Or a registry key in windows i can set to not allow multiple instances of this program? My goal is to open multiples files to one instance of this viewer, it can handle many files at once. Thanks! check if the program has any command line switch or automation interface to reuse an existing instance. -- Regards Sheng Jiang Microsoft Most Valuable Professional in Visual C++ http://www.jiangsheng.net ...

Linking Chart items to files
Anyone got a clue on how to link each stacked bar(or similar) to a different file, so when someone views the file, he can click on the bar, to get a relevant document on his web-browser/similar(pdf, whatever). Any suggestions? You can write event procedures that determine which point was clicked. Within the procedure you need some kind of lookup to see what document should be launched according to the point which was clicked. Here's an article on chart events: http://www.computorcompanion.com/LPMArticle.asp?ID=221 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical...

offline files
My laptop is set up to connect with the company lan at work. When I take the laptop home, some of my email files are available and some are not. This is aggravating when I'm at a meeting and expect to find the email with the previous minutes, but can't. What can I do? Check your sync settings while online, and make sure it's set to sync *all* the folders you want - and to do so automatically while online every X minutes, and automatically when you close Outlook. Note - non-default folders will need to be setup for offline use manually, and new subfolders will not inherit thos...

Printing Checks
I'm having a problem printing checks from Money 2003. I have three accounts that come up in one .mny file. Two of the accounts use 3-on-a-page checks and I want the name and address printed on them. One account uses voucher checks and needs to be nudged a little south. When I go to print checks I have to be aware of what account I'm in and change the settings by hand to make sure the checks print correctly. How can I make the check-print settings stick with the account I'm using. It's a pain to have to remember every time. I'm frequently forgetting and screwing up a pag...

Can I create custom forms to add data to a Excel Spreadsheet? How.
see this site http://www.contextures.com/excelfiles.html#UserForm it has a sample WB that you download. good online instructions. >-----Original Message----- > >. > ...

How do I turn off the text to speech facility in Exccel?
I am trying to turn off the text to speech facility in Excel. Please help. On Sat, 4 Jun 2005 01:29:26 -0700, "David Boas" <David Boas@discussions.microsoft.com> wrote in microsoft.public.excel.misc: >I am trying to turn off the text to speech facility in Excel. Please help. On the Speech toolbar, click Speak On Enter, the rightmost button. See also Help: "Turn off speech playback" where it says: Turn off speech playback Do one of the following: To turn off all speech playback, click Stop Speaking on the Text To Speech toolbar. To turn on or...

excel 97 problems
Each time i try to save my changes i get a message stating there has encountered a problem and needs to shut down. My pc is on a local network, where I am primary. The other machine can save without a problem and those changes do appear on my machine but I am unable to save Clean out your temp files, clean out your Temp Internet Files (include offline content) "Ken" <cls@peoplepc.com> wrote in message news:001801c381e4$d0ed8720$a301280a@phx.gbl... : Each time i try to save my changes i get a message stating : there has encountered a problem and needs to shut down. My : p...

Text problems
Using a newsletter created by Publisher 2000, opening the newsletter in Publisher 2002 and saving as a web page some of the text lines are over writen by the next line and the text that fills a column in the newsletter leaves a lot of white space at the bottom of the column. Is there any way to fix this problem or do I continue to use Publisher 2000 and give up on Publisher 2002? Thanks for any help any one can give. Bill ...

Linking workbooks in Excel
I'm trying to link two workbooks. The information I enter in the first workbook is the master copy and I would like this same information to be updated in the second workbook. I use the second workbook as the updated status which I send to clients. I usually create two workbooks and then enter the information twice, once in the master workbook and again in the client's copy - tedious and incorrect, I'm sure. Thank you for your help. Just some thoughts .. if the 2 books were identical (probably not?), just send over a copy* of your master would satisfy ? *say, a frozen copy ...

Opening Excel Problem
Reply to windsurf222@hotmail.com Hi I was trying to open excel on my Pentium 2, windows 98. I have office 2000 premium, the excel version. As soon as I click on the icon, and the the opening page appears, all of a sudden the screen changes rapidfire speed and it starts going thru the sheets up to about 500 or however much it goes up to. I can stop it by cntrl, alt, dlt and them hitting end task. What is going on ? I tried uninstalling and reinstalling same thing. If I look at excel on the cd, it's fine. I turned off norton anti virus. George Try opening Excel in safe mode, then go to t...