GetElementsById shows Error

gurunathblessings · Apr 14, 2022

My Code

VBA Code:

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


website = "https://www.mooncalc.org/#/40.7146,-74.0071,9/2022.04.15/18:30/1/3"

Set request = CreateObject("MSXML2.XMLHTTP")

request.Open "GET", website, False

request.send

response = StrConv(request.responseBody, vbUnicode)

html.body.innerHTML = response

price = html.getElementsById("azimuth").Item(0).innerText

Cells(2, "B") = price

End Sub

Thanks.

Anthony47 · Apr 14, 2022

In a html code an ID is unique, the function that catch it is getElementById (not getElementSById) and returns a single IHTMLElement, not a collection.
That said, try

VBA Code:

price = html.getElementById("azimuth").innerText

gurunathblessings · Apr 14, 2022

Anthony47 said:
In a html code an ID is unique, the function that catch it is getElementById (not getElementSById) and returns a single IHTMLElement, not a collection.
That said, try

VBA Code:

price = html.getElementById("azimuth").innerText

Thanks for the response.

I tried but still get the same error.

Thanks.

Anthony47 · Apr 15, 2022

I tried but still get the same error

No, "Object doesn't support this property or method" (the previous error) is different from "Object variable or With block not set" (the current one)
This is the response you get from the site:

VBA Code:

<body>
<div class="page">
  <div class="main">
    <h1>Server Error</h1>
    <div class="error-code">404</div>
    <h2>Page Not Found</h2>
    <p class="lead">This page either doesn't exist, or it moved somewhere else.</p>
    <hr/>
    <p>That's what you can do</p>
    <div class="help-actions">
      <a href="javascript:location.reload();">Reload Page</a>
      <a href="javascript:history.back();">Back to Previous Page</a>
      <a href="/">Home Page</a>
    </div>
  </div>
</div>
</body>

It means your ID is not there, thus getElementByID has nothing to catch…

You may try catching the information via InternetExplorer (till it last), using the following code:

VBA Code:

Sub GetViaIE()
Dim IE As Object
'
Set IE = CreateObject("InternetExplorer.Application")
'
website = "https://www.mooncalc.org/#/40.7146,-74.0071,9/2022.04.15/18:30/1/3"
With IE
    .navigate website
    .Visible = True
    Do While .Busy: DoEvents: Loop    'wait not busy
    Do While .readyState <> 4: DoEvents: Loop 'wait document
End With
myStart = Timer  'extra wait
Do
    DoEvents
    If Timer > myStart + 1 Or Timer < myStart Then Exit Do
Loop
'
MoonAz = IE.document.getElementById("azimuth").innerText
MoonAlt = IE.document.getElementById("sunhoehe").innerText
'
Stop                         '<<< See message
'
IE.Quit
Set IE = Nothing
End Sub

There is a Stop that will interrupt the code after MoonAlt and MoonAz have been stored; I guess that you need to add in that position your own instructions to process the data before the macro ends

Bye

gurunathblessings · Apr 15, 2022

Hi,

Thanks.

Please refer to the attachment. I could find an ID which is looking different from what you have sent in your mail.

It is possible to use Google Chrome instead of IE? I have a difficulty of using IE.

Thanks.

Anthony47 · Apr 15, 2022

Which is the problem with IE?

What you show is how the page appears when created by a browser; what I attached is what the url responds to a xml request; you can read it by reading the content of "response".

To use Chrome you have to install Selenium basic and its chrome driver
Selenium basic can be downloaded from here: Releases · florentbr/SeleniumBasic
Chrome driver varies with Chrome version; check here: ChromeDriver - WebDriver for Chrome - Downloads

After the installation is complete download the following worksheet: SELENIUM_TEST.xlsm
There is a button "Importa Dati"; press it and check that a table of some 50 rows * 7 columns is filled without error

At that point your system is ready and I should have a running code for you.

Bye

Anthony47 · Apr 15, 2022

After installing the Selenium environment, you should be able to access the information using this macro:

VBA Code:

Sub MoonParam()
Dim WPage As Object, MoonAlt As String, MoonAz As String
'
Set WPage = CreateObject("Selenium.WebDriver")
'
website = "https://www.mooncalc.org/#/40.7146,-74.0071,9/2022.04.15/18:30/1/3"
WPage.Start "Chrome", website
WPage.Get "/"
'
MoonAz = WPage.FindElementById("azimuth").Text
MoonAlt = WPage.FindElementById("sunhoehe").Text
WPage.Quit
MsgBox ("Moon Altitude is " & MoonAlt & vbCrLf & "Moon Azimuth is " & MoonAz)
'
Stop    '<< Only for debug
'
End Sub

Bye

gurunathblessings · Apr 28, 2022

Anthony47 said:

After installing the Selenium environment, you should be able to access the information using this macro:

VBA Code:

Sub MoonParam()
Dim WPage As Object, MoonAlt As String, MoonAz As String
'
Set WPage = CreateObject("Selenium.WebDriver")
'
website = "https://www.mooncalc.org/#/40.7146,-74.0071,9/2022.04.15/18:30/1/3"
WPage.Start "Chrome", website
WPage.Get "/"
'
MoonAz = WPage.FindElementById("azimuth").Text
MoonAlt = WPage.FindElementById("sunhoehe").Text
WPage.Quit
MsgBox ("Moon Altitude is " & MoonAlt & vbCrLf & "Moon Azimuth is " & MoonAz)
'
Stop    '<< Only for debug
'
End Sub

Bye

Hi,

I tried to run your program but could not able to because I could not locate the location as where I should download the worksheet: SELENIUM_TEST.xlsm.

" After the installation is complete download the following worksheet: SELENIUM_TEST.xlsm
There is a button "Importa Dati"; press it and check that a table of some 50 rows * 7 columns is filled without error"

I tried another method.

VBA Code:

from selenium import webdriver
from openpyxl import load_workbook
import pandas as pd
from datetime import datetime
from time import sleep

file_path = 'C:\\Users\\suspa\\Desktop\\New folder (3)\\Book1.xlsx'

wb = load_workbook(file_path)

ws = wb.active

x = 'moon'

calander = pd.date_range(start="1997-07-03",end="2022-12-31")
calandercounter = 0

def formatter(a):
    if len(a) == 1:
        a = '0' + a
    return a

while calandercounter != 2:
    driver = webdriver.Chrome()
    a = str(calander[calandercounter].date().day)
    b = str(calander[calandercounter].date().month)
    c = str(calander[calandercounter].date().year)
    formatter(a)
    formatter(b)
    driver.get('https://www.' + x + 'calc.org/#/1.2904,103.8521,11/'+c+'.'+b+'.'+a+'/18:30/1/3')
    grab1 = driver.find_element_by_id('azimuth').text
    grab2 = driver.find_element_by_id('sunhoehe').text

    if x == 'moon':
        ws['D' + str(1+calandercounter)],ws['E' + str(1+calandercounter)] = grab2,grab1
        x = 'sun'
    else:
        ws['B' + str(1+calandercounter)],ws['C' + str(1+calandercounter)] = grab2,grab1
        x = 'moon'
        calandercounter += 1
    driver.quit()


wb.save(file_path)

I could get it work but nothing is registered in excel sheet.

Anyway, I have found a better website where they have all the data I require.

Solar System Live

www.fourmilab.ch

Unfortunately,

I could not get it work. I need to get the entire highlighted information into Cell "B2" and the subsequent follow below it.

Thanks.

Anthony47 · Apr 28, 2022

The test workbook mentioned in my post #6 was intended to let you test the selenium environment after its installation, because at that time I didn't had a working macro for the moon parametres.
The macro mentioned in post #7 to be integrated in your workbook should import your moon parametres into your workbook. Beware that the macro loads the parametres in the variables MoonAz e MoonAlt and rise a message box; if you want the data stored into excel you have to add the storing instrunctions, something like

VBA Code:

Range("B2").Value = MoonAz
Range("B3").Value = MoonAlt

You also have the macro that works vie IE, see post #4

Bye

GetElementsById shows Error

gurunathblessings

New Member

Attachments

Excel Facts

Anthony47

Well-known Member

gurunathblessings

New Member

Attachments

Anthony47

Well-known Member

gurunathblessings

New Member

Attachments

Anthony47

Well-known Member

Anthony47

Well-known Member

gurunathblessings

New Member

Solar System Live

Attachments

Anthony47

Well-known Member

Similar threads

Forum statistics

Share this page

GetElementsById shows Error

New Member

Attachments

Excel Facts

Well-known Member

New Member

Attachments

Well-known Member

New Member

Attachments

Well-known Member

Well-known Member

New Member

Attachments

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock