CODEWITHSUNDEEP
vbaexcelinternet-explorer
WK145
WK145

Reputation: 23

VBA-Excel: Filling forms in an IE Window

I'm hoping someone can help. I'm trying to speed up the process of filling a webform that must be completed dozens or hundreds of times with information stored in excel.

To do this, I need one button to open an IE window and navigate to a certain website's login page (I've figured this bit out). The user can then log in and navigate to the form that needs to be filled. Then, I'd like the user to be able to return to the excel page, click another button, which will automatically fill several drop downs and text boxes.

Within Excel, I already have some code to allow the user to search for the particular set of information that needs to go to the form, so all they should have to do is click the button to transfer it over. The first bit of the code is just this:

Public IE As Object

Public Sub OpenIE()
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.Navigate "loginpage url"
End Sub

Where I'm having trouble, however, is having a different function access the same IE window once the user has logged in and navigated to the form. Right now I've got this:

Sub FillMacro()

       Dim sh As Object, oWin As Object

Set sh = CreateObject("Shell.Application")

For Each oWin In sh.Windows
    If TypeName(oWin.document) = "HTMLDocument" Then
        Set IE = oWin
        Exit For
    End If
Next

    IE.Visible = True
    Application.Wait (Now + TimeValue("00:00:01"))
    IE.document.getElementById("idec").Value = "John"
    Application.Wait (Now + TimeValue("00:00:01"))
    IE.document.getElementById("idee").Value = "Smith"
End Sub

Most of that I've gotten from other posts on this forum, and while I'm a bit of a novice at this, the problem seems to be that for some reason VBA can't find the text boxes with the id of LastName or FirstName. What's more, the IE.Visible = True doesn't bring the IE window back to the foreground, so I'm trying to find the proper line to do that. When I try to run the code, I get an "Object Required" error at:

IE.document.getElementById("idec").Value = "John"

I've tried searching this site and will continue to look, but any help in the meantime would be greatly appreciated!

On the Internet Explorer page, here is the line for the first text box I'm trying to fill:

<input name="componentListPanel:componentListView:1:component:patientLastNameContainer:patientLastName" class="input300" id="idec" type="text" maxlength="60" value="">

Upvotes: 2

Views: 7832

Answers (1)

Ryszard Jędraszyk
Ryszard Jędraszyk

Reputation: 2412

Why not automate logging process as well? Login could be stored in Excel and its value read by macro from cell.

As Tim Williams suggests, if there is Iframe on website, use (for me it works only with contentwindow included):

IE.document.getElementById("iFrameIdHere").contentwindow.document.getEleme‌ntById("idec").Value = "John"

Instead of Application.Wait use:

Do Until IE.ReadyState = 4 And IE.Busy = False
    DoEvents
Loop

It will save you a lot of time when page loads fast and prevent errors when loading exceeds wait time. Use it ONLY after page reloads (meaning after navigating or anything what causes page reloads, especially .click on HTML elements.

Use early binding, it's a bit faster than creating objects. It can increase performance by a few percent based on page loading speed, the faster pages load, the bigger increase.

Set IE = New InternetExplorer

Finally, you can toggle loading pictures, depending on whether you need to download images from website.

Public Sub ShowPictures(ByVal EnabledStatus As Boolean)

Public ScrapingCancelled as Boolean
Dim obj_Shell
Dim v_Result As Variant
Set obj_Shell = CreateObject("WScript.Shell")

'Reads the registry key that determines whether 'Show pictures' Internet Explorer advanced setting is enabled

v_Result = obj_Shell.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Main\Display Inline Images")

Select Case v_Result
    Case "yes"  'Pictures are displayed
            If EnabledStatus = False Then _
            obj_Shell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Main\Display Inline Images", "no", "REG_SZ"
    Case "no"  'Pictures are not displayed
            If EnabledStatus = True Then _
            obj_Shell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Internet Explorer\Main\Display Inline Images", "yes", "REG_SZ"
    Case Else
            ScrapingCancelled = True
End Select

End Sub

No images loaded:

ShowPictures (0)

Images loaded:

ShowPictures (1)

A good practice is to set value to 1 in the end of macro.

Upvotes: 1

Related Questions