Hi Nacho,
You think of other thing ??
Firewall settings?
If you cannot get this to work at all, you could always access the
webservice directly using the msxml dll.
Below is an example for the NationalGrid webservice.
IN effect, what you need to know is what envelope (that's xml) to send
to which url and what xml structure you get back. Then you can use the
msxml domdocument object to extract the relevant information from the
message you receive from the webservice.
Mind the word wrap, which has probably been wrecked in this post.
Regards,
Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com
'======================================================================
' Sub Name : GetDataFromNatGrid
' Return value : none
' Purpose : Fetches gas flows from national grid webservice
' Assumptions : ---
' Uses : ---
' Created : 17-1-2007 11:42, jpieter5
' Modifications :
'=====================================================================
Sub GetDataFromNatGrid()
'Ref:
http://groups.google.nl/group/microsoft.public.project.vba/browse_threa
d/thread/661eb408328a6bbe/cbdfb0560425ca83?lnk=st&q=%22Just+posting+thi
s+for+others+to+reference+since+I+never+found+an%22&rnum=1#cbdfb0560425
ca83
Dim sURL As String
Dim strXML As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.XMLHTTP40
' Dim xmlhtp As New MSXML2.ServerXMLHTTP40
Dim xmlDoc As New DOMDocument
Dim varArray As Variant
If IsTimeStampUpdated Then
EnableProxyHTTP
sURL =
"
http://energywatch.natgrid.co.uk/EDP-PublicUI/PublicPI/InstantaneousFl
owWebService.asmx"
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>" & vbNewLine
& _
"<soap:Envelope
xmlns:xsi=""
http://www.w3.org/2001/XMLSchema-instance""
xmlns:xsd=""
http://www.w3.org/2001/XMLSchema""
xmlns:soap=""
http://schemas.xmlsoap.org/soap/envelope/"">" & vbNewLine
& _
" <soap:Body>" & vbNewLine & _
" <GetInstantaneousFlowDataResponse
xmlns=""
http://www.NationalGrid.com/EDP/UI/"">" & vbNewLine & _
" <GetInstantaneousFlowDataResult>" & vbNewLine & _
" <ReportName
xmlns=""
http://www.NationalGrid.com/EDP/BusinessEntities/Public"">strin
g</ReportName>" & vbNewLine & _
" <PublishedTime
xmlns=""
http://www.NationalGrid.com/EDP/BusinessEntities/Public"">dateT
ime</PublishedTime>" & vbNewLine & _
" <EDPReportPage
xmlns=""
http://www.NationalGrid.com/EDP/BusinessEntities/Public"">" &
vbNewLine & _
" <PageName>string</PageName>" & vbNewLine & _
" <CurrentGasDay>DateTime</CurrentGasDay>" &
vbNewLine & _
" <EDPEnergyGraphTableCollection>" & vbNewLine &
_
" <EDPEnergyGraphTableBE xsi:nil=""true"" />" &
vbNewLine & _
" <EDPEnergyGraphTableBE xsi:nil=""true"" />" &
vbNewLine & _
" </EDPEnergyGraphTableCollection>" & vbNewLine &
_
" <NoteCollection>" & vbNewLine & _
" <EDPNoteBE xsi:nil=""true"" />" & vbNewLine &
_
" <EDPNoteBE xsi:nil=""true"" />" & vbNewLine &
_
" </NoteCollection>" & vbNewLine & _
" </EDPReportPage>" & vbNewLine & _
" </GetInstantaneousFlowDataResult>" & vbNewLine & _
" </GetInstantaneousFlowDataResponse>" & vbNewLine & _
" </soap:Body>" & vbNewLine & _
"</soap:Envelope>"
With xmlhtp
.Open "post", sURL, False
.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
.setRequestHeader "soapAction",
"
http://www.NationalGrid.com/EDP/UI/GetInstantaneousFlowData"
.setRequestHeader "Accept-encoding", "zip"
.send sEnv
' Next statement strips off the soap envelope and creates
an XML document with just the results.
xmlDoc.loadXML .responseText
'Now use xmlDoc to get your data
End With
End If
End Sub
Public Function IsTimeStampUpdated() As Boolean
Dim sURL As String
Dim strXML As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.XMLHTTP40
Dim xmlDoc As New DOMDocument
Dim datTimestamp As Date
sURL =
"
http://energywatch.natgrid.co.uk/EDP-PublicUI/PublicPI/InstantaneousFl
owWebService.asmx"
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>" & vbNewLine & _
"<soap:Envelope
xmlns:xsi=""
http://www.w3.org/2001/XMLSchema-instance""
xmlns:xsd=""
http://www.w3.org/2001/XMLSchema""
xmlns:soap=""
http://schemas.xmlsoap.org/soap/envelope/"">" & vbNewLine
& _
" <soap:Body>" & vbNewLine & _
" <GetLatestPublicationTime
xmlns=""
http://www.NationalGrid.com/EDP/UI/"" />" & vbNewLine & _
" </soap:Body>" & vbNewLine & _
"</soap:Envelope>"
With xmlhtp
' .setProxy 2, "isa.essent.local:8080"
.Open "post", sURL, False
.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
.setRequestHeader "soapAction",
"
http://www.NationalGrid.com/EDP/UI/GetLatestPublicationTime"
.setRequestHeader "Accept-encoding", "zip"
.send sEnv
' Next statement strips off the soap envelope and creates an
XML document with just the results.
xmlDoc.loadXML .responseText
End With
datTimestamp =
CDate(Replace(xmlDoc.getElementsByTagName("GetLatestPublicationTimeResu
lt").NextNode.Text, "T", " "))
If datTimestamp > mdatNationalGridTimeStamp Then
IsTimeStampUpdated = True
Else
IsTimeStampUpdated = False
End If
End Function
Function NatGridXML2Array(strXML As String, strTable As String) As
Variant
Dim objXMLDoc As MSXML2.DOMDocument
Dim nodNode As MSXML2.IXMLDOMNode
Dim nodNode1 As MSXML2.IXMLDOMNode
Dim nodNode2 As MSXML2.IXMLDOMNode
Dim lngCountRecords As Long
Dim strObjName As String
Dim varReturn() As Variant
Set objXMLDoc = New MSXML2.DOMDocument
objXMLDoc.loadXML strXML
ReDim varReturn(4, 2)
'First get the field names
For Each nodNode In
objXMLDoc.getElementsByTagName("EDPEnergyGraphTableBE")
If UCase(nodNode.FirstChild.Text) = UCase(strTable) Then
varReturn(0, 0) =
nodNode.childNodes(2).childNodes(0).childNodes(0).nodeName
varReturn(1, 0) =
nodNode.childNodes(2).childNodes(0).childNodes(1).childNodes(0).childNo
des(0).nodeName
varReturn(2, 0) =
nodNode.childNodes(2).childNodes(0).childNodes(1).childNodes(0).childNo
des(1).nodeName
varReturn(3, 0) =
nodNode.childNodes(2).childNodes(0).childNodes(1).childNodes(0).childNo
des(2).nodeName
varReturn(4, 0) =
nodNode.childNodes(2).childNodes(0).childNodes(1).childNodes(0).childNo
des(3).nodeName
lngCountRecords = 0
For Each nodNode1 In nodNode.childNodes(2).childNodes
strObjName = nodNode1.FirstChild.Text
For Each nodNode2 In nodNode1.childNodes(1).childNodes
lngCountRecords = lngCountRecords + 1
ReDim Preserve varReturn(4, lngCountRecords)
varReturn(0, lngCountRecords) = strObjName
varReturn(1, lngCountRecords) =
CDate(Replace(nodNode2.childNodes(0).Text, "T", " "))
varReturn(2, lngCountRecords) =
CDbl(nodNode2.childNodes(1).Text)
varReturn(3, lngCountRecords) =
nodNode2.childNodes(2).Text
varReturn(4, lngCountRecords) =
CDate(Replace(nodNode2.childNodes(3).Text, "T", " "))
Next
Next
Exit For
End If
Next
NatGridXML2Array = varReturn
Set nodNode = Nothing
Set objXMLDoc = Nothing
End Function
