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).
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
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).
Code:
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
Last edited by a moderator: