Help filling in a web form from access.

[wackywoo105]

I can login to a website and navigate to this page. I want to fill in the form. The problem is the page source code doesn’t show the elements that I want to complete, so I can’t figure out how to enter any data. How do I go about completing the form, which includes a drop-down list for title, and clicking “save and next”? I’m using the code below. Is this the best way to do it?

Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")

With IE
    .Visible = True
    .Navigate "login page"
End With

Do Until IE.ReadyState = 4 And Not IE.busy
    DoEvents
Loop

IE.Document.all("PlaceHolderMain_signInControl_UserName").Value = "mylogin"
IE.Document.all("PlaceHolderMain_signInControl_password").Value = "mypassword”

While IE.busy
   DoEvents
Wend

IE.Document.all("PlaceHolderMain_signInControl_login").Click

While IE.busy
   DoEvents
Wend

With IE
    .Navigate " /GOSOne"
End With

…Now what?

 

[Pat Hartman]

Does the blue message mean anything?

Sounds to me like they don't want you to paste.

 

[wackywoo105]

You can enter postcode and if you click the grey box a list of addresses drops down. If you click the manual address a pop up appears allowing you to enter the address.

I tried to post the html, but it is too long.

Is there a way to simulate key presses in IE? I can manually tab through the data fields. If I could set focus the first one I could possibly use send keys.

 

[Edgar_]

To fill out the form correctly, you need to understand how it's set up. Good news is, you can easily figure it out using your browser's Inspection tool. Just open the Development Tools, or DevTools, and you'll be able to see how the form works and fill it in.

> Open DevTools
> Look for the inspection Icon, it looks like this on a few browsers:

> You can also right click an element and choose Inspect
> Look for the event that should fire with the element
> In the case of Input elements, like PostCode appears to be, it usually requires you to fire the onblur event (which is like LostFocus in Access)
> in the case of Select elements (dropdowns), it usually requires your to fire the onchange event
> Some elements may require you to set the value attribute of the HTML tag

When you don't know what event is fired, you can use your browser's console and enter some event listeners and see if the events I mentioned are the ones that fired. This is all reverse engineering, so it requires you to do some of this work.

This is code I've used for firing events from VBA:

'Snippet 1
Dim element As IHTMLElement
element.FireEvent "onblur"

'Snippet 2
Dim onChangeEvent As Object
Set onChangeEvent = doc.createEvent("HTMLEvents")
onChangeEvent.initEvent "change", True, False
element.dispatchEvent onChangeEvent

This is a VBA/JavaScript task, go take a look at the source code of your web form to figure out how to make this work.

Edit: One thing I like to do prior to coding in VBA is using the browser's console and looking for the event and trying to see if I make it fire, for example (this is JS), I want to try to fill in an input with Id "postcode", for that, I'd do this
document.getElementById("postcode").setAttribute("value","12345")
or
document.getElementById("postcode").value = "12345"
And if text was entered, then I use this to see if I make the onblur event fires:
document.getElementById("postcode").fireEvent("onblur")

If it works, you can then translate it to VBA. That is, if what happens when I fill it out manually also happens with the code above, I can use that to automate it from VBA.

 

[wackywoo105]

Thanks. The elements didn't show up in the html, but using the tool you suggested revealed them and I have managed to enter data.

 

[Edgar_]

Thanks. The elements didn't show up in the html, but using the tool you suggested revealed them and I have managed to enter data.

Glad to know it works now. Remember that just entering the data will not fire events if the elements have events associated to them. For example, post code inputs usually trigger the onblur event to fill out other inputs like "State", "City", etc. And Select controls sometimes fire the onchange event to reveal additional html elements related to that selection. If that is the case, you can use the mentioned snippets.

 

[Edgar_]

Is there a way to simulate key presses in IE? I can manually tab through the data fields. If I could set focus the first one I could possibly use send keys.

You can send keys, yes.

 

[MsAccessNL]

Did you try right click > inspect to see all the html elements. You need a htmlElement.Tagname or elementid to tefer to the specific inputbox.

 

[MsAccessNL]

Did you try right click > inspect to see all the html elements. You need a htmlElement.Tagname or elementid to tefer to the specific inputbox.

Sorry didn’t see the other post.

 

[wackywoo105]

Thanks again everyone.

Is there a way using this code to read the entire page source/html into a string variable? I used to use the code below but it started getting blocked by the site I was querying.

    Dim winReq As Object
    Dim HTM, Address As Variant
    
 '  Set winReq = New WinHttpRequest   'now use line below to stop secure channel error on win 7
    Set winReq = CreateObject("MSXML2.XMLHTTP")
    With winReq
        .Open "GET", sStr, False
        .Send
        HTM = Split(Replace(.ResponseText, """", "'"), "<")
        If .Status <> 200 Then
            MsgBox ("Address not found")
            Exit Sub
        End If
    End With

 

[wackywoo105]

Also I'm trying to click this ckeckbox:

'<input name="IsFirstTest" id="IsFirstTest" type="checkbox" value="true" data-val-required="The First test field is required." data-val="true" htmlattributes="{ id = cbkFirstTest, class = form-control }" data-group="cbTest">

I've tried using all of these and more:

IE.Document.all("IsFirstTest").Click
IE.Document.all("cbkFirstTest").Click
IE.Document.all("IsFirstTest").value = "true"
IE.Document.all("IsFirstTest").value = true

but none work.

Can anyone help with how to tick this checkbox?

EDIT This works:

IE.Document.getElementById("IsFirstTest").Click

Does anyone know why the above doesn't?

 

[Edgar_]

Can anyone help with how to tick this checkbox?

If you can set the .checked property to true, that's better and more correct

Does anyone know why the above doesn't?

The .all property is ambiguous, it's better if you use .getElementById or other methods, avoid the deprecated when there are better alternatives.

If you have other snippets using the .all property, replace them.

 

[Edgar_]

Is there a way using this code to read the entire page source/html into a string variable? I used to use the code below but it started getting blocked by the site I was querying.

Avoid spamming websites looking for server responses, always add a few seconds between attempts or you will be blocked temporarily (if the server has such measures against brute force attacks).