Search & return all rows contains that word from all txt files with filename

**suchetherrah** · 09-30-2018, 03:12 PM

Hi,

I am not good in vba. I have about 500 txt files to search for the first word "subject" and return that whole sentence from each txt by filename. In each file can have more than 9 words with subject.

If found the word subject, I need the whole sentence merged in 1 excel file.

I was searching for the vba to meet this criteria but couldn't find in google.

Can anyone please assist? Your assistance is very much appreciated.

**YasserKhalil** · 09-30-2018, 03:24 PM

Try this code

Please Login or Register  to view this content.

**suchetherrah** · 09-30-2018, 10:16 PM

Hi,
Wow it's working. However, if I have 5 Certificates, Output Result shows 7 Certificates where it take the last 2 rows of Cert4 and Cert5 columns extra repeated at Cert6 and Cert7 columns.
If I have 8 Certificates column, output show 3 rows extra by taking Cert6, Cert7 and Cert8 repeated at Cert9, Cert10 and Cert11.

Attached is the test results.

Regards
Chitra

**jindon** · 09-30-2018, 11:40 PM

Different method

Please Login or Register  to view this content.

**suchetherrah** · 10-01-2018, 12:33 AM

Hi Jindon,

Test result is correct now. It's not repeated. Thank you.

However YasserKhalil vba format is correct. Need the format in column format, not in row
I did a attached test result manually just to show the record is repeated.

Regards
Chitra

**jindon** · 10-01-2018, 12:34 AM

Then you will need to upload a workbook with your exact desired result, not the workbook from the other.

PS.
OK, I downloaded your original file.

What do the numbers like 1,2,3... in the first row represent?
Do they correspond to "Certificate" number?

**suchetherrah** · 10-01-2018, 02:34 AM

Hi Jindon
Yes, it correspond to "Certificate number.

**jindon** · 10-01-2018, 02:44 AM

Try this one

Please Login or Register  to view this content.

**suchetherrah** · 10-01-2018, 02:56 AM

Hi Jindon,
I love it. It works perfectly.

If there is a file with no certificate, also need to display the filename with blank cell or state as none.

**jindon** · 10-01-2018, 03:06 AM

Move one line in bold

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**suchetherrah** · 10-01-2018, 03:24 AM

Hi Jindon,

I commented 'n = n + 1: Cells(n, 1).Value = fn but it's not working. Cert number heading and 1st filename is mssing but has data is there and the txt file with no certificate did not show.

Please Login or Register  to view this content.

**jindon** · 10-01-2018, 03:27 AM

1)
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
2)
Not to comment out, but MOVE.

Please Login or Register  to view this content.

**suchetherrah** · 10-01-2018, 04:31 AM

Hi Jindon,

The file without certificate shows data which is repeated from the next file contents.
Attached is the test results and the contents of the data and the txt file looks like

Chitra

**jindon** · 10-01-2018, 04:43 AM

When the txt file doesn't have "Certificate #", it adds a blank row, regardless of the line(s) of "Subject:", because it can not be lined up with the "Certificate #".

How do you want really it?

**suchetherrah** · 10-01-2018, 05:03 AM

Hi Jindon,
Just a filename at column A. Column B onwards is blank row of cells for that filename.
As you can see the Test.xlsm, it output the data for that file.

**jindon** · 10-01-2018, 05:05 AM

By the way, you need to follow the forum rule and edit your post #11 first to continue.
It is a strict rule here.

**suchetherrah** · 10-01-2018, 05:37 AM

Hi Jindon,
Done as requested.

**jindon** · 10-01-2018, 05:41 AM

OK, thanks.

If you just want the lines that begins with "Subject:" then

Please Login or Register  to view this content.

**suchetherrah** · 10-01-2018, 07:55 AM

Hi Jindon,

When run the command getting error message, run time error '62' Input past end of file

**jindon** · 10-01-2018, 08:13 AM

You must have empty file in the folder.

Please Login or Register  to view this content.

**suchetherrah** · 10-01-2018, 08:46 AM

Hi Jindon,

Meaning each time, I have to add the VBA and execute the vba?
I am doing this for another department.

Can I fix an empty file with macro set in it? I will be using the same empty filename to execute. The user will just have to click the macro button and it will overwrite.

Error message run time error '62' Input past end of file hit at
x = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(myDir & fn).ReadAll, vbCrLf)

**jindon** · 10-01-2018, 08:50 AM

Error message run time error '62' Input past end of file hit at
x = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(myDir & fn).ReadAll, vbCrLf)

Did you use the last code I posted?

If so, upload a txt files that you are using.

**suchetherrah** · 10-01-2018, 09:19 AM

Hi Jindon,
The data extraction is working. Just checked. The filesize was 0. That's why it hit the error message. There is 100,000 records to extract.

1) Need to display the filename eventhough filesize is zeror, sir. To tally the number of files
2) Column B is not required. Just letting you know the results of the data.
3) I need to use this file as a template with Macro and Command button. After extracting User will rename to another filename.
4) Start the extraction of records from row 3 onwards. User will hardcode the Title and name the Cert# at Row1 and Row2

Regards
Chitra

**jindon** · 10-01-2018, 09:30 AM

1) It is already there.
2) fixed.
3) Add a Button and assign this macro for yourself
4) the code now will create a new workbook that you can work and save.

Please Login or Register  to view this content.

**suchetherrah** · 10-02-2018, 12:46 AM

Hi,
Thank you for your time and support. It worked like a charm.
Thank you so much Yaseer and Jindon.

Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Re: Search & return all rows contains that word from all txt files with filename

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula Help Pls - Search cell for WORD, return fields from WORD lookup

[SOLVED] need formula to search column for a word and return another word

Bookmarks

Bookmarks

Posting Permissions