VBA Automation - How To Access Date Picker Elements

user_12167866 0
Viewed 635

I am working on some VBA Automation where it is taking data from an Excel Spreadsheet, and inputting that data into an Intranet Site.

On the Intranet Site there is a little datepicker tool (with a start date and end date) that allows you to pick the date (please see html excerpt of the datepicker html code):

<input type="text" id="startdate" name="startdate" value="" class="form-control" size="30">    
<button class="pika-button pika-day" type="button" data-pika-year="2019" data-pika-month="8" data-pika-day="2">2</button>
<button class="pika-prev" type="button">Previous Month</button>

<input type="text" id="enddate" name="enddate" value="" class="form-control" size="30">
<button class="pika-button pika-day" type="button" data-pika-year="2019" data-pika-month="8" data-pika-day="2">2</button>
<button class="pika-prev" type="button">Previous Month</button>

Please also see the JavaScript code below for the datepicker function:

<script>
    var startDate,
    endDate,
    updateStartDate = function() {
        startPicker.setStartRange(startDate);
        endPicker.setStartRange(startDate);
        endPicker.setMinDate(startDate);
    },
    updateEndDate = function() {
        startPicker.setEndRange(endDate);
        startPicker.setMaxDate(endDate);
        endPicker.setEndRange(endDate);
    },
    startPicker = new Pikaday({
        field: document.getElementById('startdate'),
        minDate: new Date(2016, 1, 1),
        maxDate: new Date(2050, 12, 31),
        onSelect: function() {
            startDate = this.getDate();
            updateStartDate();
        }
    }),
    endPicker = new Pikaday({
        field: document.getElementById('enddate'),
        minDate: new Date(2016, 1, 1),
        maxDate: new Date(2050, 12, 31),
        onSelect: function() {
            endDate = this.getDate();
            updateEndDate();
        }
    });
    _startDate = startPicker.getDate();
    _endDate = endPicker.getDate();
    if (_startDate) {
        startDate = _startDate;
        updateStartDate();
    };
    if (_endDate) {
        endDate = _endDate;
        updateEndDate();
    };
</script>

I originally had this code which pushes the correct date string to the form, but the datepicker elements inside the datepicker don't get selected (they default to today's date) after running the automation (see code below:).

IE.document.all("startdate").Value = Format$(ThisWorkbook.Sheets("Legends").Range("b7").Value, "yyyy-mm-dd")
IE.document.all("enddate").Value = Format$(ThisWorkbook.Sheets("Legends").Range("b8").Value, "yyyy-mm-dd")

This is what happens after I run the automation, please see image with the date pushed: Intranet site with date

This is how it looks when I click on the datepicker element: Datepicker defaulting to today's date

After reading some some stuff online, it seems like you have to click on the "startdate" element, and then click on the other elements inside the datepicker (which I have successfully done below:).

IE.document.querySelector("startdate").Click

However I am unable to figure out how to click on the other elements (previous month, and the day) to complete this part of the automation.

Any help would be much appreciated!

datepicker internet-explorer vba automation
1 Answers

You could try to use the SendKeys method to enter the date, please refer to the following code:

Sub Test()
    Dim IE As Object

    Dim startDateText As Object, endDateText As Object

    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "<the website url>"

        While IE.ReadyState <> 4
            DoEvents
        Wend

        'find the startdate textbox and focus
        Set startDateText = IE.document.all("startdate")
        startDateText.Focus
        'using sendkeys method to enter the date.
        SendKeys Format$(ThisWorkbook.Sheets("Legends").Range("b7").Value, "yyyy-mm-dd")
        'Waiting the date value enters (1 second)
        Application.Wait (Now + TimeValue("0:00:1"))

        Set endDateText = IE.document.all("enddate")
        endDateText.Focus
        SendKeys Format$(ThisWorkbook.Sheets("Legends").Range("b8").Value, "yyyy-mm-dd")

        Application.Wait (Now + TimeValue("0:00:1"))
        IE.document.all("startdate").Click

    End With
    Set IE = Nothing
End Sub
user_70622910
Related