MSXML2.ServerXMLHTTP call in macro

Posted on 2014-08-03
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="""" xmlns="""">" & _
    "<" + "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
    LVL 44

    Expert Comment

    what is qdon?

    Author Comment

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

    Accepted Solution

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now