troubleshooting Question

need help with automating a website from vba

Avatar of hal60
hal60 asked on
VBAMicrosoft AccessMicrosoft Excel
4 Comments1 Solution120 ViewsLast Modified:
i am scraping a website and all the elements that i want i can get the hyperlinks and do a click on them thru vba exept for one element. i do not know html but have managed to automate 95% of what i need from the site. There is a "Next" link on the page that i can;t get to work, the elementd from it is coming back 0, so i can't cross reference to a link,

here a snippet for testing
Dim IE As InternetExplorer
Set IE = New InternetExplorer

Set elemCollection = IE.Document.getElementsByTagName("Table")

For t = 0 To elemCollection.Length - 1 ' start with table 2 which holds negotions if first table then make t start at 0
 For r = 0 To elemCollection(t).rows.Length - 1
   For c = 0 To elemCollection(t).rows(r).Cells.Length - 1
        If InStr(1, elemCollection(t).rows(r).Cells(c).Innertext, "Next 50") Then
            ElementID2 = Rtn_ElementID(IE, elemCollection(t).rows(r).Cells(c).Innertext)  ; this is where i get 0 back
            GoTo foundnext50
        End If
    Next c
    Next r
Next t

Function Rtn_ElementID(IEIn As InternetExplorer, SearchFor As String, Optional ByVal count As Integer = 1) As Variant
Dim doc, element
    Set doc = IEIn.Document
        Dim i As Integer
        i = 1
    'loop through all elements till we find a match
    For Each element In doc.all
      If Len(element.ID) <> 0 Then
         If element.Innertext = SearchFor Then
         count = count - 1
         If count = 0 Then ' find proper occurance
           Rtn_ElementID = element.ID
           Exit Function
         End If
' here is the html code
</script></td><td valign="middle" nowrap><a onclick="_navBarSubmit('DefaultFormName', 'goto','N4',1,'51', '50','ResultTable');return false" href="#" class="x41">Next 50</a></td><td><script>t(5,1)</script><script> if(DanaFlushWrite) DanaFlushWrite();
</script></td><td valign="middle"><a onclick="_navBarSubmit('DefaultFormName', 'goto','N4',1,'51', '50','ResultTable');return false" href="#"><img

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros