Internet Explorer automation with Excel VBA - beginner question

[AlexCS]

Hello everyone,

I have been trying to automatically fill in an Internet Explorer form with values from an Excel spreadsheet. I am using the below VBA code to try and select an option from a drop box to begin with.

Sub InternetExplorerForm()
Dim IntExpl As Object
Set IntExpl = CreateObject("InternetExplorer.Application")

With IntExpl
.navigate "Zoll online - Formulare und Merkblätter"
.Visible = True
Do Until IntExpl.ReadyState = READYSTATE_COMPLETE
.Document.getElementById("f103950d103956").Value
Loop
End With
End Sub

However, I am getting an error stating that Method Document of object Iwebbrowser2 failed. I believe this is due to one of two reasons: either I am using the Document method incorrectly (in the IE object model Document is listed as an object rather than a method - perhaps an object declaration is needed) OR I am using the incorrect Document ID due to not being very good at understanding the source code. However, when using Developer - View - Class ID and information, I am getting the following info next to the drop box I am trying to manipulate ".form-group first-group #f103950d103956 "

Does anyone have an idea what I am doing wrong here? I would appreciate any nudge in the right direction since I have been trying for days to make this work.

Thank you very much!

Alex

 

[AlexCS]

Hi Rory and Kyle,

@Rory, thank you for the tip, it will help a lot with structuring my posts better. And thanks a lot for offering to invest a bit of your time in testing and correcting the code, I really appreciate it.

@Kyle, thanks for the interest, what I am actually trying to do is automatically fill in some forms that are located online with figures taken from an Excel spreadsheet. I chose IE because my company does not use other browsers and I also thought the automation may be easier since IE and Excel are both part of the Windows package. I need to fill in these forms automatically because it takes a lot of time to fill them in several times a month and there can also be quite a few human errors with the input of important information.

Kind regards,

Alex

 

[RoryA]

There are probably better ways (I'm sure Kyle will know them!) but this seems to work:

    Dim IntExpl               As Object    Dim dd                    As Object
    Dim dd1                   As Object
    Dim dd2                   As Object
    
    Set IntExpl = CreateObject("InternetExplorer.Application")


    With IntExpl
        .navigate "http://www.zoll.de/DE/Service/Formulare-Merkblaetter/formulare-merkblaetter_node.html"
        .Visible = True
        Do Until IntExpl.readystate = 4
        Loop
        Set dd = .Document.getElementById("f103950d103956")
        Set dd1 = .Document.getElementById("f103950d103960")
        Set dd2 = .Document.getElementById("f103950d103964")
        dd.Value = "Verbrauchsteuern_DC"
        dd.Click
        Do Until dd1.disabled = False
            DoEvents
        Loop
        dd1.Value = "Energiesteuer_DC"
        dd1.Click
        Do Until dd2.disabled = False
            DoEvents
        Loop
        dd2.Value = "Steueranmeldungen_DC"
        dd2.Click
    End With

 

[AlexCS]

Rory, thank you so much for this! the .disabled property made a difference and everything works now. It really gives me hope that I will eventually manage to do what I am trying to do

Have a nice rest of the day and thanks for all the life saving work that all of you do on this forum!

 

[RoryA]

Glad to help.

 

[Kyle123]

Glad you got it sorted

Since this was more of a general exercise in automating ie than an actual need then I'd stick with this approach. Generally however, there are more efficient ways of sending data to a webform, for example, if you were looking for the results of the search from your conditions above, you can send the data directly to the server and handling the page returned in Excel, removing the need for internet explorer all together.

Public Sub SearchTable()


    Dim oDoc As Object
    Set oDoc = CreateObject("htmlFile")
    Dim x As Long, y As Long, tr As Object, td As Object
    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", "http://www.zoll.de/SiteGlobals/Forms/FormularMerkblattSuche/FormularMerkblattSuche_ThemenSuche_form.html#theme-search-anchor", False
        .setrequestheader "Content-Type", "application/x-www-form-urlencoded"
        .send "resourceId=103950&input_=103806&pageLocale=de&" & _
                    "bereich=Verbrauchsteuern_DC" & _
                    "&bereich.GROUP=1" & _
                    "&thema=Energiesteuer_DC" & _
                    "&thema.GROUP=1" & _
                    "&rubrik=Steueranmeldungen_DC" & _
                    "&rubrik.GROUP=1" & _
                    "&submit=Auswahl starten"
        oDoc.body.innerHtml = .responsetext
        With oDoc.getelementbyid("searchResult")
            For x = 0 To .Rows.Length - 1
                For y = 0 To .Rows(x).Cells.Length - 1
                    Sheet1.Cells(x + 1, y + 1).Value = .Rows(x).Cells(y).innerText
                Next y
            Next x
        End With
    End With
    
End Sub

If you look in the .Send Method, you can see that the parameters you are passing are the same as the values you select in the form (with a few extra parameters too)

 

[AlexCS]

Hi Kyle,

Sorry I only just got to work and tested your code, which I think works beautifully! Since what I am trying to do is not just an exercise - I really need to fill in one webform with Excel figures, but I did not want to annoy everyone on the forum with silly questions so I just asked for a kick in the right direction - I am very interested in what you did. Could you please just tell me what exactly are you using? It looks like HTML to me, but I am not sure since I have not used it much. I would really like to try and explore this option as well!

Thank you very much,

Alex

 

[Kyle123]

Ok, no problem, this might get a bit heavy a bit fast but here goes - Let me know if you need any clarification.

The code works by using the underlying methods that any web browser would, when you are browsing through the internet, you are actually sending requests to a remote web server and your browser takes the response sent back and parses it so that you can see the page. This is usually an HTML string, but could in fact be a wide array of things.

The code sends the same request to the web server that a browser would, so gets the same response. The request is text based so usually easily copied, to see the actual request you need to use some sort of developer tools - these are built into modern browsers, I use Chrome so I hit F12, but they are also available in Firefox and later versions of IE. Once you have them open, you can see what data is actually being sent and received by the browser.

For the above request, this is what is sent:
http://s12.postimage.org/ov5tldj99/browser.png

The Request URL is the address the data is being sent to. - http://www.zoll.de/SiteGlobals/Forms/FormularMerkblattSuche/FormularMerkblattSuche_ThemenSuche_form.html#theme-search-anchor

The Request Method is the type of request to send. - POST

The Content-Type is the form of the data that we are sending, this allows the webserver to handle it correctly: application/x-www-form-urlencoded

The Form data is the data that we are actually sending, you get this in POST requests:

resourceId:
103950

input_:
103806

pageLocale:
de

bereich:
Verbrauchsteuern_DC

bereich.GROUP:
1

thema:
Energiesteuer_DC

thema.GROUP:
1

rubrik:
Steueranmeldungen_DC

rubrik.GROUP:
1

submit:
Auswahl+starten

This is the most basic information that we must send in order to get the response we are after from Excel. For other sites, you will have to include more items from the Request Header Section in order for the site to respond as it would to a browser.

Now we have this data, we can go about building a web request, the MSXML2.XMLHTTP object (as well as a few others) allow us to send this request directly to the server.

You should now be able to see where all the cryptic looking strings come from in the above text, they're the properties that we extracted from the browser request.

The response from the server is in the form of a web page, which is really just a long string (to see this just right click on any web page and view source), so you can deal with it in a number of ways.

If you are only looking for a small part of the data, you can use string techniques like left, right, mid, instr etc.

If you are looking for something a bit more complex, we can use the string as the source of an HTML page - this allows us to use properties and methods of internet explorer like getElementsbyTagName(), getElementsbyID() etc. In the above, I have used the ID of the search results to find the results table, the getElementbyID method returns an HTML object, in this case an HTML table that we can loop through - this allows us to populate and Excel sheet with the contents of the table.

Since non of this uses Internet Explorer it is much faster - using Internet Explorer introduces a lot of overhead that we don't need, it's much more efficient to interact with the web server directly. It is however, a lot of trial and error, some web servers can be very fussy in which headers are set, whereas others will require you to be logged in and have a valid cookie.

The code I posted was an illustration, so feel free to pull it apart and see how it works, since it's written in quite terse short hand, it would be easier to set all the objects to a variable rather than using the With statements - also use the locals table extensively when parsing html as it allows you to navigate through the structure of the returned html and see all the relevant properties.

Does this help any?

 

[AlexCS]

Hi Kyle,

Wow, thanks a lot for taking the time to explain this to me, I think I do understand it since I could find all the elements that you posted to the server in the HTML code that Internet Explorer shows me (F12 works with IE as well ). However, I am afraid my users are quite reluctant to transmit information to the server that they cannot see. Since the figures will have an impact on our tax returns, they want me to automatically fill in the form and then they will press Submit after checking that the information is correct. Along these lines, do you maybe know how to press the Submit button "Auswahl starten" in IE? The code provides no button ID and when I try to submit the whole form I get the result equivalent to pressing button "Suche starten" on top of the page..

Thanks again for your amazing ideas, I cannot wait to use them in other projects that involve retrieving info from a server rather than posting info via a webform.

Kind regards,

Alex

 

[Norie]

Alex

What do you mean by the whole form?

There is actually more than one form on the page.

The Auswahl Starten button is on the third form on the page.

I'm not sure what results you expect from clicking the button but this seems to at least do something.

        Set frm = dd2.form


        Set x = frm.Item("submit")(0)

        x.Click

 

[AlexCS]

Hi Norie,

These are exactly the results that I was expecting, thank you very much! I was only trying to press the Auswahl Starten button and your code does just that. I read somewhere that if there are more forms on a page, the numbering starts at zero. So I tried to submit forms from 0 to 5 and the only time something actually happened was when I submitted form 0 - therefore I thought that there is only one form but obviously that was the wrong assumption to make .

Thanks again!

Alex