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]

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.

No, but there is another way to achieve what I think you're asking for. Instead of having to know the required selectedIndex of the HTML select element (dropdown box), we can write a function that searches the visible options of the select element for the required combo box value and returns the associated option index and use that as the selectedIndex.

For this code you need to set a reference to Microsoft HTML Object Library in Tools -> References in the VBA editor.

Replace your

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

with:

    Dim selectElement As HTMLSelectElement
    Dim optionIndex As Integer
    
    Set selectElement = IE.Document.getElementById("Control_Room_Operator")
    optionIndex = FindSelectOptionIndex(selectElement, ComboBox1.Value)
    If optionIndex >= 0 Then
        selectElement.selectedIndex = optionIndex
    Else
        MsgBox ComboBox1.Value & " not found in Control_Room_Operator dropdown"
    End If
    
    Set selectElement = IE.Document.getElementById("Analyst")
    optionIndex = FindSelectOptionIndex(selectElement, ComboBox2.Value)
    If optionIndex >= 0 Then
        selectElement.selectedIndex = optionIndex
    Else
        MsgBox ComboBox2.Value & " not found in Analyst dropdown"
    End If

and add this at the bottom of the module:

Private Function FindSelectOptionIndex(selectElement As HTMLSelectElement, findOptionText As String) As Integer

    Dim i As Integer
    
    FindSelectOptionIndex = -1
    i = 0
    While i < selectElement.Options.Length And FindSelectOptionIndex = -1
        Debug.Print i, selectElement.Item(i).Value & " >" & selectElement.Item(i).Text & "<"
        If LCase(selectElement.Item(i).Text) = LCase(findOptionText) Then FindSelectOptionIndex = i
        i = i + 1
    Wend

End Function

 

[Peter h]

Thank you. I'll mess around with it on Monday. You've been a great help.

 

[Peter h]

Hey, John_w, I was wondering if you could take a look at this other thread, since you helped me out with this. It's the same project, but different issue, so I thought I'd start a new thread.

http://www.mrexcel.com/forum/excel-questions/945360-need-some-help-doevents-loop-please.html

Thanks.