none
EXCEL VBA - When Changing the Option of Drop down via VBA, Next drop down is not displayed RRS feed

  • Question

  • Hello People,

    I am trying to scrape a website, I am successful to some extent, I can easily select the option for SELECT (.getElementById("wb-auto-25")) and changed selected index but the problem is there needs to be a next dropdown appearing after changing the values in the first dropdown but this is not happening via VBA. While working from browser the same works when selected a value from Temporary Residence (visiting, studying, working) from the first drop down the second drop down " <label class="required" for="wb-auto-51">Which temporary residence application?(required)" </label>appears. Copied the steps and also the code below, please could you help how to make the second drop appear. I will do the same coding for other dropsdowns.

    The report would involve the following steps:
    1.  Going to https://www.canada.ca/en/immigration-refugees-citizenship/services/application/check-processing-times.html
    2.  Selecting Temporary Residence (visiting, studying, working) from the first drop down menu
    3.  Selecting Work Permit from the second drop down menu (generated after making selection in first drop down)
    4.  Selecting Country Name ‘Afghanistan’ in Where are you applying from drop down menu
    5.  Clicking on ‘Get processing time’ button
    6.  Copying Number of weeks displayed in the panel to the right
    7.  Copying the Last Update Date in panel below
    8.  Select next country name after Afghanistan in the Where are you applying from drop down
    9.  Copying Number of weeks displayed in the panel to the right
    10. Copying the date in Last Update in panel below
    11. Repeat until reaching Zimbabwe
    12. The final result would be an Excel Spreadsheet with the following 5 columns: Country, Application Category (= Temporary Residence (visiting, studying, working), Application Type (= Work Permit), Processing Time (= X weeks), Last Update (= Date)   

    Sub Wait(ie)
      #If VBA7 Then
        #Else
            Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
        #End If
    
        Sub Wait(ie)
          Do
            Sleep 500
          Loop While ie.readyState < 4 And ie.Busy
          Do
            Sleep 500
          Loop While ie.readyState < 4 And ie.Busy
        End Sub
    
    Public Sub SearchImmigProcessingTimes()
    Dim ie As InternetExplorer
    Dim html As HTMLDocument
    Dim Link As Object
    Dim ElementCol As Object
    Dim erow As Long
    Dim ObjA As Object
    Dim Allinks As Object
    Dim HyperLink As Object
    Dim LastRow As Integer
    Dim mySearchURL As String
    Dim boolEmailIdGot As Boolean
    Dim cn, sConString, rst, sSQL, sLoginURL, sLogin, sPassword
    
            mySearchURL = "https://www.canada.ca/en/immigration-refugees-citizenship/services/application/check-processing-times.html"
            Set ie = New InternetExplorer
            ie.Visible = True
    
    
            ' Navigate to Immigration Processing Times URL
            ie.navigate mySearchURL
            Do While ie.readyState <> READYSTATE_COMPLETE
                Application.StatusBar = "Loading website…"
                DoEvents
            Loop
    
            If mySearchURL <> "" Then
    
                Set html = ie.document
                With html
    
                    ' Selecting Temporary Residence (visiting, studying, working) from the first drop down menu
    
            Do While ie.readyState <> READYSTATE_COMPLETE
                Application.StatusBar = "Loading website…"
                DoEvents
            Loop
                    Wait ie
    
                    For Each objoption In .getElementById("wb-auto-25").Options
                    'For Each objoption In .getElementByName("wb-fieldflowwb-auto-1wb-auto-25").Options
                    'MsgBox Trim(objoption.innerText)
                        If LCase(Trim(objoption.innerText)) = LCase("Temporary Residence (visiting, studying, working)") Then
                            'objoption.Selected = True
                            'objoption.Value = objoption.innerText
                            '.getElementById("wb-auto-25").selectedIndex = objoption.Index
                            .getElementById("wb-auto-25").Value = objoption.Value
                            .getElementById("wb-auto-25").Focus
                            .getElementById("wb-auto-25").FireEvent "onchange"
                            .getElementById("wb-auto-25").Click
                            SendKeys "{Tab}"
                            Exit For
                        End If
                        'MsgBox objoption.Index
                    Next
    
    
                End With
            End If
    End Sub

                                                                            
    Wednesday, June 12, 2019 11:50 AM

All replies

  • I suggest you use SeleniumBasic.  It is dated but works with IE and Chrome.  I only did a sample of your problem.  I use xpath to select elements.  Xpath has a lot of flexibility.

    Dim drv As Selenium.WebDriver
    Dim ele As Selenium.WebElement
    Dim eleSel As Selenium.SelectElement
    
    Sub SelectSet()
       '' Add reference to selenium type library
      Set drv = New Selenium.IEDriver
      Dim xpath As String
      Dim browser As String
    
      drv.Get "https://www.canada.ca/en/immigration-refugees-citizenship/services/application/check-processing-times.html"
      drv.Wait (3000)
      xpath = "//select[@id='wb-auto-25']"
      If GetElementSelect(xpath) Then
        eleSel.SelectByText "Temporary residence (visiting, studying, working)"
      End If
    End Sub
    
    Function GetElementSelect(xpath As String) As Boolean
      On Error GoTo Handler
      
      Set eleSel = drv.FindElementByXPath(xpath).AsSelect
      GetElementSelect = True
      Exit Function
    Handler:
      Err.Clear
      GetElementSelect = False
    End Function
    

    Wednesday, June 12, 2019 8:41 PM
  • Thanks Mogul Man I tried selenium but I get Overflow error at this line

    drv.Get "https://www.canada.ca/en/immigration-refugees-citizenship/services/application/check-processing-times.html", 10

    

    Thursday, June 13, 2019 8:56 AM
  • In order to debug use my exact example.  Add reference to 'selenium type library'.  Tell me what you get.
    Thursday, June 13, 2019 12:08 PM