Structure of parsing 3k html files

[mos]

Good morning,

I have about 3-4k html files, each looking like this

Code (could not use Code tag, got f ed up by the html code itself)

I look for something like this:

NAO, my question:

What methods do you suggest?

I had in mind

• using import function and the re-order the tables (disadv: gives 1sheet per html file)
• parse the html files as text, look for String "Company Name" and get following String bracket content somehoe

what else would be good?

will be a bigger task, at least for me, so I wanna check whats best.

thanks, cheers

mos

 

[Sektor]

Use regular expressions. For instance, get Company Name:

Dim re As New RegExp
Dim mc As MatchCollection
re.Pattern = [HTML]"<tr><th>Company Name</th><td colspan=""2"">(\w*)<br />"[/HTML]
Set mc = re.Execute("[I]YOUR_HTML[/I]")
For Each m in mc
    MsgBox m.Value
Next

 

[mos]

awesome idea, thanks Sektor!
I think I got some problems with the "YOUR HTML" part.
Not sure if I get the html txt in the string here:

I also get a undefined type error in the "Dim RE as RegExp" line.
As well es in the version of Sektor as well in this pre-written version from
http://www.tmehta.com/regexp/add_code.htm

How do I declare and initialize regexp object correctly?

I find only these formulations use, but they dont seem to be recognized.

Sub fileloop()
    Dim MyDir As String
    Dim strPath As String
    Dim vaFileName As Variant
    Dim i As Integer
    Dim searchstring As String
        
    MyDir = ActiveWorkbook.Path ' current path
    strPath = MyDir & "\files" ' files subdir

    With Application.FileSearch
        .NewSearch
        .LookIn = strPath
        .SearchSubFolders = False
        .Filename = ".htm"

        If .Execute > 0 Then
     
               For i = 1 To .FoundFiles.Count
               searchstring = OpenTextFileToString2(.FoundFiles(i))
                                            
              'Company Name
              Cells(i + 1, 1) = RegExpFind(searchstring, [HTML]"<tr><th>Company Name</th><td colspan=""2"">(\w*)"[/HTML], True)
              
               Next i
 
            End If
    End With
End Sub

Function OpenTextFileToString2(ByVal strFile As String) As String
Dim hFile As Long
hFile = FreeFile
Open strFile For Input As #hFile
OpenTextFileToString2 = Input$(LOF(hFile), hFile)
Close #hFile
End Function

Function RegExpFind(FindIn, FindWhat As String, _
        Optional IgnoreCase As Boolean = False)
    Dim i As Long
    #If Not LateBind Then
    Dim RE As RegExp, allMatches As MatchCollection, aMatch As Match
    Set RE = New RegExp
    #Else
    Dim RE As Object, allMatches As Object, aMatch As Object
    Set RE = CreateObject("vbscript.regexp")
        #End If
    RE.Pattern = FindWhat
    RE.IgnoreCase = IgnoreCase
    RE.Global = True
    Set allMatches = RE.Execute(FindIn)
    ReDim rslt(0 To allMatches.Count - 1)
    For i = 0 To allMatches.Count - 1
        rslt(i) = allMatches(i).Value
        Next i
    RegExpFind = rslt
    End Function

Thanks for any advice!

cheers, mos

 

[mos]

crap. I forgot the Tools - Reference to RegExp.

Sorry.

Works now.


But result is:

<tr><th>Company Name</th><td colspan="2">ABB

why is it giving back the html code, not only the \w* part?


Basically in the html code it is:

<tr><th>Company Name</th><td colspan="2">ABB Bla2 Ltd. LLC-XYZ </tr> <whatever html code..>

I need ABB Bla2 Ltd. LLC-XYZ

So

<tr><th>Company Name</th><td colspan="2">(\w|\s|\d)*</tr> <whatever html code..>

?

thanks a lot

 

[Norie]

What happens if you open these files in Excel?

 

[Sektor]

Please, write HTML code of your company. From screen shot it can't be recognized.

 

[mos]

What happens if you open these files in Excel?

which is actually quite good yes. As I wrote in the first post I was considering importing them. Each file as one sheet and then reorganize to one sheet.
But this with 3-4k files, dont know...

As I look back now, maybe would have been easier.


Sektor: thanks for you pm, works almost perfect now!

html line is:

<tr><th>Company Name</th><td colspan="2">ABB 123 Ltd. XYZ"<br />"

RegExpFind(searchstring, [HTML]"<tr><th>Company Name</th><td colspan=""2"">(.*)<br />"[/HTML], True)

gives me

<tr><th>Company Name</th><td colspan="2">ABB 123 Ltd. XYZ<br />

How can I strip context away and just get
ABB 123 Ltd. XYZ

Function RegExpFind(FindIn, FindWhat As String, _
        Optional IgnoreCase As Boolean = False)
    Dim i As Long
    #If Not LateBind Then
    Dim RE As RegExp, allMatches As MatchCollection, aMatch As Match
    Set RE = New RegExp
    #Else
    Dim RE As Object, allMatches As Object, aMatch As Object
    Set RE = CreateObject("vbscript.regexp")
        #End If
    RE.Pattern = FindWhat
    RE.IgnoreCase = IgnoreCase
    RE.Global = True
    Set allMatches = RE.Execute(FindIn)
    ReDim rslt(0 To allMatches.Count - 1)
    For i = 0 To allMatches.Count - 1
        rslt(i) = allMatches(i).Value
        Next i
    RegExpFind = rslt
    End Function

thanks

 

[Sektor]

In

<tr><th>Company Name</th><td colspan="2">ABB 123 Ltd. XYZ"

what is going after "XYZ"?

 

[Sektor]

Sub FindCompanies()

    Dim re As New RegExp, mc As MatchCollection, m As Match
    Dim str As String
    
    str = "<tr><th>Company Name</th><td colspan=""2"">ABB 123 Ltd. XYZ</td>" & vbNewLine & _
          "<tr><th>Company Name</th><td colspan=""2"">CCC 456 Ltd. FFF</td>"
    
    With re
        .Global = True
        .Pattern = "<tr><th>Company Name</th><td colspan=""2"">(.*)<.*"
    End With
    
    For Each m In re.Execute(str)
        MsgBox m.SubMatches(0)
    Next
    
End Sub

BTW, I don't see rslt variable declaration.

 

[brettdj]

Use Replace rather than Execute and then just return the first submatch, ie (*.?) - which I made non greedy

Cheers

Dave

Function RegExpFind(FindIn, FindWhat As String)
    Dim RegExp
    Set RegExp = CreateObject("vbscript.regexp")
    With RegExp
        .Pattern = FindWhat
        .Global = True
    End With
    RegExpFind = RegExp.Replace(FindIn, "$1")
End Function