Import Web Query From Report Location w/out Link

[twoplustwo]

Hi guys, bit of a tricky first for me. I need to pull back the data returned from the below link to a database. Ideally I would like to have control over what is being read in so I'd like to pass the data to an array first. Once the web query has run the URL does not update etc...

The trouble I'm having is navigating to the report and running the query. The Excel macro recorder gave me no clues. Any guidance would be much appreciated!

Link:

http://marketinformation.natgrid.co.uk/gas/ReportExplorer.aspx

Operational Data > Report Explorer > Nomination Report (date = current day)

 

[thechazm]

Yup thats a tough one. I looked at all the src from the site and they are doing some hocus pocus in there to keep the data safe and also link purposes. I don't even know which direction I would take on this issue but good luck.

 

[twoplustwo]

Hey Chaz.

SRC? It's not looking likely. Anyone else have any thoughts?

 

[spikepl]

How about giving their API a go?

 

[twoplustwo]

Hi Spike,

I wouldn't know where to start. Any decent links?

Cheers.

 

[spikepl]

http://www.nationalgrid.com/uk/Gas/Data/help/

 

[twoplustwo]

Thanks Spike. I guess I am interested in the "Pull API" information? The data items listed are accessible via this method.

 

[thechazm]

spikepl Great find! It's the simple things that bite the hardest...

 

[twoplustwo]

I've updated the top half of the sub with the National Grid details. I am not sure on the part from "With xmlhtp"

Thanks for your help with this guys!

Sub DoIt()
 
Dim sURL As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.XMLHTTP40
Dim xmlDoc As New DOMDocument
 
'sURL = "[URL]http://webservices.gama-system.com/exchangerates.asmx?op=CurrentConvertToEUR[/URL]"
sURL = "[URL]http://marketinformation.natgrid.co.uk/MIPIws-public/public/publicwebservice.asmx[/URL]"
 
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap:Envelope xmlns:xsi=""[URL]http://www.w3.org/2001/XMLSchema-instance[/URL]"" xmlns:xsd=""[URL]http://www.w3.org/2001/XMLSchema[/URL]"" xmlns:soap=""[URL]http://schemas.xmlsoap.org/soap/envelope/[/URL]"">"
sEnv = sEnv & " <soap:Body>"
sEnv = sEnv & " <GetPublicationDataWM xmlns=""[URL]http://www.NationalGrid.com/MIPI/[/URL]"">"
sEnv = sEnv & " <reqObject>"
sEnv = sEnv & " <LatestFlag>string</LatestFlag>"
sEnv = sEnv & " <ApplicableForFlag>string</ApplicableForFlag>"
sEnv = sEnv & " <ToDate>dateTime</ToDate>"
sEnv = sEnv & " <FromDate>dateTime</FromDate>"
sEnv = sEnv & " <DateType>string</DateType>"
sEnv = sEnv & " <PublicationObjectNameList>"
sEnv = sEnv & " <string>string</string>"
sEnv = sEnv & " <string>string</string>"
sEnv = sEnv & " </PublicationObjectNameList>"
sEnv = sEnv & " </reqObject>"
sEnv = sEnv & " </GetPublicationDataWM>"
sEnv = sEnv & " </soap:Body>"
sEnv = sEnv & "</soap:Envelope>"
 
With xmlhtp
  .Open "post", sURL, False
  .setRequestHeader "Host", "webservices.gama-system.com"
  .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
  .setRequestHeader "soapAction", "[URL]http://www.gama-system.com/webservices/CurrentConvertToEUR[/URL]"
  .setRequestHeader "Accept-encoding", "zip"
  .send sEnv
  xmlDoc.loadXML .responseText
  MsgBox .responseText
End With
 
End Sub

 

[twoplustwo]

Tell a lie...

With xmlhtp
    .Open "post", sURL, False
    .setRequestHeader "Host", "marketinformation.natgrid.co.uk"
    .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    .setRequestHeader "soapAction", "[URL]http://www.NationalGrid.com/MIPI/GetPublicationDataWM[/URL]"
    .setRequestHeader "Accept-encoding", "zip"
    .send sEnv
    xmlDoc.loadXML .responseText
    MsgBox .responseText
End With

How can I find the parameters required for the query details e.g. report name etc?

 

[twoplustwo]

Morning guys, hope you're all well.

Still trying to crack this with little success. Any suggestions?

 

[twoplustwo]

The routine attached provides the following attached messagebox.

Sub DoIt()
 
Dim sURL As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.XMLHTTP40
Dim xmlDoc As New DOMDocument
'sURL = "[URL]http://webservices.gama-system.com/exchangerates.asmx?op=CurrentConvertToEUR[/URL]"
sURL = "[URL]http://marketinformation.natgrid.co.uk/MIPIws-public/public/publicwebservice.asmx[/URL]"
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap:Envelope xmlns:xsi=""[URL]http://www.w3.org/2001/XMLSchema-instance[/URL]"" xmlns:xsd=""[URL]http://www.w3.org/2001/XMLSchema[/URL]"" xmlns:soap=""[URL]http://schemas.xmlsoap.org/soap/envelope/[/URL]"">"
sEnv = sEnv & "  <soap:Body>"
sEnv = sEnv & "    <GetPublicationDataWM xmlns=""[URL]http://www.NationalGrid.com/MIPI/[/URL]"">"
sEnv = sEnv & "      <reqObject>"
sEnv = sEnv & "        <LatestFlag>string</LatestFlag>"
sEnv = sEnv & "        <ApplicableForFlag>N</ApplicableForFlag>"
sEnv = sEnv & "        <ToDate>'15-AUG-2012 00:00:00'</ToDate>"
sEnv = sEnv & "        <FromDate>'10-AUG-2012 00:00:00'</FromDate>"
sEnv = sEnv & "        <DateType>normalday</DateType>"
sEnv = sEnv & "        <PublicationObjectNameList>"
sEnv = sEnv & "         <string>Actual EOD System Input</string>"
'sEnv = sEnv & "         <string>string</string>"
sEnv = sEnv & "        </PublicationObjectNameList>"
sEnv = sEnv & "      </reqObject>"
sEnv = sEnv & "    </GetPublicationDataWM>"
sEnv = sEnv & "  </soap:Body>"
sEnv = sEnv & "</soap:Envelope>"
 
With xmlhtp
    .Open "post", sURL, False
    .setRequestHeader "Host", "marketinformation.natgrid.co.uk"
    .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    .setRequestHeader "soapAction", "[URL]http://www.NationalGrid.com/MIPI/GetPublicationDataWM[/URL]"
    .setRequestHeader "Accept-encoding", "zip"
    .send sEnv
    xmlDoc.loadXML .responseText
    MsgBox .responseText
End With
 
End Sub