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
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
Can you post a sample .htm file?
ASKER
Thanks very much Sam. I am home now, i will upload the sample htm once i reach office.
Thanks again. much appreciated.
Thanks again. much appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sam, I have uploaded here the htm example.
Department_Lowenssa.htm
Department_Lowenssa.htm
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
Department_Lowenssa.htm
Department_Lowenssa.htm
rng.Value = item(0)
Look at the function prototype:
Do you have any knowledge with VBA ?
SearchHTMLFiles(ByVal path As String, ByRef results As Collection)
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 ?
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
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?
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
should take care of it, but it does not work. the second column returns blank.
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, ";"))
should take care of it, but it does not work. the second column returns blank.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fabrice,
You are genius! Thanks very much. It is perfect.
You are genius! Thanks very much. It is perfect.
ASKER
I posted a new follow up question https://www.experts-exchange.com/questions/29125252/Follow-up-question-to-VBA-solution-by-Expert-Fabrice-Lambert.html