VBA - Click Javascript-based Anchor?

[t3chie]

Windows OS - Win7
Excel Ver. - 2007
IE Ver. - 8

I'm attempting to setup an excel based automation of pulling a report, however to access the report. I need to navigate a javascript based link, it has no ID or name so my usual code doesn't work.

<'a href='javascript:parent.AdminJS.resetDoCmd();parent.AdminJS.setContentsUrl("javascript%3Aparent.AdminJS.setContentsUrl%28%27/webview/FavoritesFrame.jsp%27%29%3B");'><'script>document.write(properUnescape("Favorites"));<'/script>Favorites<'/span><'/a>

I can't figure out a way to execute this script or click the link itself, it has to be a click or javascript execution because it manipulates the HTML on the page and cannot be navigated to directly.

 

[John_w]

Loop through HTMLdocument.links looking for a link with innerText = "Favorites". If found call the Click method. You might need to call the Focus method before that.

 

[t3chie]

Loop through HTMLdocument.links looking for a link with innerText = "Favorites". If found call the Click method. You might need to call the Focus method before that.

Would you mind providing me an example of this loop?

 

[John_w]

Something like this. The code assumes that the IE variable is Set to an InternetExplorer object. The code needs a reference to MS HTML Library for it to work.

    Dim IE As Object
    Dim i As Integer
    Dim HTMLdoc As HTMLDocument
    Dim link As HTMLLinkElement
    Set HTMLdoc = IE.document
    Set link = Nothing
    i = 0
    While i < HTMLdoc.Links.Length And link Is Nothing
        If HTMLdoc.Links(i).innerText = "Favorites" Then Set link = HTMLdoc.Links(i)
        i = i + 1
    Wend
    If Not link Is Nothing Then
        link.Focus
        link.Click
    End If

 

[t3chie]

Something like this. The code assumes that the IE variable is Set to an InternetExplorer object. The code needs a reference to MS HTML Library for it to work.

    Dim IE As Object
    Dim i As Integer
    Dim HTMLdoc As HTMLDocument
    Dim link As HTMLLinkElement
    Set HTMLdoc = IE.document
    Set link = Nothing
    i = 0
    While i < HTMLdoc.Links.Length And link Is Nothing
        If HTMLdoc.Links(i).innerText = "Favorites" Then Set link = HTMLdoc.Links(i)
        i = i + 1
    Wend
    If Not link Is Nothing Then
        link.Focus
        link.Click
    End If

I'm receiving an interface error when attempting to utilize this code, am I implementing this incorrectly?

Error:

Global appIE As Object ' InternetExplorer.Application
Global sURL As String ' URL to navigate to.
Global i As Integer ' Integer used for loop
Global HTMLdoc As HTMLDocument ' HTML element
Global link As HTMLLinkElement ' HTML link

Sub pullTest()
 
Application.ScreenUpdating = True
Set appIE = CreateObject("InternetExplorer.Application")


' URL of the event log entry
sURL = "https://testurl.com/index.html"

' Instructes the macro to open IE and navigate to sURL.
With appIE
    .Navigate sURL
    .Visible = True
End With


' Delays the code execution to allow IE to catch up.
Application.Wait Now + TimeValue("00:00:03")

    Set HTMLdoc = appIE.HTMLDocument
    Set link = Nothing
    i = 0
    While i < HTMLdoc.Links.Length And link Is Nothing
        If HTMLdoc.Links(i).innerText = "Favorites" Then Set link = HTMLdoc.Links(i)
        i = i + 1
    Wend
    If Not link Is Nothing Then
        link.Focus
        link.Click
    End If


End Sub

 

[John_w]

Have you set the reference I mentioned? Does the code compile? Which line causes the error?

I'm just guessing without seeing the real URL, but maybe add the following line immediately after the .Visible line:

While .busy Or .readyState <> 4: DoEvents: Wend

and then you shouldn't need the Application.Wait. (The 3 second wait might not be long enough). The above change might be insufficient if the element you're trying to access is not ready or doesn't yet exist.

Apart from that the code looks OK.

If still no joy post the real URL.

 

[t3chie]

Have you set the reference I mentioned? Does the code compile? Which line causes the error?

I'm just guessing without seeing the real URL, but maybe add the following line immediately after the .Visible line:

While .busy Or .readyState <> 4: DoEvents: Wend

and then you shouldn't need the Application.Wait. (The 3 second wait might not be long enough). The above change might be insufficient if the element you're trying to access is not ready or doesn't yet exist.

Apart from that the code looks OK.

If still no joy post the real URL.

Thank you for your patience! Appending the given ".busy" code provided gives me the follow error:

References:

I receive the interface error from my previous post at "Set HTMLdoc = appIE.document"

Global appIE As Object ' InternetExplorer.Application
Global sURL As String ' URL for IE to browse to


Sub Test()
    Dim i As Integer
    Dim HTMLdoc As HTMLDocument
    Dim link As HTMLLinkElement
 
Application.ScreenUpdating = True
Set appIE = CreateObject("InternetExplorer.Application")


' URL of the event log entry
sURL = "https://test.com/index2.html"


' Instructes the macro to open IE and navigate to sURL.
With appIE
    .Navigate sURL
    .Visible = True
End With


' Delays the code execution to allow IE to catch up.
Application.Wait Now + TimeValue("00:00:05")


    HTMLdoc = appIE.document
    Set link = Nothing
    i = 0
    While i < HTMLdoc.Links.Length And link Is Nothing
        If HTMLdoc.Links(i).innerText = "Favorites" Then Set link = HTMLdoc.Links(i)
        i = i + 1
    Wend
    If Not link Is Nothing Then
        link.Focus
        link.Click
    End If


End Sub

Unfortunately, I cannot provide the exact URL, but I have access to anything you'd like me to pull from the source. The actual URL is an internal HTML file.

 

[t3chie]

Well, it appears it had an issue to do with my company laptop having UAC, I relocated the excel sheet to a Windows 2003 server where I have admin and ran the below code:

Global appIE As Object ' InternetExplorer.ApplicationGlobal sURL As String


Sub EventLogTest()
    Dim i As Integer
    Dim HTMLdoc As HTMLDocument
    Dim link As HTMLLinkElement
 
Application.ScreenUpdating = True
Set appIE = CreateObject("InternetExplorer.Application")


' URL of the event log entry
sURL = "https://test/index.html"


' Instructes the macro to open IE and navigate to sURL.
With appIE
    .Navigate sURL
    .Visible = True
End With


' Delays the code execution to allow IE to catch up.
Application.Wait Now + TimeValue("00:00:05")


    Set HTMLdoc = appIE.Document
    Set link = Nothing
    i = 0
    While i < HTMLdoc.Links.Length And link Is Nothing
        If HTMLdoc.Links(i).innerText = "Favorites" Then Set link = HTMLdoc.Links(i)
        i = i + 1
    Wend
    If Not link Is Nothing Then
    msg = MsgBox("FOUND", vbOKOnly, "YAY!")
        link.Focus
        link.Click
    ElseIf link Is Nothing Then
    msg = MsgBox(":(", vbOKOnly, ":(")
    End If


End Sub

Needless to say, I'm getting the frowning face. It doesn't appear to be picking up the anchor.

 

[John_w]

I see you've corrected this now, but the cause of the interface error was:

Set HTMLdoc = AppIE.HTMLDocument

and it should be:

Set HTMLdoc = AppIE.Document

Try this code which displays all the links in the VB Immediate window. It needs MS Internet Controls and HTML Object Library references.

Sub EventLogTest()

    Dim appIE As InternetExplorer
    Dim sURL As String
    Dim i As Integer
    Dim HTMLdoc As HTMLDocument
    Dim link As HTMLLinkElement
 
    sURL = "https://test/index.html"
    'sURL = "http://www.mrexcel.com"

    Set appIE = New InternetExplorer

    With appIE
        .Navigate sURL
        .Visible = True
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        Set HTMLdoc = .Document
    End With

    Set link = Nothing
    i = 0
    While i < HTMLdoc.Links.Length And link Is Nothing
        Debug.Print HTMLdoc.Links(i).innerText, HTMLdoc.Links(i).href
        If HTMLdoc.Links(i).innerText = "Favorites" Then Set link = HTMLdoc.Links(i)
        i = i + 1
    Wend
    If Not link Is Nothing Then
        link.Focus
        link.Click
    Else
        Debug.Print "Not found"
    End If

End Sub

Is the link in a frame or iframe? If so that might explain which it isn't being found. In which case you need to access the HTMLdocument inside the iframe, something like this:

Sub EventLogTest2()

    Dim appIE As InternetExplorer
    Dim sURL As String
    Dim i As Integer
    Dim HTMLdoc As HTMLDocument
    Dim link As HTMLLinkElement
    Dim theFrame As HTMLIFrame
 
    sURL = "https://test/index.html"
    'sURL = "http://www.mrexcel.com"

    Set appIE = New InternetExplorer

    With appIE
        .Navigate sURL
        .Visible = True
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        Set HTMLdoc = .Document
    End With

    'Get HTMLDocument inside the iframe whose id is "myIFrame"

    Set theFrame = HTMLdoc.getElementById("myIFrame")
    Set HTMLdoc = theFrame.contentWindow.Document

    Set link = Nothing
    i = 0
    While i < HTMLdoc.Links.Length And link Is Nothing
        Debug.Print HTMLdoc.Links(i).innerText, HTMLdoc.Links(i).href
        If HTMLdoc.Links(i).innerText = "Favorites" Then Set link = HTMLdoc.Links(i)
        i = i + 1
    Wend
    If Not link Is Nothing Then
        link.Focus
        link.Click
    Else
        Debug.Print "Not found"
    End If

End Sub

In the above code you'll need to change "myIFrame" to the id of the iframe. That's assuming it has an id. If not you'll need another way, e.g. looping through tags or elements similar to the method already shown, or getElementsByName("iframeName")(0), etc.

 

[t3chie]

John, I just wanted to thank you for your assistance and patience with this, it has been very educational! More over, you hit the nail on the head. The website I'm using is an ugly compilation of nested frames that are arranged via JavaScript, which is the reason why it wasn't finding the links.

But you gave me an idea. Going through the source code, I was able to figure out that frames are included from separate jhtml documents and was able to find the file which housed the specific frame I was looking for and now the code is working flawlessly!

Thank you!