GetElementsById shows Error

G

gurunathblessings

New Member
Joined
Sep 15, 2016
Messages
22
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.
 

Attachments

  • Error3.jpg
    Error3.jpg
    150.7 KB · Views: 27
  • Error3A.jpg
    Error3A.jpg
    144.3 KB · Views: 25

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
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
 
Upvote 0
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
Click to expand...
Thanks for the response.

I tried but still get the same error.

Thanks.
 

Attachments

  • Error3.jpg
    Error3.jpg
    148.2 KB · Views: 16
  • Error3A.jpg
    Error3A.jpg
    142.7 KB · Views: 16
Upvote 0
I tried but still get the same error
Click to expand...
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
 
Upvote 0
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.
 

Attachments

  • Error3B.jpg
    Error3B.jpg
    250.2 KB · Views: 12
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
Click to expand...
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.


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.
 

Attachments

  • solar system live.jpg
    solar system live.jpg
    232.1 KB · Views: 9
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

netspeedz
  • Solved
Extracting Data From Kitco.com [Using VBA]
Replies
6
Views
1K
netspeedz
netspeedz
M
  • Question Question
Trying to find VBA Code to call locally stored X509Certificate2 in to a Module
Replies
0
Views
326
mwdbActuary
M
G
  • Locked
  • Question Question
Optimized version shows error
Replies
1
Views
505
Fluff
Fluff
G
  • Locked
  • Solved
a way to simply
2
Replies
12
Views
1K
Fluff
Fluff
J
  • Solved
VBA: GetElementbyClassName
2 3
Replies
22
Views
2K
Anthony47
A

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top