MSXML2.ServerXMLHTTP call in macro

Posted on 2014-08-03
Medium Priority
Last Modified: 2014-08-04

I have a macro that calls a j2ee integration object and displays the xml data in a cell in my excel

Public Sub Macro1()
Dim result As String
Dim myURL As String, postData As String
Dim winHttpReq As Object
Dim xmlhttp, responsexml As String

Dim usedRowRange As Integer
usedRowRange = ActiveSheet.UsedRange.Rows.Count
Set winHttpReq = CreateObject("MSXML2.ServerXMLHTTP")
myURL = "http://qdon:9080/os/labor"
postData = ("<Query" + "labor" + " xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns=""http://www.test.com/int"">" & _
    "<" + "labor" + "Query>" & _
    "</" + "labor" + "Query>" & _
    "</Query" + "labor" + ">")
winHttpReq.Open "POST", myURL, False
winHttpReq.setrequestheader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
winHttpReq.Send (postData)
'Response.ContentType = "text/xml"
'result = winHttpReq.responseText
result = winHttpReq.responsetext
'result = postData
'MsgBox (result)
Sheet1.Range("B2").Value = result
'Sheet1.Cells("A1").Value = result

Open in new window

i would like also to save the xml into a file called ""test.xml" on my c:\temp directory, can anyone assist me on this pls?
Question by:rutgermons
  • 2
LVL 46

Expert Comment

ID: 40238125
what is qdon?

Author Comment

ID: 40238495
its a j2ee url (dummy) for a business system the http call is to extract data from the labor object within that solution
LVL 46

Accepted Solution

aikimark earned 2000 total points
ID: 40238738
This code will write the XML.
Dim intFN as Integer

Open "C:\Temp\test.xml" For Output As intFN
Print #intFN, result
Close #intFN

Open in new window


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question