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:
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:
<?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:
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", _
"http://www.automateexcel.com/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.
Tom | 
09 24th, 2005| 
Gyula Gulyas took my original Google Maps in Excel and made two changes to it that some readers may be interested in:
so I’m sure I can use the Canadian version.