0

I am trying to leverage Selenium to automate standardized web inputs; however, the remaining piece is handling the calendar popup. I am needing to dynamically set the values of the calendar to the relative cell value in the sheet.

I am using my windows computer- Excel-VBA; using Selenium Webdriver and Chromedriver. I've tried using both Xpaths-Method, ExecuteScript (JS), and controlling mouse. 

Sub googStart()

Dim bot As New WebDriver
Dim rng As Range
Dim ele As WebElement

' 
lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row


bot.Start "chrome", "URL HERE"
bot.Get "/"

For X = 2 To lastRow
   Set ele = bot.FindElementById("calendarId").AsSelect
   ele.SelectByValue "30"
' Above threw an runt time error 59

bot.FindElementById("calendarId").Click
' this opens up the calender's pop up 

bot.Wait 500

Also, tRied the below combinations: 


' cScript = "document.getElementsByClass('a-cal-days').value='29'"

' bot.ExecuteScript cScript


' For Each ele In bot.FindElementsByXPath("//select[@class='a-cal-select-day[]']")

' ele.AsSelect = Sheet1.Cells(X, 4)

' bot.FindElementById("calendarInputId").Value ("10/29/2019")

' bot.FindElementByXPath("(//[@class='a-cal-d'])").Text Sheet1.Cells(X, 5)

' bot.findElement(By.xpath("//input[@id='calendarId']/div[1]/select[2])).setAttribute('value', '24')

' //*[@id="calendarId"]/div[1]/select[1] ' Month

' //*[@id='calendarId']/div[1]/select[2] ' day select

' //*[@id="calendarId"]/div[1]/select[2]

Cells Values; needing to match calendar day and month: See attached image! HTML BELOW: 

<td>
  <div id="calendarId" data-a-cal-attributes="{&quot;minDate&quot;:{&quot;month&quot;:10,&quot;year&quot;:2019,&quot;day&quot;:23},&quot;maxDate&quot;:{&quot;month&quot;:10,&quot;year&quot;:2020,&quot;day&quot;:24},&quot;selectedDate&quot;:{&quot;month&quot;:10,&quot;year&quot;:2019,&quot;day&quot;:24}}"
    data-a-cal-input="true" class="a-cal-calendar-container a-cal-static">
    <div class="a-cal-dropdown-container">
      <select data-action="a-cal-dropdown-select" class="a-cal-select-month a-declarative">
        <option value="">Month</option>
        <option value="1">1</option>
        <option value="2">2</option>
        <option value="3">3</option>
        <option value="4">4</option>
        <option value="5">5</option>
        <option value="6">6</option>
        <option value="7">7</option>
        <option value="8">8</option>
        <option value="9">9</option>
        <option value="10">10</option>
        <option value="11">11</option>
        <option value="12">12</option>
      </select>
      <select data-action="a-cal-dropdown-select" class="a-cal-select-day a-declarative">
        <option value="">Day</option>
        <option value="1">1</option>
        <option value="2">2</option>
        <option value="3">3</option>
        <option value="4">4</option>
        <option value="5">5</option>
        <option value="6">6</option>
        <option value="7">7</option>
        <option value="8">8</option>
        <option value="9">9</option>
        <option value="10">10</option>
        <option value="11">11</option>
        <option value="12">12</option>
        <option value="13">13</option>
        <option value="14">14</option>
        <option value="15">15</option>
        <option value="16">16</option>
        <option value="17">17</option>
        <option value="18">18</option>
        <option value="19">19</option>
        <option value="20">20</option>
        <option value="21">21</option>
        <option value="22">22</option>
        <option value="23">23</option>
        <option value="24">24</option>
        <option value="25">25</option>
        <option value="26">26</option>
        <option value="27">27</option>
        <option value="28">28</option>
        <option value="29">29</option>
        <option value="30">30</option>
        <option value="31">31</option>
      </select>
      <select data-action="a-cal-dropdown-select" class="a-cal-select-year a-declarative">
        <option value="">Year</option>
        <option value="1900">1900</option>
        <option value="1901">1901</option>
        <option value="1902">1902</option>
        <option value="1903">1903</option>
        <option value="1904">1904</option>
        <option value="1905">1905</option>
        <option value="1906">1906</option>
        <option value="1907">1907</option>
        <option value="1908">1908</option>
        <option value="1909">1909</option>
        <option value="1910">1910</option>
        <option value="1911">1911</option>
        <option value="1912">1912</option>
        <option value="1913">1913</option>
        <option value="1914">1914</option>
        <option value="1915">1915</option>
        <option value="1916">1916</option>
        <option value="1917">1917</option>
        <option value="1918">1918</option>
        <option value="1919">1919</option>
        <option value="1920">1920</option>
        <option value="1921">1921</option>
        <option value="1922">1922</option>
        <option value="1923">1923</option>
        <option value="1924">1924</option>
        <option value="1925">1925</option>
        <option value="1926">1926</option>
        <option value="1927">1927</option>
        <option value="1928">1928</option>
        <option value="1929">1929</option>
        <option value="1930">1930</option>
        <option value="1931">1931</option>
        <option value="1932">1932</option>
        <option value="1933">1933</option>
        <option value="1934">1934</option>
        <option value="1935">1935</option>
        <option value="1936">1936</option>
        <option value="1937">1937</option>
        <option value="1938">1938</option>
        <option value="1939">1939</option>
        <option value="1940">1940</option>
        <option value="1941">1941</option>
        <option value="1942">1942</option>
        <option value="1943">1943</option>
        <option value="1944">1944</option>
        <option value="1945">1945</option>
        <option value="1946">1946</option>
        <option value="1947">1947</option>
        <option value="1948">1948</option>
        <option value="1949">1949</option>
        <option value="1950">1950</option>
        <option value="1951">1951</option>
        <option value="1952">1952</option>
        <option value="1953">1953</option>
        <option value="1954">1954</option>
        <option value="1955">1955</option>
        <option value="1956">1956</option>
        <option value="1957">1957</option>
        <option value="1958">1958</option>
        <option value="1959">1959</option>
        <option value="1960">1960</option>
        <option value="1961">1961</option>
        <option value="1962">1962</option>
        <option value="1963">1963</option>
        <option value="1964">1964</option>
        <option value="1965">1965</option>
        <option value="1966">1966</option>
        <option value="1967">1967</option>
        <option value="1968">1968</option>
        <option value="1969">1969</option>
        <option value="1970">1970</option>
        <option value="1971">1971</option>
        <option value="1972">1972</option>
        <option value="1973">1973</option>
        <option value="1974">1974</option>
        <option value="1975">1975</option>
        <option value="1976">1976</option>
        <option value="1977">1977</option>
        <option value="1978">1978</option>
        <option value="1979">1979</option>
        <option value="1980">1980</option>
        <option value="1981">1981</option>
        <option value="1982">1982</option>
        <option value="1983">1983</option>
        <option value="1984">1984</option>
        <option value="1985">1985</option>
        <option value="1986">1986</option>
        <option value="1987">1987</option>
        <option value="1988">1988</option>
        <option value="1989">1989</option>
        <option value="1990">1990</option>
        <option value="1991">1991</option>
        <option value="1992">1992</option>
        <option value="1993">1993</option>
        <option value="1994">1994</option>
        <option value="1995">1995</option>
        <option value="1996">1996</option>
        <option value="1997">1997</option>
        <option value="1998">1998</option>
        <option value="1999">1999</option>
        <option value="2000">2000</option>
        <option value="2001">2001</option>
        <option value="2002">2002</option>
        <option value="2003">2003</option>
        <option value="2004">2004</option>
        <option value="2005">2005</option>
        <option value="2006">2006</option>
        <option value="2007">2007</option>
        <option value="2008">2008</option>
        <option value="2009">2009</option>
        <option value="2010">2010</option>
        <option value="2011">2011</option>
        <option value="2012">2012</option>
        <option value="2013">2013</option>
        <option value="2014">2014</option>
        <option value="2015">2015</option>
        <option value="2016">2016</option>
        <option value="2017">2017</option>
        <option value="2018">2018</option>
        <option value="2019">2019</option>
        <option value="2020">2020</option>
        <option value="2021">2021</option>
        <option value="2022">2022</option>
        <option value="2023">2023</option>
        <option value="2024">2024</option>
        <option value="2025">2025</option>
        <option value="2026">2026</option>
        <option value="2027">2027</option>
        <option value="2028">2028</option>
        <option value="2029">2029</option>
        <option value="2030">2030</option>
        <option value="2031">2031</option>
        <option value="2032">2032</option>
        <option value="2033">2033</option>
        <option value="2034">2034</option>
        <option value="2035">2035</option>
        <option value="2036">2036</option>
        <option value="2037">2037</option>
        <option value="2038">2038</option>
        <option value="2039">2039</option>
      </select>
    </div>
    <div class="a-cal-date-range-group a-cal-date-range-group-vertical">
      <div class="a-cal-date-range-wrapper">
        <div class="a-input-text-addon-group-wrapper">
          <div class="a-input-text-addon-group a-width-medium"><span class="a-input-text-addon"><i class="a-icon a-icon-calendar"></i></span><input type="text" maxlength="10" data-action="a-cal-input" data-input="start" class="a-input-text a-cal-input a-declarative" id="calendarInputId" /></div>
        </div>
      </div>
    </div>
  </div>
</td>
nbk
  • 45,398
  • 8
  • 30
  • 47
JustRush
  • 1
  • 1

1 Answers1

0

You need to ensure that it is the select element which serves as the base. Also, I don't think you can grab the window handle of pop-up and use that to switch to pop-up but you could try bot.SwitchToNextWindow. You return to original with bot.SwitchToDefaultContent.

E.g.

bot.SwitchToNextWindow

Dim monthSel As Object, daySel As Object

Set monthSel = bot.FindElementByCss(".a-cal-select-month").AsSelect
Set daySel = bot.FindElementByCss(".a-cal-select-day").AsSelect
Set yearSel = bot.FindElementByCss(".a-cal-select-year").AsSelect

Then I would expect you to be able to leverage SelectBy methods e.g.

                            ''<== assuming cell contains value 1
monthSel.SelectByText "10"   'or  monthSel.SelectByText ActiveSheet.Cells(2,1).value

daySel.SelectByValue "29"   
yearSel.SelectByValue 2019   'different ways of doing same thing shown

Be careful with your current lastRow calculation and looping method. The AsSelect can be done outside any loops as you don't need to set new references (at least I don't think so); you only want to alter the currently selected value in the dropdowns.

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • Thanks; so I tried the below code: 'Sub googStart() Dim bot As New WebDriver Dim rng As Range Dim ele As WebElement Dim monthSel As Object Dim daySel As Object lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row For X = 2 To lastRow – JustRush Oct 25 '19 at 21:49
  • 'Set monthSel = bot.FindElementByCss(".a-cal-select-month").AsSelect Set daySel = bot.FindElementByCss(".a-cal-select-day").AsSelect Set yearSel = bot.FindElementByCss(".a-cal-select-year").AsSelect bot.FindElementById("calendarInputId").Click monthSel.SelectByValue "10" ' Sheet1.Cells(X, 5).Value daySel.SelectByValue "31" 'Sheet1.Cells(X, 6).Value yearSel.SelectByValue 2019 bot.Wait 500' – JustRush Oct 25 '19 at 21:52
  • but.. it gave me a run time error '11' ElementNotVisibleError; element not intractable: Element is not currently visible and may not be manipulated. – JustRush Oct 25 '19 at 21:55
  • Should also note- the colander box is not a drop down, it’s a pop up box – JustRush Oct 26 '19 at 00:56
  • You need to switch to that window. Normal way (at least in other languages is https://stackoverflow.com/a/29052586/6241235) but could try as shown in my edit to answer above if vba doesn't implement the gethandles. – QHarr Oct 26 '19 at 06:18