VBA code to Select Date from Calendar on Webpage

Question

I wanted to download the excel from fallowing link by selecting date https://www.mcxindia.com/market-operations/clearing-settlement/daily-margin.

I had tried fallowing VBA Codes

Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate "https://www.mcxindia.com/market-operations/clearing-settlement/daily-margin"
Loop While ie.ReadyState <> 4 Or ie.Busy = True
ie.document.getelementbyid("txtDate").Value = "14/02/2022"
ie.document.getelementbyid("txtDate").Focus
ie.document.getelementbyid("txtDate").Selected = True
ie.document.getelementbyid("txtDate").Click

please clarify my mistake which help me to select date and download csv file

Code insert the date but its not getting selected as we select using mouse..so Show button not showing result — Ashish Patel, Feb 15 '22 at 12:45
Hello, you need to select date in frame windows, after date select on window, the date is accepted and forward, and Show work. Try and discovery Selenium driver for chrome to more options work to web objects. — Julio Gadioli Soares, Feb 15 '22 at 15:22

Julio Gadioli Soares · Accepted Answer · 2022-02-17T17:54:02.890

Holla Ashish, I will help you because at least you tried, to get more control of the objects and do what you need, I use the Selenium Driver to use in Google Chrome, you will need to download the driver according to your chrome version from the website: https:/ /chromedriver.chromium.org/downloads. And the Selenium Driver that makes it work... install the exe. SeleniumBasic v2.0.9.0: https://github.com/florentbr/SeleniumBasic/releases/tag/v2.0.9.0

For use 'Selenium.ChromeDriver' goto: Tools > Reference ... add ... in the folder where you installed the Selenium Driver, also place the file chromedriver.exe point to the file: 'Selenium64.tlb' or 32.

I needed to find the day to avoid many errors and other errors may appear, especially if wait is not the time necessary for the object to be available, use debug.print and debug if there are problems. Don't forget to put the chromedrive file in the selenium installed folder, and check your folder 'C:\Users\Your_User\AppData\Local\SeleniumBasic' it usually installs there.

if it helped answer.

Option Explicit
Private ch As Selenium.ChromeDriver

Public Function GET_DailyMargin()
On Error GoTo Err_Control
Dim FindBy As New Selenium.By
Dim DtDate As Date
Dim WeekOfMonth As Long
Dim DayOfMonth As Long
Dim DayOfFirst As Integer
Dim StartOfWeek2 As Integer
Dim weekNum As Integer

DtDate = Date

'DtDate = "08/02/2022"

If Weekday(DtDate) = 1 Then 'vbSunday
    DayOfMonth = 1
ElseIf Weekday(DtDate) = 2 Then 'vbMonday
    DayOfMonth = 2
ElseIf Weekday(DtDate) = 3 Then 'vbTuesday
    DayOfMonth = 3
ElseIf Weekday(DtDate) = 4 Then 'vbWednesday
    DayOfMonth = 4
ElseIf Weekday(DtDate) = 5 Then 'vbThursday
    DayOfMonth = 5
ElseIf Weekday(DtDate) = 6 Then 'vbFriday
    DayOfMonth = 6
ElseIf Weekday(DtDate) = 7 Then 'vbSaturday
    DayOfMonth = 7
End If
''Discovery week of mounth
''Credits: -> https://stackoverflow.com/questions/21690077/get-the-number-of-the-week-of-the-month-from-a-given-date
DayOfFirst = Weekday(DateSerial(Year(DtDate), Month(DtDate), 1), vbSunday)
StartOfWeek2 = (7 - DayOfFirst) + 2
    Select Case DtDate
        Case DateSerial(Year(DtDate), Month(DtDate), 1) _
        To DateSerial(Year(DtDate), Month(DtDate), StartOfWeek2 - 1)
            WeekOfMonth = 1
        Case DateSerial(Year(DtDate), Month(DtDate), StartOfWeek2) _
        To DateSerial(Year(DtDate), Month(DtDate), StartOfWeek2 + 6)
            WeekOfMonth = 2
        Case DateSerial(Year(DtDate), Month(DtDate), StartOfWeek2 + 7) _
        To DateSerial(Year(DtDate), Month(DtDate), StartOfWeek2 + 13)
            WeekOfMonth = 3
        Case DateSerial(Year(DtDate), Month(DtDate), StartOfWeek2 + 14) _
        To DateSerial(Year(DtDate), Month(DtDate), StartOfWeek2 + 20)
            WeekOfMonth = 4
        Case DateSerial(Year(DtDate), Month(DtDate), StartOfWeek2 + 21) _
        To DateSerial(Year(DtDate), Month(DtDate), StartOfWeek2 + 27)
            WeekOfMonth = 5
        Case DateSerial(Year(DtDate), Month(DtDate), StartOfWeek2 + 28) _
        To DateSerial(Year(DtDate), Month(DtDate) + 1, 1)
            WeekOfMonth = 6
    End Select

Set ch = New Selenium.ChromeDriver 'if comment this line to ch.Get, not open again, discomment DtDate here and you get any date without having to reopen the browser
ch.SetPreference "plugins.always_open_pdf_externally", True
ch.SetPreference "download.prompt_for_download", False
ch.SetPreference "browser.download.folderList", 2
ch.Start
ch.Get "https://www.mcxindia.com/market-operations/clearing-settlement/daily-margin"

ch.Refresh
ch.Window.Maximize
ch.FindElementById("txtDate").SendKeys DtDate
ch.FindElementById("txtDate").Click
ch.Wait (1000)
    If Format((ch.FindElement(FindBy.XPath("/html/body/div[2]/div/div[2]/div/table/tbody/tr[" & WeekOfMonth & "]/td[" & DayOfMonth & "]/a")).Text), "00") = Format(DtDate, "dd") Then
        ch.FindElement(FindBy.XPath("/html/body/div[2]/div/div[2]/div/table/tbody/tr[" & WeekOfMonth & "]/td[" & DayOfMonth & "]/a")).Click
        ch.FindElementById("btnShow").Click
    Else
        Debug.Print ch.FindElement(FindBy.XPath("/html/body/div[2]/div/div[2]/div/table/tbody/tr[" & WeekOfMonth & "]/td[" & DayOfMonth & "]/a")).Text
        MsgBox "Check the date, or web page updates", vbOKOnly, "Error"
    End If

ch.Wait (3000)
ch.FindElementById("cph_InnerContainerRight_C001_lnkExportToCSV").Click
ch.Wait (3000)
ch.Close
    
Err_Control:
If Err.Number <> 0 Then
    If Err.Number = 7 Then
        Resume Next
        Err.Clear
    ElseIf Err.Number = 11 Then
        MsgBox "download not available, verify"
        Err.Clear
        Stop
        Exit Function
    End If
    MsgBox Err.Description
End If
End Function

Lookup update, if error verify time of ch.wait. remove comments. — Julio Gadioli Soares, Feb 17 '22 at 15:17

VBA code to Select Date from Calendar on Webpage

1 Answers1