Novice to VBA. Require Help

[SimonT]

I Require a VBA to download information from URL.

Currently, I have to key-in 3 information to obtain required information.

I Require information for 24 years from 1997 to 2021. I am in need of a VBA where I have to key in just one information to get information for 24 years.

I used the following VBA but it shows error as Run time error 91 (object variable or with block variable not set).

Public Sub Comets3()

    Dim cometCells As Range, cometCell As Range
    Dim inputDate As String, startDate As Date
   
    inputDate = InputBox("Enter start date")
    If inputDate = "" Then Exit Sub
    startDate = CDate(inputDate)
   
    With ActiveSheet
        Set cometCells = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
    End With
   
    For Each cometCell In cometCells
        Application.StatusBar = cometCell.Value
        Get_Latest_Details "https://in-the-sky.org/ephemeris.php?startday=" & Day(startDate) & "&startmonth=" & Month(startDate) & "&startyear=" & Year(startDate) & _
                           "&ird=1&irs=1&ima=1&iph=0&ias=0&iss=0&iob=1&interval=0&tz=0&format=csv&objtype=3&objpl=" & Escape(cometCell.Value) & "&objtxt=" & Escape(cometCell.Value), _
                           cometCell.Offset(0, 1)
        DoEvents
    Next
   
    Application.StatusBar = ""

    MsgBox "Finished"

End Sub


Private Sub Get_Latest_Details(URL As String, destinationCell As Range)

    Static httpReq As Object
    Dim HTMLdoc As Object
    Dim table As Object, dataRow As Object
    Dim i As Long
   
    If httpReq Is Nothing Then Set httpReq = CreateObject("MSXML2.XMLHTTP")
   
    With httpReq
        .Open "GET", URL, False
        .send
        If .Status = 200 Then
            Set HTMLdoc = CreateObject("HTMLfile")
            HTMLdoc.Open
            HTMLdoc.write .responseText
            HTMLdoc.Close
           
            Set table = HTMLdoc.getElementsByTagName("TABLE")(1)
            Set dataRow = table.Rows(3)
            For i = 0 To dataRow.Cells.Length - 1
                destinationCell.Offset(0, i).Value = dataRow.Cells(i).innerText
            Next
            Set HTMLdoc = Nothing
        Else
            destinationCell.Offset(0, 0).Value = "Error"
            destinationCell.Offset(0, 1).Value = URL
            destinationCell.Offset(0, 2).Value = .statusText
        End If
    End With
   
End Sub


Private Function Escape(ByVal param As String) As String

    Dim i As Integer, BadChars As String

    BadChars = "%<>=&!@#$^()+{[}]|\;:'"",/?"
    For i = 1 To Len(BadChars)
        param = Replace(param, Mid(BadChars, i, 1), "%" & Hex(Asc(Mid(BadChars, i, 1))))
    Next
    param = Replace(param, " ", "+")
    Escape = param

End Function

Is it possible to modify the VBA I am using to retrieve information of the following 3 URLs?

Thanking in Advance.


Link 1
1998 Pratipada tithi dates. Krishna & Shukla paksha Pratipada in 1998, next Pratipada date with exact start and end time.

Link 2
Hindu calendar November 2007 with festivals & daily panchang. Holidays, daily tithi, vrat, Hindu festival calendar November 2007.

Link 3
Find your Nakshatra. This Nakshatra Calculator helps you find your Janma Nakshatras. There are 27 nakshatras and nakshatra is an important aspect of Indian astrology

 

[vletm]

SimonT
As You have read Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

Could You explain shortly
How Your title match with below?

• Use Relevant words in the Title and in the tag Box, This will aid future searches.