Forum Discussion

JoanneS's avatar
JoanneS
Copper Contributor
Oct 18, 2020

Easy way to pull a date from a Web Page?

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!

Excel
Excel for web
Formulas and Functions
Macros and VBA
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Oct 18, 2020

    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.

     

     

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Oct 18, 2020

    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.

     

     

Resources