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.

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:

        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)

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.