Send XML Using XMLHTTP
I’ve put together a basic script for sending an XML string or file to a webserver using Excel VBA and a simple PHP script to receive the data. The process uses XMLHTTP.
The goal is to create a simple upload script to update a server with XML data created from a spreadsheet. Then have multiple end user workbooks map to the central file and create reports from it. The basic nuts and bolts:
For the example I use a basic XML string which looks like this:
1 2 3 4 5 6 7 8 9 10 11 | myxml = "<?xml version=""1.0""?>" & _ "<order>" & _ "<product>mens polo</product>" & _ "<price>4.89</price>" & _ "<size>large</size>" & _ "</order>" |
The PHP I’ve used simply returns the data it was sent back to Excel. So if you recieve the same XML data you sent in a message box, you’ve done it correctly. If no post data is found it says “no dice”. The location of the test URL is /pl/xlxml.php
Obviously you’ll want to update the script to save the file contents to your server for practical uses. Here’s the PHP to recieve the XML post data and return it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <?php $data = file_get_contents('php://input'); if (empty($data)) { echo "woops, no dice"; } else { echo $data; } ?> |
So we now know what we’re sending, and how to recieve it, let’s send the XML data!
The following code was created in Excel 2003 and using a reference to Tools->References->Microsoft XML v5.0 in the VB editor. It’s well commented so you can see everything that’s going on:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | Sub SendXML() 'HTTP variable Dim myHTTP As MSXML2.XMLHTTP 'HTTP object Set myHTTP = CreateObject("msxml2.xmlhttp") 'create dom document variable 'stores the xml to send Dim myDom As MSXML2.DOMDocument 'Create the DomDocument Object Set myDom = CreateObject("MSXML2.DOMDocument") 'Load entire Document before moving on myDom.async = False 'xml string variable 'replace with location if sending from file or URL Dim myxml As String myxml = "<?xml version=""1.0""?>" & _ "<order>" & _ "<product>mens polo</product>" & _ "<price>4.89</price>" & _ "<size>large</size>" & _ "</order>" 'loads the xml 'change to .Load for file or url myDom.loadXML (myxml) 'open the connection myHTTP.Open "post", _ "https://www.automateexcel.com/excel/pl/xlxml.php", False 'send the XML myHTTP.Send (myDom.XML) 'Display the response MsgBox myHTTP.ResponseText End Sub |
If you’ve made it this far, added a reference and ran the code, you’ll see that indeed PHP received and returned the XML:
Notes: I’ve only dynamically created the XML or loaded it from XML files and URL’s, next is to send data from an XML map.
I echoed the data back to Excel instead of writing it to the server because the URL is public, I wanted to give people a sandbox and not have random files being sent to my server.
