SOLVED

Easy way to pull a date from a Web Page?

%3CLINGO-SUB%20id%3D%22lingo-sub-1792401%22%20slang%3D%22en-US%22%3EEasy%20way%20to%20pull%20a%20date%20from%20a%20Web%20Page%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792401%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20-%20I%20have%20an%20Excel%20spread%20that%20pulls%20in%20the%20latest%20silver%20prices%20from%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.kitco.com%2FSilver-price-today-USA%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.kitco.com%2FSilver-price-today-USA%2F%3C%2FA%3E%26nbsp%3Bbut%20I%20need%20to%20extract%20the%20date%20that%20the%20price%20was%20last%20updated%20like%20%22%3CSPAN%3EOct%2016%2C%202020%2016%3A59%20NY%20Time%22%20into%20my%20spread.%26nbsp%3B%20Is%20that%20possible%3F%26nbsp%3B%20The%20connection%20and%20refresh%20of%20the%20table%20was%20easy%20but%20don't%20see%20a%20way%20of%20extracting%20that%20important%20piece%20of%20info.%26nbsp%3B%20Thanks%20for%20any%20help!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1792401%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1792560%22%20slang%3D%22en-US%22%3ERe%3A%20Easy%20way%20to%20pull%20a%20date%20from%20a%20Web%20Page%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792560%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F836238%22%20target%3D%22_blank%22%3E%40JoanneS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20use%20this%20User%20Defined%20Function%20to%20get%20the%20desired%20info%20from%20the%20web%20page.%3C%2FP%3E%3CP%3EPlace%20this%20UDF%20on%20a%20Standard%20Module%20like%20Module1.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EFunction%20getDate(url%20As%20String)%0ADim%20ie%20As%20Object%0ADim%20doc%20As%20Object%0ADim%20eDate%20As%20Object%0A%0ASet%20ie%20%3D%20CreateObject(%22InternetExplorer.Application%22)%0AWith%20ie%0A%20%20%20%20.navigate%20url%0A%20%20%20%20.Visible%20%3D%20False%0A%20%20%20%20Do%20While%20.readyState%20%26lt%3B%26gt%3B%204%0A%20%20%20%20%20%20%20%20DoEvents%0A%20%20%20%20Loop%0A%20%20%20%20%0AEnd%20With%0A%0ASet%20doc%20%3D%20ie.document%0ASet%20eDate%20%3D%20doc.getElementsByClassName(%22date%22)(0)%0AgetDate%20%3D%20eDate.innerText%0Aie.Quit%0ASet%20ie%20%3D%20Nothing%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20then%20you%20may%20use%20the%20above%20UDF%20like%20a%20regular%20Excel%20function%20on%20the%20worksheet%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20a%20blank%20cell%2C%20place%20the%20following%20function...%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DgetDate(%22https%3A%2F%2Fwww.kitco.com%2FSilver-price-today-USA%2F%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20this%20will%20return%20%3CSTRONG%3EOct%2016%2C%202020%2016%3A59%20NY%20Time%3C%2FSTRONG%3E%20in%20the%20formula%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20more%20details%2C%20refer%20to%20the%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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!

3 Replies
Best Response confirmed by JoanneS (New Contributor)
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.

 

 

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

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