Fun with Excel 2003 and Web Services

Work are breaking me back in gently to the technical stream with one or two small jobs with bits of coding. I’m currently finishing up a Microsoft Excel 2003 based utility that imports and exports information from a system. A new version is on the way, so in the meantime we work around missing functionality by creating utilities. This particular one calls a few web services, and also a couple of aspx pages. In between we save a few xml files.

Got really stuck last night with an xml document that wouldn’t de-serialize at the server side. What was really annoying was that the document was based on an empty one that another method on the web service had provided. You’d think it would accept back the xml it had created? Wrong!

We narrowed it down to a problem with dates. The xml we were dealing with had two dates, one as an attribute of the parent element and one as a child element in the xml itself thus:

<PARENT action=”Update” lastChange=”2007-06-21T18:50:24.2970000+01:00″>



We tried all sorts of combinations of date formatting, I had a good hunt around the web for UTC date format related things and typed these in manually to no avail.

Then my colleague came up with an idea to do with the timezone offset, he had a theory that the plus sign adjusting us for British Summer Time in the UK was causing us a problem. And right enough, when you replace the plus sign with the escape code %2b (i.e. the hex for the ascii code) it would go through the process on the server side which was trying to fit the information into format, thus:

<PARENT action=”Update” lastChange=”2007-06-21T18:50:24.2970000%2b01:00″>



Interesting to see that the effort I had to go through with form submission in the old days of the Internet are still around with us.