# 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


----------



## RoryA

Did you define READYSTATE_COMPLETE somewhere?


----------



## Norie

Alex

Do you really have the friendly name for the link in the code and not the URL?

Try this, it uses the URL and it grabs a reference to the select element you are insterested in.


		Code:
__


Sub InternetExplorerForm()
Dim IntExpl As Object
Dim dd 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")

    End With
End Sub


----------



## AlexCS

Hi Rory and Norie,

Thank you so much for your replies..I thought this might be a tough one even for this forum but I got answers already 
@Rory, you are right, I should have used the waiting until ready state like Norie did
@Norie, my code contains the URL only the website automatically translated that into the link
the code that you suggested works perfectly and I am getting no error anymore - could I maybe add a line to select the option I need from the drop down box, namely "Verbrauchsteuern"? I know I have to use the .Value method but I am not sure how to combine it with the Document object

Regards,

Alex


----------



## Norie

Alex

You are selecting a value from the select element not the document, so it's that you need to work with.

PS Wonder why the site didn't translate the URL in my code?

PPS The value you need isn't the value that's displayed.


----------



## AlexCS

Hi Norie,

Thanks for taking the time to look at this and sorry for the silly questions/remarks, I never worked with IE before 
I added dd.Value = "Verbrauchsteuern_DC" after your code and this does select the correct option. There is a little problem however, it does not really click it like a human user would do so the next dependant drop down box does not open. I tried .Submit but it returns an error. Is there a way to simulate clicking on an option?

As for the URL translation issue, I believe I should have used the special code insert tool to avoid the problem. I will try and use it next time. 

Alex


----------



## RoryA

Try adding:


		Code:
__


        dd.Click


----------



## AlexCS

Hi Rory,

That works perfectly, thank you very much for the tip 
I have one more question if you have a minute sometime today - my end goal is to select three options from three connected drop down boxes in a row and so far selecting the option in the first one and thus unlocking the connected box options I managed to achieve with your and Norie's help. However, when I try to repeat the same lines of code for the next two drop down boxes, the code just stops after selecting the option in the first box.

As advised, I am using the below (sorry I cannot figure out how to insert the code box that you use)


		Rich (BB code):
__


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

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")
       dd.Value = "Verbrauchsteuern_DC"
       dd.Click
       Set dd1 = .Document.getElementById("f103950d103960")
       dd1.Value = "Energiesteuer_DC"
       dd1.Click
       Set dd2 = .Document.getElementById("f103950d103964")
       dd2.Value = "Steueranmeldungen_DC"
       dd2.Click
End With

Have you encountered this issue before and, if so, how can one go around it?

Also, what resources did you use when learning how to automate IE with VBA? I cannot find any info out there that deals with this exact topic

Have a good day!

Alex


----------



## RoryA

To use code tags just type [code]all the code goes here[/code]

I'll have a play with the code later. Which also answers your third question - I make it up as I go along with trial and error and searches!


----------



## Kyle123

What data is it that you're trying to get, is it the search results?

If you are wanting to just return the search results to Excel, there's a more efficient way than automating IE.


----------



## 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:


		Code:
__


    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. 


		Code:
__


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.


		Code:
__


        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


----------

