Using VBA to click on a link within an iframe/automation error.

[LCarney]

Ok, so I'm working on a macro to automate data entry into a website. I've gotten stuck at the point of trying to click on a new link within and iframe. After a lot of googling I found that there is a handling issue with IE and tried to employee a work around someone suggested. The problem is I get stuck in a loop with this workaround, when I "fix" (by replaceing = with <>) the work around so it's not stuck I get the automation error I got before.

Below is the code that I'm using and I've attached the HTML;
Sub AUTOFILL()
Dim IE As Object
Dim doc As HTMLDocument
Dim iframe As HTMLIFrame

Set IE = CreateObject("InternetExplorer.Application")

IE.Visible = True
IE.Navigate "Ally Auto Dealer Services | Financing, Training, Rewards & More | Ally Auto"

Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

Set doc = IE.document

IE.document.getElementById("user").Value = "USERNAME"
IE.document.getElementById("password").Value = "PASSWORD"
IE.document.getElementById("processLogin").Click

Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

IE.document.getElementsByTagName ("a")
Dim VPClink As HTMLAnchorElement
Set VPClink = Nothing
Do
Set VPClink = doc.querySelector("[data-track-name='Vehicle Protection Center']")
DoEvents
Loop While VPClink Is Nothing
VPClink.Click
IE.document.querySelector("[data-track-name='Vehicle Protection Center']").Click

Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

Dim baseURL As String
Dim HTMLdoc As HTMLDocument
Dim workFrame As HTMLIFrame
Dim acctInput As HTMLInputElement

Set IE = New InternetExplorer


With IE
.Visible = True
IE.Navigate "Ally Financial (API securedealerally.) - Sign In"

Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

Set doc = IE.document

'Craziest workaround ever due to bugged IE Eventhandling, like this is gets stuck in the loop
Do While IE.readyState = 4: DoEvents: Loop
Do Until IE.readyState = 4: DoEvents: Loop

'This is the line that gives me an automation error
IE.document.getElementsByTagName("iframe")(0).contentDocument.getElementById("startNewQuote").Item(0).innerHTML.Click
'<a title="Start a New Quote" class="primary-btn floatright nav-link" id="startNewQuote" href="/qps/getCreateQuote"><span class="marginTop10 marginLeft10px">Start a New Quote </span></a>




End With






End Sub

 

[Worf]

Can I create an account at that site for testing purposes?

Would you consider using Chrome instead? See thread below for directions and also a frame example.

How to extract a single span value from a URL behind an iframe Element?

I am trying to pull a single value from a work website that refreshes every 5 minutes into an Excel cell. This is the final step in automating an entire workbook filled with a bunch of formulas I created. I am using HTTP Get Request to do this. However, I am having problems pulling the actual...

www.mrexcel.com

 

[LCarney]

I don't know, it's for dealerships. I don't know if you can't create an account without a dealership as an umbrella. I would be willing to use Chrome, I've just never used VBA to interact with Chrome before, I'm pretty new to VBA.

 

[Worf]

This example worked for me:

Sub extractTablesData()
Dim IE As Object, Data As Object, mf, i%
Set IE = CreateObject("InternetExplorer.Application")
With IE
    .Visible = True
    .navigate ("http://intrinsicedge.blogspot.com/")
    While IE.readyState <> 4
        DoEvents
    Wend
    Set Data = .Document.getElementsByTagName("iframe")
    On Error Resume Next
    For i = 0 To Data.Length - 1
        MsgBox Data.item(i).getAttribute("width") & _
        vbLf & Data.item(i).getAttribute("src"), , i
    Next
    On Error GoTo 0
    Set mf = .Document.getElementsByTagName("iframe") _
    (3).Document.getElementsByTagName("table")
    MsgBox mf.Length
End With
End Sub

 

[LCarney]

Well that seemed to do something, but when it gets to
"
Set mf = .document.getElementsByTagName("iframe") _
(3).document.getElementsByTagName("table")" I get this error.
"Object variable or With block variable not set" When I run the code a second IE window does pop up, but it's the same page as what was already open and I get an error message. I've attached a screen shot of that.

I am still very much at a beginner level with VBA, I inserted your example code as follows, what I am I missing? Also if you don't mind me asking, since I'm so new at VBA on the line "Dim Data As Object, mf, i%".....what does the "mf, i%" do here? Appreciate your help.


Sub AUTOFILL()
Dim IE As Object
Dim Doc As HTMLDocument
Dim iframe As HTMLIFrame

Set IE = CreateObject("InternetExplorer.Application")

IE.Visible = True
IE.Navigate "Ally Auto Dealer Services | Financing, Training, Rewards & More | Ally Auto"

Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

Set Doc = IE.document

IE.document.getElementById("user").Value = "USERNAME"
IE.document.getElementById("password").Value = "PASSWORD"
IE.document.getElementById("processLogin").Click

Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

IE.document.getElementsByTagName ("a")
Dim VPClink As HTMLAnchorElement
Set VPClink = Nothing
Do
Set VPClink = Doc.querySelector("[data-track-name='Vehicle Protection Center']")
DoEvents
Loop While VPClink Is Nothing
VPClink.Click
IE.document.querySelector("[data-track-name='Vehicle Protection Center']").Click

Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop



'This was from stack overflow

Dim baseURL As String
Dim HTMLdoc As HTMLDocument
Dim workFrame As HTMLIFrame
Dim acctInput As HTMLInputElement

Set IE = New InternetExplorer

Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
Dim Data As Object, mf, i%

IE.Navigate "Ally Financial (API securedealerally.) - Sign In"

While IE.readyState <> 4
DoEvents
Wend
Set Data = .document.getElementsByTagName("iframe")
On Error Resume Next
For i = 0 To Data.Length - 1
MsgBox Data.Item(i).getAttribute("width") & _
vbLf & Data.Item(i).getAttribute("src"), , i
Next
On Error GoTo 0
Set mf = .document.getElementsByTagName("iframe") _
(3).document.getElementsByTagName("table")
MsgBox mf.Length
End With

IE.document.getElementsByTagName("iframe")(0).contentDocument.getElementById("startNewQuote").Item(0).innerHTML.Click
'<a title="Start a New Quote" class="primary-btn floatright nav-link" id="startNewQuote" href="/qps/getCreateQuote"><span class="marginTop10 marginLeft10px">Start a New Quote </span></a>

End Sub

 

[Worf]

• That is not an error, it is a message box. My code loops through the frames on the example page and shows the src attribute for each one. You know the src for your frame from the HTML image you posted before. Use the code to find out the index of your frame.
• My example then locates a table inside the third frame, this will not work for you.
• Note that I am using document and not content document.
• You could run my code untouched to see it working on the example page that has frames.
• When no type is declared, as with mf, the variable will be a variant. The percent sign means an integer variable.

 

[LCarney]

Ok, I'm sure this is going to be a stupid question, but what is the index of my iframe and how will that help me once i find it? and once I find it how do i implement it? (stupid string of three questions)

 

[Worf]

Everybody was a beginner once. At the line of code below, the index is 3. It starts at zero in this case and provides a way to refer to elements in a collection of objects. It is a numerical identification.

Set mf = .Document.getElementsByTagName("iframe") _

(3).Document.getElementsByTagName("table")

At my example code, which does not use your actual page, the index appears at the top left corner of the message box.

After finding it you can write something like

IE.document.getElementsByTagName("iframe")(index).Document.getElementById("startNewQuote").Item(0).innerHTML.Click