Easy way to pull a date from a Web Page?

[JoanneS]

Hello - I have an Excel spread that pulls in the latest silver prices from https://www.kitco.com/Silver-price-today-USA/ but I need to extract the date that the price was last updated like "Oct 16, 2020 16:59 NY Time" into my spread.  Is that possible?  The connection and refresh of the table was easy but don't see a way of extracting that important piece of info.  Thanks for any help!

[#2 · Accepted solution]

@JoanneS 

You may use this User Defined Function to get the desired info from the web page.

Place this UDF on a Standard Module like Module1.

Function getDate(url As String)
Dim ie As Object
Dim doc As Object
Dim eDate As Object

Set ie = CreateObject("InternetExplorer.Application")
With ie
    .navigate url
    .Visible = False
    Do While .readyState <> 4
        DoEvents
    Loop
    
End With

Set doc = ie.document
Set eDate = doc.getElementsByClassName("date")(0)
getDate = eDate.innerText
ie.Quit
Set ie = Nothing
End Function

And then you may use the above UDF like a regular Excel function on the worksheet like this...

In a blank cell, place the following function...

=getDate("https://www.kitco.com/Silver-price-today-USA/")

and this will return Oct 16, 2020 16:59 NY Time in the formula cell.

For more details, refer to the attached.

[#3]

@Subodh_Tiwari_sktneer Thank you for the detailed info...it worked great!!

[#4]

You're welcome @JoanneS! Glad it worked as desired.