Office web services toolkit search problem
 

Hi,
I'm trying to call a web service from an excel worksheet. I've
downloaded the office web services toolkit and tried it with many examples in
the web and worked. But now, I'm having a problem to connect to a web service
located in a computer inside my company LAN, that it's not in a public
internet server.
When I enter the address where the web service is located (i.e
http://10.0.0.10/test/urn:Echo?wsdl), it doesn't find it.
What can I be doing wrong.
Thank you in advance.

Office web services toolkit search problem
 

Hi Nacho,

When I enter the address where the web service is located (i.e
http://10.0.0.10/test/urn:Echo?wsdl), it doesn't find it.

What happens if you type that url into IE?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Office web services toolkit search problem
 

Hi Jan,
If I type it into IE, it appears the file. I believe it's some kind of
problem with the proxy or something like that. I don't know how to tell excel
that for certain ip address (the local ones), don't use the proxy server.
Could be something related to that ???
Thanks
Regards,
Nacho

"Jan Karel Pieterse" wrote:

Hi Nacho,

When I enter the address where the web service is located (i.e
http://10.0.0.10/test/urn:Echo?wsdl), it doesn't find it.

What happens if you type that url into IE?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Office web services toolkit search problem
 

Hi Nacho,

I don't know how to tell excel
that for certain ip address (the local ones), don't use the proxy server.
Could be something related to that ???

Try (in IE!) Tools, internet options, Advanced. Change the setting at "Use
HTTP 1.1 through proxy connections" and try again.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Office web services toolkit search problem
 

Hi Jan,
I tried changing the setting "Use HTTP 1.1 through proxy
connections" but didn't work. I don't know if could be something about proxy
but I believe it's possible becouse if I search a file in a public address it
works, but with the an ip of a LAN don't. You think of other thing ??




"Jan Karel Pieterse" wrote:

Hi Nacho,

I don't know how to tell excel
that for certain ip address (the local ones), don't use the proxy server.
Could be something related to that ???

Try (in IE!) Tools, internet options, Advanced. Change the setting at "Use
HTTP 1.1 through proxy connections" and try again.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Office web services toolkit search problem
 

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/micros...a/browse_threa
d/thread/661eb408328a6bbe/cbdfb0560425ca83?lnk=st&q=%22Just+posting+thi
s+for+others+to+reference+since+I+never+found+an%2 2&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 & _
" <PageNamestring</PageName" & vbNewLine & _
" <CurrentGasDayDateTime</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("GetLate stPublicationTimeResu
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("EDPEnergyGraphTabl eBE")
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

Office web services toolkit search problem
 

Hi Jan,
Actually I could make it work, it was a problem with the proxy I think, but
the IT department solved it.
Thank you for your help

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/micros...a/browse_threa
d/thread/661eb408328a6bbe/cbdfb0560425ca83?lnk=st&q=%22Just+posting+thi
s+for+others+to+reference+since+I+never+found+an%2 2&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 & _
" <PageNamestring</PageName" & vbNewLine & _
" <CurrentGasDayDateTime</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("GetLate stPublicationTimeResu
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("EDPEnergyGraphTabl eBE")
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