VBA Post to webform trying to select Option element

Question

im trying to automate a webform that i have to fill out, the code is below. what i cant seem to figure out is how to get the optionbox to take the data that is on the excel cell.

Here is the optionbox content from the webpage. i basically put the words in Cell h, so it can say ontario and i want it to select Ontario from the drop down.

    <select name="companyProvince" style="width:204">
                
                    <option value="" disabled="" selected="">Select your state/province: </option>
            <optgroup label="Canada">
                    <option value="AB">
                        Alberta
                    </option>
                
                    <option value="BC">
                        British Columbia
                    </option>
                
                    <option value="MB">
                        Manitoba
                    </option>
                
                    <option value="NB">
                        New Brunswick
                    </option>
                
                    <option value="NL">
                        Newfoundland and Labrador
                    </option>
                
                    <option value="NT">
                        Northwest Territories
                    </option>
                
                    <option value="NS">
                        Nova Scotia
                    </option>
                
                    <option value="NU">
                        Nunavut
                    </option>
                
                    <option value="ON">
                        Ontario
                    </option>
                
                    <option value="PE">
                        Prince Edward Island
                    </option>


Option Explicit

Const sSiteName = "https://www.onlinemart.ca/ApplyAccount/default.asp?language_code=ENG&formType=insidesales"

Sub Button1_Click()
 Dim oIE As Object
    Dim oHDoc As HTMLDocument
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets("data")
    Dim LastRow As Long
    Dim i As Long
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    
    Set oIE = CreateObject("InternetExplorer.Application")
    
    ' Open Internet Explorer Browser and keep it visible.
    With oIE
        .Visible = True
        .Navigate sSiteName
    End With
    
    While oIE.ReadyState <> 4
        DoEvents
    Wend
 
    Set oHDoc = oIE.Document
   For i = 2 To LastRow
   
   If ActiveSheet.Range("A1").Value = "" Then Exit Sub
   
   
    With oHDoc
       .getElementById("ownershipName").Value = sht.Range("A" & i).Value
        .getElementById("companyPrimaryPhoneArea").Value = sht.Range("B" & i).Value
        .getElementById("companyPrimaryPhone").Value = sht.Range("C" & i).Value
        .getElementById("companyPrimaryContactEmail").Value = sht.Range("D" & i).Value
        .getElementById("companyName").Value = sht.Range("E" & i).Value
         .getElementById("companyAddress").Value = sht.Range("F" & i).Value
        .getElementById("companyCity").Value = sht.Range("G" & i).Value
        '***********************************************************************
         If sht.Range("H" & i).Value = "Newfoundland and Labrador" Then
        '.getElementById("companyProvince").Value = sht.Range("H" & i).Value
       ' .getElementById("companyProvince").Value = "Newfoundland and Labrador"
        .getElementsByName("companyProvince").Item(0).Value = "Newfoundland and Labrador"
                '.getElementsByName("companyProvince").Value = 5
    End If
        
        '***********************************************************************
       
        .getElementById("companyPostal").Value = sht.Range("I" & i).Value
           
        '**************** Country **************************
        If sht.Range("J" & i).Value = "CAD" Then
         .getElementsByName("accountCountry").Item(0).Checked = True
    Else
         .getElementsByName("accountCountry").Item(1).Checked = True
       End If
        
        '**************** GET CURRENCY **************************
        
        If sht.Range("K" & i).Value = "CAD" Then
         .getElementsByName("accountCurrency").Item(0).Checked = True
    Else
         .getElementsByName("accountCurrency").Item(1).Checked = True
       End If

         .getElementById("salesAgentName").Value = sht.Range("L" & i).Value
         .getElementById("salesAgentId").Value = sht.Range("M" & i).Value
         .getElementById("salesChannel").Value = sht.Range("N" & i).Value
      
        If sht.Range("O" & i).Value = "Payment Services" Then
         .getElementsByName("applyService").Item(0).Checked = True
    Else
         .getElementsByName("applyService").Item(1).Checked = True
       End If
       
       '*************************************************************
       If sht.Range("P" & i).Value = "Yes" Then
         .getElementsByName("paymentProfile").Item(0).Checked = True
    Else
         .getElementsByName("paymentProfile").Item(0).Checked = False
       End If
       
       '*************************************************************
       If sht.Range("Q" & i).Value = "Yes" Then
         .getElementsByName("recurringBilling").Item(0).Checked = True
    Else
          .getElementsByName("recurringBilling").Item(0).Checked = False
       End If
     
      '  .getElementById("btnSubmit").Click
    End With
Next i
MsgBox "Process 100% completed"
End Sub

Try something like this: https://stackoverflow.com/a/66551327/478884 — Tim Williams, Apr 29 '22 at 21:37
^^ But you will need the two letter province abbreviations either in H or in a lookup. That abbreviation is the option element value attribute. e.g. `oHDoc.querySelector("[name=companyProvince] [label=Canada] [value=ON]").selected = True`. Or with concatenating in cell value`oHDoc.querySelector("[name=companyProvince] [label=Canada] [value=" & addCellReferenceHere & "]").selected = True`. — QHarr, Apr 30 '22 at 05:25
The above restrict via `[label=Canada]` to Canadian territories. Remove if restriction not wanted. You'd then need to be sure the 2 letter abbreviations are unique across US states/Canadian territories. The abbreviations you input need to match what you see in the html for the option elements in the dropdown. — QHarr, Apr 30 '22 at 05:27

VBA Post to webform trying to select Option element

0 Answers0