pulling off data from website

Hi,

I am trying to pull off data from the following webpage

http://www.bmreports.com/bsp/SystemPricesHistoric.htm

I would like to pull off all the data in the table, i have tried a webquery and that hasnt seem to work. clicking on the csv red box at the top hasnt really helped either, and besides i would like an automated process which i will work on after i can pull off the data from site.

Could someone please help?

Originally Posted by Leith Ross

Hello squirrely,

The table you want is a Frame element named "bottom". This Frame is an empty document that contains a place holder for the table. It will be filled in by a Java servlet after the date has been selected and validated. I don't believe VBA has access to the server side, only the client side.

Any ideas what i can do?

As far as I can see, the data is being pulled in via Ajax from this url:
http://www.bmreports.com/bsp/additio...&dT=2012-05-21
Where dT is the date, that being said, I can't actually see the data in the web page

Originally Posted by Kyle123

As far as I can see, the data is being pulled in via Ajax from this url:
http://www.bmreports.com/bsp/additio...&dT=2012-05-21
Where dT is the date, that being said, I can't actually see the data in the web page

Hi Thanks for your reply, what can i do to get the data then? is it possible?

Please don't quote whole posts, it clutters up the thread. Have you tried doing a web query to the link I posted?

I have tried it although it requires quite a clean up, perhaps i can do this with VBA after i pulled it in.

Hi Kyle,

How did you know it was pulled in from the URL you quoted, there are some other pages on that website i would like pulling in also and i dont want to keep on having to ask so how to do this would be much appreciated?

Can you provide the output of how you would like it to look in a workbook, I'll have a fiddle and sort something out for you

Open developer tools in Chrome, select a date in the top part as normal, run the report then have a ferret through the Network tab in the chrome developer tools. It will be one of the top couple, you'll see that when you click on a request, you can see the data that was sent as well as the response received. In your case that xml looked the most promising

very clever! I am going to send you a file shortly

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi Kyle, i have attached the file with some notes.

Cheers

How's this?

Sub GetData(dDate As Date)

Dim oXML As Object
Dim oNodes As Object, oNode As Object, oChildNode As Object
Dim x As Long, y As Long
Dim sNodeName, vData, vNodeNames

vNodeNames = Array("SD", "SP", "SSP", "SBP", "BD", "PDC", _
                   "NIV", "SPPA", "BPPA", "OV", "BV", "TOV", _
                   "TBV", "ASV", "ABV", "TASV", "TABV", "RP", "RPRV")

With CreateObject("MSXML2.DOMDocument")
    .async = False
    .Load "http://www.bmreports.com/bsp/additional/soapfunctions.php?element=SYSPRICE&dT=" & Format(dDate, "yyyy-mm-dd")
    Set oNodes = .getElementsByTagName("ELEMENT")
End With

ReDim vData(1 To oNodes.Length, 1 To 19)

x = 1: y = 1
For Each oNode In oNodes
    For Each sNodeName In vNodeNames
        Set oChildNode = oNode.SelectSingleNode(sNodeName)
        If Not oChildNode Is Nothing Then
            vData(x, y) = oChildNode.Text
        End If
        y = y + 1
    Next sNodeName
    y = 1
    x = x + 1
Next oNode

With Sheets("output")
    .Range("a1").Resize(, UBound(vNodeNames) + 1).Value = vNodeNames
    .Range("a2").Resize(UBound(vData), UBound(vData, 2)).Value = vData
End With

End Sub

Sub test()
GetData #1/1/2013#
End Sub

You'll need to change the name of the output sheet accordingly

This looks ace, exactly what I wanted. Your a star.

Glad it helped