Using Excel Macro to extract specific text from website based on li id

I'm trying to write Excel macro that extracts specific text from a website based on the html structure.

Here is part of the website's html

                       <div id="footer" role="contentinfo">
            <ul id="footer-info">
                             <li id="footer-info-lastmod"> This page was last modified on 28 July 2014, at 19:44.</li>

I only need the text associated with li id "footer-info-lastmod".
Richard DetschChief Knowledge OfficerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LajuanTaylorCommented:
Take a look at the fully functional 30 day trial of WebReplay. It's an IE Plugin and it's pretty straight forward to use.
http://www.iewatch.com/downloadwr.aspx

I use this product daily with a SaaS provider that doesn't have an API for me to export my data. The data I get daily is Excel. I simulate logging into the website to get what I need...

The tool handles sites that use AJAX and sessions variables... You can screen scrape elements and load them into Excel if necessary.
0
Richard DetschChief Knowledge OfficerAuthor Commented:
Lijuan,
Thanks for product reference, but I was really looking for a solution in Excel visual basic code.
0
LajuanTaylorCommented:
No problem.

So I assume you have a button in your Excel document that you will push to trigger the Excel VBA that will perform an http request to a remote site?

The returned html would then be loaded into a variable, parsed, and the specific piece of text extracted... Is that what you are looking to do?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Richard DetschChief Knowledge OfficerAuthor Commented:
It is.
0
LajuanTaylorCommented:
Okay, you will need to make a VBA HTTP request from within Excel first:
https://www.google.com/?gws_rd=ssl#q=vba+http+get+request+excel

Sub http()
Dim MyRequest As Object
    Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    MyRequest.Open "GET", _
    "http://www.google.com"
   
 ' Send Request.
    MyRequest.Send

    'And we get this response
    MsgBox MyRequest.ResponseText
End Sub
0
LajuanTaylorCommented:
Your request should be "get" because you are not posting anything to a remote site. You will have to manipulate the returned html to extract what you.

I find that writing the data to disk first works better than trying to parse and extract in-memory... You will have to experiment.
0
Richard DetschChief Knowledge OfficerAuthor Commented:
That's working to get entire text from open websites.
The ones I scraping are behind my firewall and need a CAC login.

I was playing around before and was able to get those to open with excel, but had to type in my CAC number.

This code does allow me to enter my CAC number and get to the site:
           'from https://www.youtube.com/watch?v=7sZRcaaAVbg

           Dim IE As New InternetExplorer
           IE.Visible = True

           IE.Navigate "https://wiki.erdc.dren.mil/ERDCpedia"

             Do
                DoEvents
                 Loop Until IE.ReadyState = READYSTATE_COMPLETE
0
LajuanTaylorCommented:
For security reasons you might have to password protect the VB module and use a service account...

The following has not been tested, but this can be the basis for your login form input:

Dim IE As Object

Sub submitLoginInfo()
    Application.ScreenUpdating = False

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.Navigate "https://wiki.erdc.dren.mil/ERDCpedia"

    Application.StatusBar = "Submitting"
    ' Wait while IE loading...
    While IE.Busy
        DoEvents
    Wend
    ' **********************************************************************
    ' You can adjust the delay to suite your needs...
    delay 5
    IE.Document.getElementById("YourUserName_FormIdHere").Value = "username"
    delay 5
    IE.Document.getElementById("YourPassword_FormIdHere").Value = "password"
    delay 5
    IE.Document.getElementById("YourSubmitButton_FormIdHere").Click
    '**********************************************************************
    Application.StatusBar = "Form Submitted"
    IE.Quit
    Set IE = Nothing

    Application.ScreenUpdating = True
End Sub

' **********************************************************************
' Delay Subroutine
' **********************************************************************
Private Sub delay(seconds As Long)
    Dim endTime As Date
    endTime = DateAdd("s", seconds, Now())
    Do While Now() < endTime
        DoEvents
    Loop
End Sub
0
Richard DetschChief Knowledge OfficerAuthor Commented:
your sub submitLoginInfo works for opening page. But how do I get the source text into excel?
0
LajuanTaylorCommented:
Take a look at the following example. It uses the XMLHTTP object to get info from a remote site which, can  then be parsed using the (XML DOM).

The example is VB and it stores the extracted text is stored in Excel.
http://www.vba-and-excel.com/vba/internet/6-loading-information-from-the-internet-using-the-xmlhttp-object
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LajuanTaylorCommented:
I tested the sample and it works for getting text from a web site and loading into Excel... I just created a button on the worksheet and assigned a VB macro using the code from the example.
0
Richard DetschChief Knowledge OfficerAuthor Commented:
Great how fast the reply was, and how the progammer went back and forth on the issue until it was resolved.
0
LajuanTaylorCommented:
Thank You.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
HTML

From novice to tech pro — start learning today.