Navigation
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
More options
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
How to automate web forms from VBA using Internet Explorer
VBA How To
How to automate web forms from VBA using Internet Explorer
by
CautionMP
Posted
(Edited
)
Since I have seen more and more questions regarding the automation of forms on the
Internet from the Microsoft Office suite I thought I would put together a little
FAQ on the subject. Essentially this is the result of me being tired of looking at
HTML source code when doing web page automation.
This is written in Excel (2000 SR-1) because it seemed to be the best format for
this type of ad-hoc reporting, but the concepts could be easily adapted to any
application with a VBA environment. As with all VBA projects, this is a work in
progress and I apologize in advance for any errors/omissions.
There are three routines:[ol]
[li][tt]GetFields()[/tt]: Simply lists all the forms and fields in a web page, with a few
relevent attributes.[/li]
[li][tt]SetFields()[/tt]: Takes the above listing and pushes data back to the web page
using the last column in the worksheet.[/li]
[li][tt]GetIEApp()[/tt]: Facilitates the connection to a current instance of Internet
Explorer (I went this route to cover popup/SSL pages that you may or may not
be able to navigate of directly).[/li][/ol]
Enjoy,
CMP
Internet from the Microsoft Office suite I thought I would put together a little
FAQ on the subject. Essentially this is the result of me being tired of looking at
HTML source code when doing web page automation.
This is written in Excel (2000 SR-1) because it seemed to be the best format for
this type of ad-hoc reporting, but the concepts could be easily adapted to any
application with a VBA environment. As with all VBA projects, this is a work in
progress and I apologize in advance for any errors/omissions.
There are three routines:[ol]
[li][tt]GetFields()[/tt]: Simply lists all the forms and fields in a web page, with a few
relevent attributes.[/li]
[li][tt]SetFields()[/tt]: Takes the above listing and pushes data back to the web page
using the last column in the worksheet.[/li]
[li][tt]GetIEApp()[/tt]: Facilitates the connection to a current instance of Internet
Explorer (I went this route to cover popup/SSL pages that you may or may not
be able to navigate of directly).[/li][/ol]
Code:
[navy]Option Explicit[/navy]
[navy]Option Compare Text[/navy]
[navy]Const[/navy] cForm_name [navy]As Long[/navy] = 1
[navy]Const[/navy] cForm_Id [navy]As Long[/navy] = 2
[navy]Const[/navy] cElement_Name [navy]As Long[/navy] = 3
[navy]Const[/navy] cElement_ID [navy]As Long[/navy] = 4
[navy]Const[/navy] cElement_nodeName [navy]As Long[/navy] = 5
[navy]Const[/navy] cElement_Type [navy]As Long[/navy] = 6
[navy]Const[/navy] cElement_Value [navy]As Long[/navy] = 7
[navy]Const[/navy] cElement_SetValue [navy]As Long[/navy] = 8
[navy]Sub[/navy] SetFields()
[navy]On Error Resume Next[/navy]
[navy]Dim[/navy] objIE [navy]As Object[/navy]
[navy]Dim[/navy] objParent [navy]As Object[/navy]
[navy]Dim[/navy] objInputElement [navy]As Object[/navy]
[navy]Dim[/navy] lngRow [navy]As Long[/navy]
[navy]Set[/navy] objIE = GetIEApp
[green]'Make sure an IE object was hooked[/green]
[navy]If[/navy] TypeName(objIE) = "Nothing" [navy]Then[/navy]
MsgBox "Could not hook Internet Explorer object", vbCritical, "GetFields() Error"
[navy]GoTo[/navy] Clean_Up
[navy]End If[/navy]
[navy]For[/navy] lngRow = 2 [navy]To[/navy] ActiveSheet.UsedRange.Rows.Count
[navy]If[/navy] ActiveSheet.Cells(lngRow, cElement_SetValue) <> "" [navy]Then[/navy]
[green]'If we have a parent name/ID drill to that element, otherwise point to whole document[/green]
[navy]If[/navy] ActiveSheet.Cells(lngRow, cForm_name).Text <> "" [navy]Then[/navy]
[navy]Set[/navy] objParent = objIE.Document.Forms(ActiveSheet.Cells(lngRow, cForm_name).Text)
[navy]ElseIf[/navy] ActiveSheet.Cells(lngRow, cForm_Id).Text <> "" [navy]Then[/navy]
[navy]Set[/navy] objParent = objIE.Document.Forms(ActiveSheet.Cells(lngRow, cForm_Id).Text)
[navy]Else[/navy]
[navy]Set[/navy] objParent = objIE.Document.All
[navy]End If[/navy]
[navy]With[/navy] objParent
[navy]If[/navy] ActiveSheet.Cells(lngRow, cElement_Type) = "radio" [navy]Then[/navy]
[navy]Set[/navy] objInputElement = objParent.Tags("INPUT").Item(ActiveSheet.Cells(lngRow,
cElement_Name).Text)
objInputElement.Item(ActiveSheet.Cells(lngRow, cElement_ID).Text).Checked = [navy]True[/navy]
[navy]Set[/navy] objInputElement = [navy]Nothing[/navy]
[navy]ElseIf[/navy] ActiveSheet.Cells(lngRow, cElement_Type) = "checkbox" [navy]Then[/navy]
objParent.Item(ActiveSheet.Cells(lngRow, cElement_ID).Text).Checked = [navy]True[/navy]
[navy]Else[/navy]
objParent.Item(ActiveSheet.Cells(lngRow, cElement_Name).Text).Value = CStr(ActiveSheet.Cells(lngRow,
cElement_SetValue))
[navy]End If[/navy]
[navy]End With[/navy]
[navy]If[/navy] Err.Number <> 0 [navy]Then[/navy]
Debug.Print "Error Writting: Row " & lngRow, ActiveSheet.Cells(lngRow, cElement_Name),
ActiveSheet.Cells(lngRow, cElement_SetValue)
Err.Clear
[navy]End If[/navy]
[navy]End If[/navy]
[navy]Next[/navy] lngRow
Clean_Up:
[navy]Set[/navy] objParent = [navy]Nothing[/navy]
[navy]Set[/navy] objIE = [navy]Nothing[/navy]
[navy]End Sub[/navy]
[navy]Sub[/navy] GetFields()
[navy]On Error[/navy] [navy]GoTo[/navy] GetFields_[navy]Error[/navy]
[navy]Dim[/navy] objIE [navy]As Object[/navy]
[navy]Dim[/navy] objForms [navy]As[/navy] Object, objForm [navy]As Object[/navy]
[navy]Dim[/navy] objInputElement [navy]As Object[/navy]
[navy]Dim[/navy] objOption [navy]As Object[/navy]
[navy]Dim[/navy] lngRow [navy]As Long[/navy]
[navy]Dim[/navy] strComment [navy]As String[/navy]
[navy]Set[/navy] objIE = GetIEApp
[green]'Make sure an IE object was hooked[/green]
[navy]If[/navy] TypeName(objIE) = "Nothing" [navy]Then[/navy]
MsgBox "Could not hook Internet Explorer object", vbCritical, "GetFields() Error"
[navy]GoTo[/navy] Clean_Up
[navy]End If[/navy]
[green]'In case the sheet is being resused, clear it[/green]
ClearActiveSheet
[green]'Get the forms object[/green]
[navy]Set[/navy] objForms = objIE.Document.Forms
[green]'Test to see if there are forms before proceding[/green]
[navy]If[/navy] objForms.Length <> 0 [navy]Then[/navy]
[green]'Write the header[/green]
lngRow = lngRow + 1
[navy]With[/navy] 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"
[navy]End With[/navy]
[green]'End Header[/green]
[green]'Cycle through all the forms in the document[/green]
[navy]For Each[/navy] objForm [navy]In[/navy] objForms
[green]'Cycle through the input elements in the form[/green]
[navy]For Each[/navy] objInputElement [navy]In[/navy] objForm
lngRow = lngRow + 1
[navy]With[/navy] 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
[navy]If[/navy] objInputElement.Type = "submit" Or objInputElement.Type = "button" [navy]Then[/navy]
.Cells(lngRow, cElement_SetValue).Interior.Color = vbBlack
[navy]ElseIf[/navy] objInputElement.Type = "hidden" [navy]Then[/navy]
.Cells(lngRow, cElement_SetValue).Interior.Color = vbYellow
[navy]End If[/navy]
.Cells(lngRow, cElement_Value) = objInputElement.Value
[green]'build a list of the possible selections for a select elements[/green]
[navy]If[/navy] objInputElement.nodeName = "SELECT" [navy]Then[/navy]
[navy]For Each[/navy] objOption [navy]In[/navy] objInputElement
strComment = strComment & Chr(34) & objOption.Value & Chr(34) & ": " & objOption.Text & vbNewLine
[navy]Next[/navy] obj[navy]Option[/navy]
[green]'place the list as a comment in the SetValue column[/green]
.Cells(lngRow, cElement_SetValue).AddComment strComment
strComment = ""
[navy]End If[/navy]
[navy]End With[/navy]
[navy]Next[/navy] objInputElement
[navy]Next[/navy] objForm
[navy]End If[/navy]
Clean_Up:
[navy]Set[/navy] objInputElement = [navy]Nothing[/navy]
[navy]Set[/navy] objForm = [navy]Nothing[/navy]
[navy]Set[/navy] objForms = [navy]Nothing[/navy]
[navy]Set[/navy] objIE = [navy]Nothing[/navy]
[navy]Exit Sub[/navy]
GetFields_Error:
Debug.Print Err.Number, Err.Description
[navy]Resume Next[/navy]
[navy]End Sub[/navy]
[navy]Function[/navy] GetIEApp() [navy]As Object[/navy]
[navy]Dim[/navy] objShell [navy]As Object[/navy]
[navy]Dim[/navy] objWindows [navy]As Object[/navy]
[navy]Dim[/navy] objWindow [navy]As Object[/navy]
[navy]Dim[/navy] lngSingleWindow [navy]As Long[/navy]
[navy]Dim[/navy] intOption [navy]As Integer[/navy]
[navy]Dim[/navy] strMessage [navy]As[/navy] String, strReturnValue [navy]As String[/navy]
[navy]Set[/navy] objShell = CreateObject("Shell.Application")
[navy]Set[/navy] objWindows = objShell.Windows
lngSingleWindow = -1
[navy]For Each[/navy] objWindow [navy]In[/navy] objWindows
[green]'Build a list of windows, make sure they are Internet Explorer[/green]
[navy]If[/navy] Right(objWindow.FullName, 12) = "iexplore.exe" [navy]Then[/navy]
strMessage = strMessage & intOption & " : " & objWindow.LocationName & vbCrLf
[navy]If[/navy] lngSingleWindow = -1 [navy]Then[/navy]
lngSingleWindow = intOption
[navy]Else[/navy]
lngSingleWindow = 0
[navy]End If[/navy]
[navy]End If[/navy]
intOption = intOption + 1
[navy]Next[/navy]
[green]'Check if there are any IE windows[/green]
[navy]If[/navy] Len(strMessage) <> 0 [navy]Then[/navy]
[green]'Prompt to pick a window, used an InputBox for portability[/green]
[navy]If[/navy] lngSingleWindow > 0 [navy]Then[/navy]
[navy]Set[/navy] GetIEApp = objWindows.Item(CLng(lngSingleWindow))
[navy]Else[/navy]
strReturnValue = InputBox(strMessage, "Please select Browser window")
[green]'If the user cancels the input box an empty string is returned[/green]
[navy]If[/navy] strReturnValue <> "" [navy]Then[/navy]
[green]'Make sure the number selected is valid[/green]
[navy]If[/navy] Val(strReturnValue) >= 0 And Val(strReturnValue) <= intOption [navy]Then[/navy]
[navy]Set[/navy] GetIEApp = objWindows.Item(CLng(strReturnValue))
[navy]End If[/navy]
[navy]End If[/navy]
[navy]End If[/navy]
[navy]End If[/navy]
[navy]Set[/navy] objWindow = [navy]Nothing[/navy]
[navy]Set[/navy] objWindows = [navy]Nothing[/navy]
[navy]Set[/navy] objShell = [navy]Nothing[/navy]
[navy]End Function[/navy]
[navy]Public Sub[/navy] ClearActiveSheet()
ActiveSheet.UsedRange.Clear
ActiveSheet.Cells(2, 1).Activate
[navy]End Sub[/navy]Enjoy,
CMP
Please Note: 1 is Bad, 10 is Good :-)
Part and Inventory Search
-
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.