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:

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:

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:

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.