Getting contents of link into excel via VBA

Question

I have asked this question a few times but i dont feel i have acheieved what i want. Several people on here kindly provided assistance but i still have the problem of using the data as its not in usable format.

I would like the contents of the link on the website put into an worksheet, via VBA

The link is on the the top right hand side of the web page.

The link is

http://bmreports.com/servlet/com.logica.neta.bwp_PanBMDataServlet

Code so far:

 Set ie = CreateObject("InternetExplorer.Application")
        ie.Navigate "http://bmreports.com/servlet/com.logica.neta.bwp_PanBMUTop"
        ie.Visible = True

    Do Until Not ie.Busy And ie.readyState = 4
        DoEvents
    Loop

    ie.Document.getelementbyid("param5").Value = "2014-04-16"
    ie.Document.getelementbyid("param6").Value = "43"
    ie.Document.getelementbyid("go_button").Click

    Set objShell = CreateObject("Shell.Application")
    IE_count = objShell.Windows.Count
    For x = 0 To (IE_count - 1)
        On Error Resume Next    ' sometimes more web pages are counted than are open
        my_url = objShell.Windows(x).Document.Location
        my_title = objShell.Windows(x).Document.Title

        If my_url Like "http://bmreports.com/servlet/com.logica.neta.bwp_PanBMDataServlet" Then
            Set ie = objShell.Windows(x)
            Exit For
        Else
        End If
    Next

For Each ele In ie.Document.getElementsByTagName("span")

    If ele.innerhtml = "Current data in CSV format" Then
        DoEvents
        ele.Click
        'At this point you need to Save the document manually
        ' or figure out for yourself how to automate this interaction.
    End If
Next

 If my_url Like "about:blank" Then
Set ie = objShell.Windows(x)
Else
End If

 table_html = ie.Document.getElementsByTagName(("Text"))(2).innerhtml
    html_lines = Split(table_html, Chr(10), -1, vbTextCompare)

    Worksheets("Sheet1").Activate
    Range("A1").Select

    For x = 0 To UBound(html_lines)
        ActiveCell = html_lines(x)
        ActiveCell.Offset(1, 0).Select
    Next

Why don't you link to the other questions and explain why you don't feel you've achieved it? What have you managed so far? — Nick.Mc, Apr 19 '14 at 12:40
Your question is not that clear; that could be why you aren't getting the answer you want. What *exactly* do you want the VBA code to do? — rory.ap, Apr 19 '14 at 12:40
possible duplicate of [get specific table into excel using VBA](http://stackoverflow.com/questions/23048363/get-specific-table-into-excel-using-vba) — David Zemens, Apr 19 '14 at 13:28
If you open the link, within the page there is a link called current data in csv format. I would like to put the contents of that into a spreadsheet automatically by VBA. — Ingram, Apr 19 '14 at 13:29
Well so far someone here helped me to copy the table from the actual webpage into a spreadsheet. But i soon realised this wasnt the route to go down — Ingram, Apr 19 '14 at 13:30
Also possible duplicate of [THIS](http://stackoverflow.com/questions/17224915/download-files-from-a-web-page-using-vba-html/17226991#17226991) where I used `FileSystemObject` and `RegularExpressions` to parse the contents of the webpage in to a CSV file. You guys need to start talking to one another, because we've already solved this question... — David Zemens, Apr 19 '14 at 13:31
@ingram it is exactly the same requirement to get the table data in CSV from the same website... now perhaps the website has changed slightly since I gave [THIS ACCEPTED ANSWER](http://stackoverflow.com/a/17280296/1467082) over a year ago, but the fundamentals should still be there and maybe only requires some tweaking. If you're not capable of making minor adjustments to the code, you should be paying people to write it for you, not soliciting "help" where you do not actually write any of the code yourself. — David Zemens, Apr 19 '14 at 13:45

score 1 · Accepted Answer · answered Apr 19 '14 at 13:42

1

Does this do what you want?

Sub Test()
    Set ie = CreateObject("InternetExplorer.Application")
        ie.Navigate "http://bmreports.com/servlet/com.logica.neta.bwp_PanBMDataServlet"
        ie.Visible = True

    Do Until Not ie.Busy And ie.readyState = 4
        DoEvents
    Loop

    ie.ExecWB 17, 2     ' select the data
    ie.ExecWB 12, 0     ' copy the data

    ActiveSheet.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False  ' paste the data
End Sub

answered Apr 19 '14 at 13:42

ron

1,456
3
18
27

Good stuff. He'll need to parse out the table, but that's neat. – David Zemens Apr 19 '14 at 13:44
@ron hello, this is so much better, i just need to tidy it all up which i can do myself. This is exactly what i needed. Thanks Ron and David for following this up – Ingram Apr 19 '14 at 13:59
@David Zemens not to sure what you mean by parsing it out? What will that do? – Ingram Apr 19 '14 at 14:00
@Ingram if you need to do a text-to-columns or other formatting to put the table cell data in an organized table in Excel, for instance. Otherwise, if you're happy just with the straight copy/paste like that, then there's no need to parse or organize the data. Cheers. – David Zemens Apr 19 '14 at 18:36

Getting contents of link into excel via VBA

1 Answers1