Excel VBA using XMLHTTP with Siteminder Secured site

user2524654 picture user2524654 · Sep 10, 2013 · Viewed 11.5k times · Source

I am completely new to using XMLHTTP and experimenting with trying to download a report off of our company's internet site using Excel VBA. The problem I seem to be running into is that the site is protected by Siteminder. I think I need to use an XMLHTTP.Open with GET but anyway I try all I just seem to get is the Siteminder HTML code. So I am trying to first use a post to send my username and password to Siteminder something like:

Function PostXmlData(vUrl As String, UserName As String, Password As String, xmlText     
As String
Dim XMLHttp As Object
Set XMLHttp = CreateObject("MSXML2.XMLHTTP")
XMLHttp.Open "POST", vUrl, False, UserName, Password
XMLHttp.setRequestHeader "Content-Type", "text/xml;charset=utf-8"   

XMLHttp.send (xmlText)
PostXmlData = XMLHttp.responseText
End Function

Sub Posttest ()
Dim add As String
Dim User As String
Dim Pass As String
Dim send As String
Dim ret As Variant

add = "https://mycompanywebsite.com/apps/application/Main/"
User = "username"
Pass = "password"
Send="DashboardId=http://mycompanywebsite.com/DAVCatalog/Dashboards/Teams/Client%20_
Extranet%20AM"

ret = PostXmlData(add, User, Pass, send)
End Sub

Am I on the right track or is this not even possible? Any suggestions would be greatly appreciated or if there is some site someone could direct me to that would be helpful. THanks.

Answer

Jaycal picture Jaycal · Sep 10, 2013

You'll need to load the responseText into an XMLDocument so that you can parse it. See example below (make sure you add the Microsoft XML reference to the reference library)

Dim xmldoc As MSXML2.DOMDocument

' Create a new XMLDocument to which to load the XML text
Set xmlDoc = New DOMDocument
xmldoc.LoadXML (xmlhttp.responseText)

' Fetch the XML
Set xmlhttp = CreateObject("Microsoft.xmlHTTP")
xmlhttp.Open "Get", yourURL, False
xmlhttp.send

' Create a new XMLDocument to which to load the XML text
Set xmlDoc = New DOMDocument
xmldoc.LoadXML (xmlhttp.responseText)

From here, you should be able to parse the XML using objects like NodeList, DOMElement, etc.