Select Radio Button on webpage error message using Excel VBA

[Moeey]

Hello,
I am getting the following error message
Run-time error '91': Object variable or With block variable not set.

Here's my code, and the line highlighted in red is where the error message occurs

Public Sub Login()
    
    Dim IE As InternetExplorer
    Dim HTMLdoc As HTMLDocument
    Dim button As HTMLInputButtonElement
    Dim i As Integer
    
    Set IE = New InternetExplorer
    With IE
        .Visible = True
        .navigate "URL"
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        Set HTMLdoc = .document
    End With
    
    Set UserName = HTMLdoc.getElementById("ctl00$ContentBody$LoginControl1$txtUserName")
    Set Password = HTMLdoc.getElementById("ctl00_ContentBody_LoginControl1_txtPassword")
    UserName.Value = "Username"
    Password.Value = "Password"
    Set SignIn = HTMLdoc.getElementById("ctl00_ContentBody_LoginControl1_butLogon")
    SignIn.Click
    
'Choose Site
    While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        
    Set button = Nothing
    i = 0
    While i < HTMLdoc.Links.Length And button Is Nothing
        If InStr(HTMLdoc.Links(i).href, "ctl00$ContentBody$rptSites$ctl00$ctl00") > 0 Then Set button = HTMLdoc.Links(i)
        i = i + 1
    Wend
    
    If Not button Is Nothing Then
       button.Click
    Else
        MsgBox "Button not found"
    End If
    
'Choose what you want to do in SoftSmart
    While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE: DoEvents: Wend
    
    Set button = HTMLdoc.getElementById("linkMailMerge")
    button.Click
    
'Choose detailed data extract
    While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        
[COLOR=#ff0000][B]  HTMLdoc.getElementById("rdoDetailedExtract").Checked = True
[/B][/COLOR]    
End Sub

Here's the html behind the radio button which I am trying to select.

any ideas where I'm going wrong?

Thanks

 

[Moeey]

Yes, you're right. The radio button is within a frame,

iframe name="pnlMain" id="pnlMain" 
src="BodyPanelPages/NewAttendance/Review01.aspx" 
frameBorder="0" marginWidth="0" marginHeight="0" 
scrolling="no" style="left: 0px; top: 0px; 
height: 407px; padding-top: 0px; padding-right: 0px;
padding-bottom: 0px; padding-left: 0px; 
margin-top: 0px; margin-right: 0px; 
margin-bottom: 0px; margin-left: 0px; 
position: absolute;"

So I changed the code to include a reference to the frame which contains the radio button, and it works a treat.

While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE: DoEvents: Wend

Set HTMLdoc = IE.document.frames("pnlMain").document

HTMLdoc.getElementById("rdoDetailedExtract").Checked = True

Thanks again John.
Now for the next button to press...

 

[Moeey]

Hello,I'm having problems clicking another button on this page. The button

input name="btnSureStartAreas" title="Select those areas assigned to the children's centre only." class="ccm-btn-d" id="btnSureStartAreas" type="submit" value="My Area"/

is inside a frame

iframe id="ifrSelectArea" src="../UserControls/ucSelectArea.aspx" frameBorder="0" scrolling="no" style="width: 100%; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px;"

which is inside another frame

iframe name="pnlMain" id="pnlMain" src="BodyPanelPages/MailMergeAndDataExtract.aspx" frameBorder="0" marginWidth="0" marginHeight="0" scrolling="no" style="left: 0px; top: 0px; height: 424px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; position: absolute;"　

I have tried the following code, which gives me the error message "Run-time error '91': Object variable or With Block variable not set"on the line highlighted in red, which I think, tells me that the button cannot be found.Is there a way to check to see that the frame with Id = "ifrSelectArea" is actually selectedwhen the code is run?

Set HTMLdoc = IE.document.frames("pnlMain").documentSet theFrame = HTMLdoc.getElementById("ifrSelectArea")[SIZE=1][COLOR=#ff0000][COLOR=#ff0000][SIZE=2]Set button = HTMLdoc.getElementById("btnSureStartAreas")   button.Click[/SIZE][/COLOR][/COLOR][/SIZE]

Any ideas?

 

[Moeey]

Sorry the code is messy to read.
Here it is again
The error message occurs on the highlighted line

Set HTMLdoc = IE.document.frames("pnlMain").document
Set theFrame = HTMLdoc.getElementById("ifrSelectArea")
[SIZE=2][COLOR=#ff0000]Set button = HTMLdoc.getElementById("btnSureStartAreas") 
button.Click
[/COLOR][/SIZE]

 

[John_w]

Try:

Set HTMLdoc = IE.document.frames("pnlMain").document

'Either
Set HTMLdoc = HTMLdoc.frames("ifrSelectArea").document

'Or
'Set HTMLdoc = HTMLdoc.frames("ifrSelectArea").contentDocument

Set button = HTMLdoc.getElementById("btnSureStartAreas") 
button.Click

Is there a way to check to see that the frame with Id = "ifrSelectArea" is actually selected when the code is run?

You could display outerHTML or innerText or search outerHTML for the Id string to see if the HTML contains what you expect:

Debug.Print HTMLdoc.body.outerHTML
Debug.Print HTMLdoc.body.innerText
Debug.Print InStr(1, HTMLdoc.body.outerHTML, "ifrSelectArea", vbTextCompare)  'displays 0 if Id string not found
Debug.Print Mid(HTMLdoc.body.outerHTML, InStr(1, HTMLdoc.body.outerHTML, "ifrSelectArea", vbTextCompare) - 20, 40)  'if found, displays 40 characters around Id string

 

[Moeey]

Hello John,
Once again you have solved my problem, many thanks.
The correct syntax is so important with this.
I had tried lots of different variations but none worked until I tried your suggestions.

So just to confirm that this is the final code I'm using.

While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE: DoEvents: Wend
    Set HTMLdoc = IE.document.frames("pnlMain").document
    Set HTMLdoc = HTMLdoc.frames("ifrSelectArea").document
    Set button = HTMLdoc.getElementById("btnSureStartAreas")
    button.Click

Also thanks for the code to display outerHTML or innerText information which will be useful for debugging stuff.
I have noticed that information within this area of VBA Excel programming is hard to find. Other than the forums and the odd webpage here and there, are there any resources such as books/websites which provide more indepth information and a good grounding in this area?

 

[John_w]

I use the following function to log HTML:

Public Sub Log_HTML(HTML As String)
    Static num As Integer
    num = num + 1
    Open "C:\Temp\HTML" & num & Format(Now, " hh mm ss") & ".txt" For Output As #1
    Print #1, HTML
    Close #1
End Sub

It creates a new text file every time it's called. The HTML string argument can be any part of the web page document, whichever element your VBA code is referencing and which you're interested in, e.g:

Log_HTML HTMLdoc.body.outerHTML

When developing IE automation code for difficult web pages, I call this function before and after a significant line of code, or in a loop with a timeout, sometimes running the code to a certain point, then continuing with manual interaction with the IE browser. This enables me to log the effect of that line of code, or manual interaction, on the HTML source and then I use WinMerge to look for differences.

For IE automation and general HTML parsing, there isn't really a single web site which pulls everything together. I don't know of any books on the subject. I have found the following web pages and references useful:

VBA web services
Collection of VBA Code Snippets and Useful Excel Knowledge - Working with Internet Explorer Using VBA
Using the XML HTTP Request object

IE Automation and HTML references

Internet Explorer Development - Hosting and Reuse

WebBrowser Control

InternetExplorer object (Windows)

HTML/XHTML Reference (Internet Explorer)

About MSHTML

Windows Internet Explorer API reference (Internet Explorer)

Document Object Model (DOM) (Internet Explorer)

 

[Moeey]

John, many thanks for the advice and input.
In particular your code to investigate HTML. I'll make sure to use it to see how it works.
Also thanks for the ton of websites. Some bedtime reading
I have encountered some other problems with the current webpage which I am working on.
I need to look at it further, maybe use the function to log HTML which you have provided, and if all that fails then I may have to ask for your advice again.