Please help me with this VB Code. Internet Explorer Automation Error

[Peter h]

So, I've been trying to write this code for about a week now, and can't seem to get it to work. I've tried adapting a lot of different codes that I've found, and I feel like I'm close, but I'm still getting hung up.

What I'm doing is this: I analyze samples for work, and the analysis get entered into a web form on our server. Well, I wanted to get away from keeping paper records of our data, so I created a userform that will store data into an appropriate worksheet, but I wanted it to automatically fill in the web form that the data has to be entered into, so that I don’t have to double enter everything. So, in my userform, when I click the submit button it should send the data to both places (worksheet and webform). Here is the code I've gotten so far.

Private Sub CommandButton1_Click()
Dim LastRow As Long, ws As Worksheet

    Set ws = Sheets("Feed")

    LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
    ws.Range("A" & LastRow).Value = Now
    ws.Range("B" & LastRow).Value = TextBox1.Text 'arrival time
    ws.Range("C" & LastRow).Value = TextBox2.Text 'sample time
    ws.Range("D" & LastRow).Value = ComboBox1.Text   'Control Room Operator
    ws.Range("E" & LastRow).Value = ComboBox2.Text   'Analyst
    ws.Range("F" & LastRow).Value = TextBox3.Text   'moisture
     

Dim IE As InternetExplorerMedium 
Dim targetURL As String
Dim webContent As String
Dim sh
Dim eachIE

targetURL = "http://miap33wsapx16/asoma/asomaentryform.aspx"
Set IE = New InternetExplorerMedium

With IE
    .Visible = True ' Set to true to watch what's happening
    .Navigate targetURL

    Do Until IE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Loop

   [B] .Document.getElementsByTagName("ddlSelection").SelectedIndex = "Feed"[/B]    
    Do Until IE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    
    .Document.All("Sample_Arrival_Time").Value = TextBox1.Text
    .Document.All("SampleTaken").Value = TextBox2.Text
    .Document.All("Control_Room_Operator").Value = ComboBox1.Text
    .Document.All("Analyst").Value = ComboBox2.Text
    .Document.All("Moisture").Value = TextBox3.Text
    '.Document.All("btnAccept").Click    'Activate this when code is working
End With
    
Set eachIE = Nothing
'Set sh = Nothing

While IE.Busy  ' The new process may still be busy even after you find it
  DoEvents
  Wend

   Unload feedform
End Sub

When I run the userform and click Submit, the webform opens, but it hangs up on the Bolded line. I'm getting this error:

Run-Time error '-2147467259(80004005)':
Automation error
Unspecified error

I've been having issues trying to get it to select the drop down list in the webform and select the type of analysis that I'm doing. Here is the code for the drop down list:

 <select name="ddlSelection" tabindex="1" id="ddlSelection" language="javascript" style="width=152px; POSITION: Absolute; left:524px; Z-Index:102; top:56px" onchange="__dopostBack('ddlSelection',' ')">
          <option=""></option>
          <option="ELF_Slag>ELF_Slag</option>
          <option="ELF_Matte">ELF_Matte</option>
          <option="ISA">ISA</option>
          <option="Conv_Slag">Conv_Slag</option>
          <option="Revert">Revert</option>
          <option="Feed">Feed</option>

Any help I can get, I'd appreciate it. I'm still pretty new to VBA coding, so if this is totally wrong, hopefully I can get on the right track with your guys' help. Thanks again.

 

[John_w]

Firstly the IE wait loops are wrong. They should be:

    Do Until IE.readyState = READYSTATE_COMPLETE
        DoEvents
    Loop

Secondly, getElementsByTagName returns an array of elements, so you should specify an index to reference a specific element in the array, and SelectedIndex is a number not a string:

      .Document.getElementsByTagName("ddlSelection")(0).SelectedIndex =   6

Or in this case it's better to use getElementById:

.Document.getElementById("ddlSelection").SelectedIndex = 6

Also, the HTML for the option tags looks wrong. Option tags are normally like this (ignore the space after each "<"):

          < option value="">< /option>
          < option value="ELF_Slag>ELF_Slag< /option>

 

[Peter h]

Thank you do much for the response and the help. I'll have to wait to get to work in the morning before I can try it.

I thought the wait loops were probably wrong. I tried them so many different ways, and that's how they ended up. I've copied probably 10 sample codes and tried to adapt them to my situation that it's difficult to keep them straight. Lol.

I also had tried using an index number to change the drop down list selection, but it wasn't working. But for some reason I was using 7 rather than 6. I think I always forget that 0 is a number that comes before 1 . I don't know.

Also, the html code could be wrong, because I didn't copy and paste it, I just typed it out from the source. So the mistake is probably in my transfer of the code.

I'll let you know tomorrow if I can get the code working. Thanks again.

 

[Peter h]

Ok, John_W, you sir are a life saver..

I used the getElementById syntax that you suggested and it worked like a charm. I had to also add a FireEvent line of code to get it to make the change once the selection was made, but it is working great now. A million thanks.

 

[Peter h]

A new problem has occurred. So, with the code it now opens the webform, and selects the analysis from the dropdown list, but now after the 2nd wait loop I'm getting the error:

"Object Variable or With block variable not set"

So, I'm thinking that it chooses the drop down list selection, and when that loads the code sees the webform as a new one so then the IE object no longer is "Set". Am I assuming right? If that is right, do I have to re-set the IE Object to the same thing as at the beginning of the code? Here is the code I've got now. It works through the second wait loop, and stops at the first data entry point ("Sample Arrival Time"):

Dim IE As InternetExplorerMedium ' This object (the "medium" variety as opposed to "InternetExplorer") is necessary in our security climate
Dim targetURL As String

targetURL = "http://miap33wsapx16/asoma/asomaentryform.aspx"
Set IE = New InternetExplorerMedium

    IE.Visible = True ' Set to true to watch what's happening
    IE.Navigate targetURL

    Do Until IE.ReadyState = READYSTATE_COMPLETE
        DoEvents
    Loop

     IE.Document.getElementById("ddlSelection").SelectedIndex = 6
     IE.Document.getElementById("ddlSelection").FireEvent ("onchange")
         
    Do Until IE.ReadyState = READYSTATE_COMPLETE
        DoEvents
    Loop
    
Set IE = New InternetExplorerMedium
    
    IE.Document.getElementById("Sample_Arrival_Time").Value = TextBox1.Text
    IE.Document.getElementById("SampleTaken").Value = TextBox2.Text
    IE.Document.getElementById("Control_Room_Operator").SelectIndex = ComboBox1.ListIndex
    IE.Document.getElementById("Analyst").Value = ComboBox2.ListIndex
    IE.Document.All("Moisture").Value = TextBox3.Text
    '.Document.All("btnAccept").Click    'Activate this when code is working
    
    Do Until IE.ReadyState = READYSTATE_COMPLETE
        DoEvents
    Loop
    
Set eachIE = Nothing

My data entry syntax's may be off also, but I haven't had a chance to really mess with them yet since the code breaks before it even gets that far.

The second "Set IE" syntax is only there because I wasn't sure how to solve the error that the object wasn't set.

 

[John_w]

So, I'm thinking that it chooses the drop down list selection, and when that loads the code sees the webform as a new one so then the IE object no longer is "Set". Am I assuming right?

No, the page is loaded into the existing IE object, unless the page opens a new IE window. Therefore you don't need the second Set IE = New InternetExplorerMedium.

The error is probably due to there being no element with id="Sample_Arrival_Time".

 

[Peter h]

Maybe the issue is just that I don't understand JavaScript, or HTML all that much. Plus still being pretty new to VBA doesn't help all that much either... lol.

So, initially there isn't an element with the id="Sample_Arrival_Time", but when you select an option in the drop down list the form updates with textboxes for data to be entered. In this specific form there is a textbox for the arrival time of the sample, the time the sample was taken, and for the moisture of the sample, plus 2 more drop down lists that are to select names. So, when I first open the form it is basically blank, and then when I select the sample type from the drop down list the form changes and allows me to enter my data depending on what type of sample I'm dealing with. I've looked at the code before and after I make the selection, and the form name and url are all the same, so it's just a javascript action that's happening that updates the form upon selection. That's why I have the 2nd wait loop in after the drop down list selection, so that it gives the explorer time to update the page. So, I thought that once the 2nd wait loop was done, and the page was done loading it would then see the new element with the id="Sample_Arrival_Time".

Does any of that make sense? lol. I know it's probably difficult for you to really get what I'm saying without being able to see the page. I appreciate the help and time you're taking with me.


*Edit- I guess it would be similar to something like say Autotrader or some other car website where you select a vehicle make from a drop down list, and then the next drop down list automatically populates with the appropriate models. Once the first selection is made then the form updates to the appropriate settings depending on your first choice.

 

[Peter h]

I figured it out. I put

Application.Wait(Now + #0:00:02#)

After the second wait loop to see if maybe it was searching for the new elements before they could load. And sure enough after putting the wait syntax in it sees the new elements just fine. So, now I have a new error.

There are 2 drop down lists on the form. One you select a control room operator's name, and the other one you choose an analyst's name. Neither list change the form like the first drop down lists. They are there only for the purpose of selecting a name from a list. So, I have this line to make the changes:

IE.Document.getElementById("Control_Room_Operator").SelectIndex = ComboBox1.ListIndex
        IE.Document.getElementById("Analyst").SelectIndex = ComboBox2.ListIndex

Which is the exact same code as I used to get the first drop down list to work. But this time it is saying "Object does not support this property or method". What's different that it won't accept this code now? Am I missing something here?

 

[John_w]

It should be SelectedIndex, not SelectIndex.

Also, instead of the artificial 2 second wait, which may not always be long enough, you could do this:

Do
    DoEvents
Loop While IE.Document.getElementById("Sample_Arrival_Time") Is Nothing
IE.Document.getElementById("Sample_Arrival_Time").Value = TextBox1.Text

 

[Peter h]

Good catch. My proofreading skills are a bit lacking. Lol.

And that wait loop worked like a charm. Thank you very much. I was concerned that I could run into issues with a specified wait time if the network was running a bit slow one day.

Thanks to you I've got all the code done for what I wanted it to do. I just have to copy it and adjust it to each of my forms.

One quick question before I'm done, though. Is there a way to assign specific list index numbers to each item in my combo boxes without rearranging the alphabetical order I have them in? For example: the second item in the list is actually index # 6.