Retrieve data from Website

I am studying Astrology.

I am trying to download some data from

After clicking the following

Astrological Ephemerides

it will go to another page

Once the “Click Once” is pressed, the data will be shown as 1 whole page in

If you were to refer to the attachment,

I only require the highlighted data to be registered in Excel Cell “B2” and subsequent entries below “B2” for my study but could not because of the javascript.

I am trying to collect Data from 1st July 1997 to 31st December 2022.

Should you have doubt if I have any knowledge of VBA, please refer to the following working code.

Thanking in Advance.

Sub Get_Web_Data()
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim price As Variant
Dim FstDate As Date
Dim TheDate  As String
Dim r As Long

'String of initial date
FstDate = CDate("1997-07-03")  '<<<<< Edit first date string if required or have entered via an input box?

    For r = 0 To 9400   ' r+2 will fill rows 2 to 9400
        TheDate = LCase(Format(FstDate + r, "yyyy%2Fmm%2Fdd"))
            website = "" & TheDate & "+10%3A30%3A00&jd=2476948.34564&img=-k0&sys=-Sf&eyes=0&imgsize=320&orb=-b0&lat=1%B017&ns=North&lon=103%B051&ew=East&hlat=90%B0&hns=North&hlon=0%B0&elements="
            Set request = CreateObject("MSXML2.XMLHTTP")
            request.Open "GET", website, False
            response = StrConv(request.responseBody, vbUnicode)
            html.body.innerHTML = response
            price = html.getElementsByTagName("pre")(0).innerText
            Cells(r + 2, "B") = price
    Next r
End Sub

Chaos astrology

You need to filter the data here. Instead of using responseBody, you could use responseXML and then extract just the pre element from the structure…if there is only one pre element in the results.


But my VBA worked on another website not for the one I am asking for.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.