Link to home
Start Free TrialLog in
Avatar of Skylar
Skylar

asked on

VBA to search htm file and return string found in excel

I have a folder which has multiple other sub folders inside it and in each of those sub folders there are different files and only one htm file.

the htm file contains table and text.

Currently, i am manually opening these hundreds of htm files and searching the words and then i put them manually in an excel file

Please see attached example file of Excel .



Product231
Product575
Product7867
Plastic
Rubber
Iron
PVC
Pipe
Liquid
Shell
Stone
Brick
Book2.xlsm
Avatar of Sam Jacobs
Sam Jacobs
Flag of United States of America image

Can you post a sample .htm file?
Avatar of Skylar
Skylar

ASKER

Thanks very much Sam.  I am home now,  i will upload the sample htm once i reach office.

Thanks again. much appreciated.
SOLUTION
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Skylar

ASKER

Sam,  I have uploaded here the htm example.
Department_Lowenssa.htm
Avatar of Skylar

ASKER

Fabrice, Thanks so much.  I tried your code but then ran into the error.  I have attached the error.  also i have attached my htm file

User generated imageDepartment_Lowenssa.htm
rng.Value = item(0)

Open in new window

Avatar of Skylar

ASKER

That error got fixed, now another error.  

User generated image
Look at the function prototype:
SearchHTMLFiles(ByVal path As String, ByRef results As Collection)

Open in new window

It expect a collection as 2nd argument, and since it is a recursive function, simply add the results collection as 2nd parameter.

Do you have any knowledge with VBA ?
Avatar of Skylar

ASKER

Hi Febrice,

Thanks a million for your help. it means the world to me. I really appreciate it.  

I know a little bit of VBA, and I am enthusiastic VBA learner.

So this function
SearchHTMLFiles(ByVal path As String, ByRef results As Collection)

Open in new window


requires two inputs path and it looks to me that SearchHTMLFiles subFld.path
 has only one argument input given but not the second.   am i right?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Skylar

ASKER

Fabrice,

Merci beaucoup!

This is truly a masterpiece.   it will really save me a lot of time.  

It worked perfectly.  the only thing, it did not return the values that found into the second column.

this part of the code
   '// 1st column is file path, 2nd column is the list of words found
            results.Add Array(fl.path, Concatenate(searchWords, ";"))

Open in new window


should take care of it, but it does not work.  the second column returns blank.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Skylar

ASKER

Fabrice,

You are genius!   Thanks very much.  It is perfect.