Avatar of hal60

asked on 

need help with automating a website from vba

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

Open in new window

' 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

Open in new window

VBAMicrosoft AccessMicrosoft Excel

Avatar of undefined
Last Comment
Avatar of darbid73
Flag of Germany image

It will be a little bit difficult to solve you problem without the website.  Can you provide a link?

If it is an intranet site or not publicly available.

  • Do you get an element object in your Rtn_ElementID function?
  • If yes debug.print its html to make sure you really have the right element AND it has an ID.
  • If 1 and 2 are ok, you can try to set focus on the element first and then click or you can use the fireevent click instead.
  • one last thing, make sure that the webpage is fully loaded, I have noticed sometimes that even though you get an element the click event simply does not work until the webpage is finished and sitting quietly waiting for the next event.
Avatar of hal60


here is the html code where the NEXT is at . javascript
                <TD vAlign=center noWrap><A class=x41 
                  onclick="_navBarSubmit('DefaultFormName', 'goto','N6',1,'26', '25','ActiveBidResultsTable');return false" 
                  href="#">Next 25</A></TD>

Open in new window

Avatar of darbid73
Flag of Germany image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of darbid73
Flag of Germany image

My text in https:#a41874075 is the answer to his problems.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo