Excel VBA to set fields or radio buttons in Internet Explorer

R

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
Hello,

I'm using some code from here: How to automate web forms from VBA using Internet Explorer - VBA Visual Basic for Applications (Microsoft) FAQ - Tek-Tips

So far, I can get the text fields to populate. I can't seem to get the radio buttons to update. In the "Set Fields" column of my spreadsheet, I have tried the value "N" and TRUE but neither seem to work. (Full code below this sub.)

Rich (BB code): 
Sub SetFields()
On Error Resume Next
Dim objIE As Object
Dim objParent As Object
Dim objInputElement As Object
Dim lngRow As Long

Set objIE = GetIEApp 'Make sure an IE object was hooked

If TypeName(objIE) = "Nothing" Then
    MsgBox "Could not hook Internet Explorer object", vbCritical, "GetFields() Error"
    GoTo Clean_Up
End If

For lngRow = 2 To ActiveSheet.UsedRange.Rows.Count
    If ActiveSheet.Cells(lngRow, cElement_SetValue) <> "" Then
    
        'If we have a parent name/ID drill to that element, otherwise point to whole document
        If ActiveSheet.Cells(lngRow, cForm_name).Text <> "" Then
            Set objParent = objIE.Document.forms(ActiveSheet.Cells(lngRow, cForm_name).Text)
        ElseIf ActiveSheet.Cells(lngRow, cForm_Id).Text <> "" Then
            Set objParent = objIE.Document.forms(ActiveSheet.Cells(lngRow, cForm_Id).Text)
        Else: Set objParent = objIE.Document.all
        End If
        

'*****************
'this is the piece where I can't get the radeio buttons to populate

        With objParent
            If ActiveSheet.Cells(lngRow, cElement_Type) = "radio" Then
                Set objInputElement = objParent.tags("INPUT").Item(ActiveSheet.Cells(lngRow, cElement_Name).Text)
                objInputElement.Item(ActiveSheet.Cells(lngRow, cElement_ID).Text).Checked = True
                Set objInputElement = Nothing         
'*****************   
            ElseIf ActiveSheet.Cells(lngRow, cElement_Type) = "checkbox" Then
                objParent.Item(ActiveSheet.Cells(lngRow, cElement_ID).Text).Checked = True
            Else
                objParent.Item(ActiveSheet.Cells(lngRow, cElement_Name).Text).Value = CStr(ActiveSheet.Cells(lngRow, cElement_SetValue))
            End If
        End With
        
        If Err.Number <> 0 Then
        Debug.Print "Error Writting: Row " & lngRow, ActiveSheet.Cells(lngRow, cElement_Name), ActiveSheet.Cells(lngRow, cElement_SetValue)
        Err.Clear
        End If
    End If
Next lngRow
Clean_Up:
Set objParent = Nothing
Set objIE = Nothing
End Sub

Full code:

Rich (BB code): 
Option Explicit
Option Compare Text
Const cForm_name As Long = 1
Const cForm_Id As Long = 2
Const cElement_Name As Long = 3
Const cElement_ID As Long = 4
Const cElement_nodeName As Long = 5
Const cElement_Type As Long = 6
Const cElement_Value As Long = 7
Const cElement_SetValue As Long = 8
Sub GetFields()
On Error GoTo GetFields_Error
Dim objIE As Object
Dim objForms As Object, objForm As Object
Dim objInputElement As Object
Dim objOption As Object
Dim lngRow As Long
Dim strComment As String

Set objIE = GetIEApp 'Make sure an IE object was hooked
If TypeName(objIE) = "Nothing" Then
    MsgBox "Could not hook Internet Explorer object", vbCritical, "GetFields() Error"
    GoTo Clean_Up
End If
'In case the sheet is being resused, clear it ClearActiveSheet
'Get the forms object
Set objForms = objIE.Document.forms 'Test to see if there are forms before proceding
If objForms.Length <> 0 Then
'Write the header
lngRow = lngRow + 1
With ActiveSheet
    .Cells(lngRow, cForm_name) = "Form_Name"
    .Cells(lngRow, cForm_Id) = "Form_ID"
    .Cells(lngRow, cElement_Name) = "Element_Name"
    .Cells(lngRow, cElement_ID) = "Element_ID"
    .Cells(lngRow, cElement_nodeName) = "Element_nodeName"
    .Cells(lngRow, cElement_Type) = "Element_Type"
    .Cells(lngRow, cElement_Value) = "Element_Value"
    .Cells(lngRow, cElement_SetValue) = "Element_SetValue"
End With   'End Header
'Cycle through all the forms in the document
For Each objForm In objForms
    'Cycle through the input elements in the form
    For Each objInputElement In objForm
        lngRow = lngRow + 1
            With ActiveSheet
                .Cells(lngRow, cForm_name) = objForm.Name
                .Cells(lngRow, cForm_Id) = objForm.ID
                .Cells(lngRow, cElement_Name) = objInputElement.Name
                .Cells(lngRow, cElement_ID) = objInputElement.ID
                .Cells(lngRow, cElement_nodeName) = objInputElement.nodeName
                .Cells(lngRow, cElement_Type) = objInputElement.Type
                    If objInputElement.Type = "submit" Or objInputElement.Type = "button" Then
                        .Cells(lngRow, cElement_SetValue).Interior.Color = vbBlack
                    ElseIf objInputElement.Type = "hidden" Then
                        .Cells(lngRow, cElement_SetValue).Interior.Color = vbYellow
                    End If
                .Cells(lngRow, cElement_Value) = objInputElement.Value
                'build a list of the possible selections for a select elements
                If objInputElement.nodeName = "SELECT" Then
                For Each objOption In objInputElement
                    strComment = strComment & Chr(34) & objOption.Value & Chr(34) & ": " & objOption.Text & vbNewLine
                Next objOption
                'place the list as a comment in the SetValue column
                .Cells(lngRow, cElement_SetValue).AddComment strComment
                strComment = ""
                End If
            End With
    Next objInputElement
Next objForm
End If

Clean_Up: Set objInputElement = Nothing
Set objForm = Nothing
Set objForms = Nothing
Set objIE = Nothing
Exit Sub
GetFields_Error: Debug.Print Err.Number, Err.Description
Resume Next

'MsgBox "The process is complete.", vbOKOnly, "PROCESS COMPLETE"

End Sub
Function GetIEApp() As Object
Dim objShell As Object
Dim objWindows As Object
Dim objWindow As Object
Dim lngSingleWindow As Long
Dim intOption As Integer
Dim strMessage As String, strReturnValue As String

Set objShell = CreateObject("Shell.Application")
Set objWindows = objShell.Windows
lngSingleWindow = -1
For Each objWindow In objWindows
'Build a list of windows, make sure they are Internet Explorer
If Right(objWindow.FullName, 12) = "iexplore.exe" Then
    strMessage = strMessage & intOption & " : " & objWindow.LocationName & vbCrLf
    If lngSingleWindow = -1 Then
        lngSingleWindow = intOption
    Else
        lngSingleWindow = 0
    End If
End If
intOption = intOption + 1
Next
'Check if there are any IE windows
If Len(strMessage) <> 0 Then   'Prompt to pick a window, used an InputBox for portability
    If lngSingleWindow > 0 Then
        Set GetIEApp = objWindows.Item(CLng(lngSingleWindow))
    Else: strReturnValue = InputBox(strMessage, "Please select Browser window")
        'If the user cancels the input box an empty string is returned
        If strReturnValue <> "" Then
        'Make sure the number selected is valid
            If Val(strReturnValue) >= 0 And Val(strReturnValue) <= intOption Then
                Set GetIEApp = objWindows.Item(CLng(strReturnValue))
            End If
        End If
    End If
End If
Set objWindow = Nothing
Set objWindows = Nothing
Set objShell = Nothing

End Function
Public Sub ClearActiveSheet()
ActiveSheet.UsedRange.Clear
ActiveSheet.Cells(2, 1).Activate
End Sub
Sub SetFields()
On Error Resume Next
Dim objIE As Object
Dim objParent As Object
Dim objInputElement As Object
Dim lngRow As Long

Set objIE = GetIEApp 'Make sure an IE object was hooked

If TypeName(objIE) = "Nothing" Then
    MsgBox "Could not hook Internet Explorer object", vbCritical, "GetFields() Error"
    GoTo Clean_Up
End If

For lngRow = 2 To ActiveSheet.UsedRange.Rows.Count
    If ActiveSheet.Cells(lngRow, cElement_SetValue) <> "" Then
    
        'If we have a parent name/ID drill to that element, otherwise point to whole document
        If ActiveSheet.Cells(lngRow, cForm_name).Text <> "" Then
            Set objParent = objIE.Document.forms(ActiveSheet.Cells(lngRow, cForm_name).Text)
        ElseIf ActiveSheet.Cells(lngRow, cForm_Id).Text <> "" Then
            Set objParent = objIE.Document.forms(ActiveSheet.Cells(lngRow, cForm_Id).Text)
        Else: Set objParent = objIE.Document.all
        End If
        
        With objParent
            If ActiveSheet.Cells(lngRow, cElement_Type) = "radio" Then
                Set objInputElement = objParent.tags("INPUT").Item(ActiveSheet.Cells(lngRow, cElement_Name).Text)
                objInputElement.Item(ActiveSheet.Cells(lngRow, cElement_ID).Text).Checked = True
                Set objInputElement = Nothing
            ElseIf ActiveSheet.Cells(lngRow, cElement_Type) = "checkbox" Then
                objParent.Item(ActiveSheet.Cells(lngRow, cElement_ID).Text).Checked = True
            Else
                objParent.Item(ActiveSheet.Cells(lngRow, cElement_Name).Text).Value = CStr(ActiveSheet.Cells(lngRow, cElement_SetValue))
            End If
        End With
        
        If Err.Number <> 0 Then
        Debug.Print "Error Writting: Row " & lngRow, ActiveSheet.Cells(lngRow, cElement_Name), ActiveSheet.Cells(lngRow, cElement_SetValue)
        Err.Clear
        End If
    End If
Next lngRow
Clean_Up:
Set objParent = Nothing
Set objIE = Nothing
End Sub
 
Sort by date Sort by votes
Yep. That's the post I'm trying to work through. Kind of having issues. If the form is on two pages, would I need to click on next, let that operate and then hit the final submit button?
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Yes. Get a reference to the 'next' button then call its Focus method (sometimes not needed, depending on the web site) followed by its Click method, and wait for the new page to load (IE.ReadyState). But I don't know how you'd do that with the code in this thread because it's designed to handle only one page. It's just a general tool for retrieving form elements (or you could look at the HTML source), however the SetFields subroutine is very limited as shown by the issues in this thread and not something I would use to automate IE.
 
Upvote 0
Up until now, this has been working pretty well. There's a general info form and product info form that I populate. The general info form elements never change. The product info form may have different elements, which is why I need something that can retrieve and set element values somewhat dynamically. For this particular set of items, the elements of the product info form aren't populating. Any ideas as to why that would be occuring? The error I'm receiving is 424 object required. I've indicated where in the code the error is occuring using *****. Thank you.

Code: 
Sub SetFields()
On Error Resume Next
Dim objIE As Object
Dim objParent As Object
Dim objInputElement As Object
Dim lngRow As Long
Dim c As Range
Dim r As Range
Dim intReply As Integer
Dim totRecs As Long
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim wb2 As Workbook
Dim ws2 As Worksheet
Dim time1 As Long
Dim time2 As Long
Dim lngLastRow As Long
Dim intReply1 As Integer
Dim origRecs As Long
'capture start time of macro
time1 = Timer

'enter the number of skus expected for this routine
intReply = InputBox("Enter the number of skus for which content needs to be updated.", "SKU COUNT")

'turn off application updates
Call ToggleEvents(False)

'set number of records and files updated to 0
totRecs = 0

'set variable to the for the workbook and
'first worksheet if changed this needs to be updated
'###################################
Set wb1 = ThisWorkbook
Set ws1 = wb1.Sheets(1)
'Set wb2 = Workbooks(strReply)
Set ws2 = wb1.Sheets("CONTENT")
'###################################
    
'find the last row of data in the file
lngLastRow = ws2.Range("A" & ws1.Rows.Count).End(xlUp).Row
        
'create a running count of items for which content is being updated
totRecs = totRecs + (lngLastRow - 4)
origRecs = totRecs

Do Until totRecs = 0

    'copy last record from the product data file and paste into row 3
    ws2.Rows(totRecs + 4).Copy
    ws2.Range("A3").PasteSpecial xlPasteAll
    
    Set objIE = GetIEApp 'Make sure an IE object was hooked
    
    If TypeName(objIE) = "Nothing" Then
        MsgBox "Could not hook Internet Explorer object", vbCritical, "GetFields() Error"
        GoTo Clean_Up
    End If
    
    'create loop to iterate through records in
    'flat file with all products and product data
    ws1.Activate
    For lngRow = 2 To ActiveSheet.UsedRange.Rows.Count
        If ActiveSheet.Cells(lngRow, cElement_SetValue) <> "" Then
        On Error GoTo 0
            'If we have a parent name/ID drill to that element, otherwise point to whole document
            If ActiveSheet.Cells(lngRow, cForm_name).Text <> "" Then
                Set objParent = objIE.Document.forms(ActiveSheet.Cells(lngRow, cForm_name).Text)
            ElseIf ActiveSheet.Cells(lngRow, cForm_Id).Text <> "" Then
                Set objParent = objIE.Document.forms(ActiveSheet.Cells(lngRow, cForm_Id).Text)
            Else: Set objParent = objIE.Document.all
            End If
            On Error GoTo 0
            With objParent
                If ActiveSheet.Cells(lngRow, cElement_Type) = "radio" Then
                    Set objInputElement = objParent.tags("INPUT").Item(ActiveSheet.Cells(lngRow, cElement_Name).Text)
                    objInputElement.Item(ActiveSheet.Cells(lngRow, cElement_Name).Text)(ActiveSheet.Cells(lngRow, cElement_SetValue)).Checked = True
                    Set objInputElement = Nothing
                ElseIf ActiveSheet.Cells(lngRow, cElement_Type) = "checkbox" Then
                    objParent.Item(ActiveSheet.Cells(lngRow, cElement_ID).Text).Checked = ActiveSheet.Cells(lngRow, cElement_SetValue).Value
                ElseIf ActiveSheet.Cells(lngRow, cElement_Type) = "select-one" Then
                    objParent.Item(ActiveSheet.Cells(lngRow, cElement_ID).Text).Options.selectedIndex = ActiveSheet.Cells(lngRow, cElement_SetValue).Value
                ElseIf ActiveSheet.Cells(lngRow, cElement_Type) = "select-multiple" Then
                    objParent.Item(ActiveSheet.Cells(lngRow, cElement_ID).Text).Item(ActiveSheet.Cells(lngRow, cElement_SetValue).Value).Selected = True
                     
'**************ERROR OCCURS BELOW           
                Else
                    objParent.Item(ActiveSheet.Cells(lngRow, cElement_Name).Text).Value = CStr(ActiveSheet.Cells(lngRow, cElement_SetValue))
                End If
'**************ERROR OCCURS ABOVE

            End With
            'On Error Resume Next
            If Err.Number <> 0 Then
            Debug.Print "Error Writting: Row " & lngRow, ActiveSheet.Cells(lngRow, cElement_Name), ActiveSheet.Cells(lngRow, cElement_SetValue)
            Err.Clear
            End If
            
        End If
    If lngRow = 207 Then Stop
    Next lngRow
 
Upvote 0
I wouldn't use that code or even try to expand it for the reasons already stated.

You need to do some debugging to investigate the cause of the error. What is the value of ActiveSheet.Cells(lngRow, cElement_Type) and ActiveSheet.Cells(lngRow, cElement_Name).Text? What type of HTML element is it? Maybe setting its Item.Value is invalid for the type of HTML element.
 
Upvote 0
John_w said:
I wouldn't use that code or even try to expand it for the reasons already stated.

You need to do some debugging to investigate the cause of the error. What is the value of ActiveSheet.Cells(lngRow, cElement_Type) and ActiveSheet.Cells(lngRow, cElement_Name).Text? What type of HTML element is it? Maybe setting its Item.Value is invalid for the type of HTML element.
Click to expand...


What is ActiveSheet.Cells(lngRow, cForm_name).Text --> This is blank. This is typically blank as the elements being filled are on the ProductInfoForm or the Form_ID.

What is the value of ActiveSheet.Cells(lngRow, cElement_Type) --> Text

What is the value of ActiveSheet.Cells(lngRow, cElement_Name).Text --> CN:Brand:77:T:Y

What type of HTML element is it? - None of the elements update whether it's a text or select-one, which are the only two elements on this particular page.

It's like for this category only, it doesn't recognize ProductInfoForm or can't find the element names in that form.


Here is the debugging that I've done so far. I filled in the values.
Rich (BB code): 
If ActiveSheet.Cells(lngRow, cForm_name).Text <> "" Then 'ActiveSheet.Cells(lngRow, cForm_name).Text  = "", so move to next elseif 
                Set objParent = objIE.Document.forms(ActiveSheet.Cells(lngRow, cForm_name).Text)

' - ActiveSheet.Cells(lngRow, cForm_Id).Text = "ProductInfoForm", so set objParent = "ProductInfoForm"
            
ElseIf ActiveSheet.Cells(lngRow, cForm_Id).Text <> "" Then  
                
'After the below line of code, when I hover ovre objParent, is just says [object].  Should it be "ProductInfoForm"?  The error I receive later in the code is object required, so it's like it's not picking it up or something.

Set objParent = objIE.Document.forms(ActiveSheet.Cells(lngRow, cForm_Id).Text)


            Else: Set objParent = objIE.Document.all
End If


Next, it moves into this section of code.


Rich (BB code): 
'the element type is a text, so it moves to the else part of this if statement

With objParent
                If ActiveSheet.Cells(lngRow, cElement_Type) = "radio" Then
                    Set objInputElement = objParent.tags("INPUT").Item(ActiveSheet.Cells(lngRow, cElement_Name).Text)
                    objInputElement.Item(ActiveSheet.Cells(lngRow, cElement_Name).Text)(ActiveSheet.Cells(lngRow, cElement_SetValue)).Checked = True
                    Set objInputElement = Nothing
                ElseIf ActiveSheet.Cells(lngRow, cElement_Type) = "checkbox" Then
                    objParent.Item(ActiveSheet.Cells(lngRow, cElement_ID).Text).Checked = ActiveSheet.Cells(lngRow, cElement_SetValue).Value
                ElseIf ActiveSheet.Cells(lngRow, cElement_Type) = "select-one" Then
                    objParent.Item(ActiveSheet.Cells(lngRow, cElement_ID).Text).Options.selectedIndex = ActiveSheet.Cells(lngRow, cElement_SetValue).Value
                ElseIf ActiveSheet.Cells(lngRow, cElement_Type) = "select-multiple" Then
                    objParent.Item(ActiveSheet.Cells(lngRow, cElement_ID).Text).Item(ActiveSheet.Cells(lngRow, cElement_SetValue).Value).Selected = True

'the element type is a text, so it moves to this line of code and that's where I receive the error.
'Error 424 object required
                                
                Else
                    objParent.Item(ActiveSheet.Cells(lngRow, cElement_Name).Text).Value = CStr(ActiveSheet.Cells(lngRow, cElement_SetValue))               
End If

End With

I'm open to other ways to do this but the issue I have is that the ProductInfoForm is a dynamic form. Depending on the product category, the elements change, so I've used this to pull down the fields and then setup formulas to correct them. Then I run a macro that loops through all the fields and populates the form. Thanks!
 
Upvote 0
Sorry, but I can't really help you to debug this without the HTML of the page and your Excel workbook including code, which reproduces the exact error you are describing.
 
Upvote 0
You must log in or register to reply here.

Similar threads

NessPJ
  • Question Question
VBA - Send Chat text to a Discord window?
Replies
2
Views
922
NessPJ
NessPJ
reasem
  • Question Question
Pulling Tables from Internet Explorer help (VBA)
Replies
1
Views
959
Worf
Worf
C
  • Solved
IE Print Screen Not Correct Info
Replies
8
Views
758
Joe4
Joe4
P
  • Question Question
IE VBA Web Scrapping Table extract issue
Replies
1
Views
1K
Worf
Worf
V
  • Solved
Could not set the List property. Permission denied - Run time error 70.
Replies
6
Views
2K
vbaNumpty
V

Forum statistics

Threads
1,224,829
Messages
6,181,222
Members
453,024
Latest member
Wingit77

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top