Hey, Scripting Guy! How can I convert an Office Excel spreadsheet to XML?

— KE

Hey, KE. We apologize in advance if today’s column seems a little rushed; at the moment, we’re sitting in an office, surrounded by boxes and packing crates and waiting for the movers to come in and clear everything out. And no, the Scripting Guy who writes this column hasn’t been fired, he – just a second, we need to double-check something ….

No, we were right: the Scripting Guy who writes this column hasn’t been fired. Instead, it’s Moving Day, and he and his fellow Scripting Guys are about to move to new offices in a new building. For those of you who like to keep track of this sort of thing, for the Scripting Guy who writes this column that will make, in the past six years, 10 offices in 4 different buildings.

And, sadly, no, that’s not a record for Microsoft. Not even close.

At any rate, the reason we’re feeling a little rushed is because we don’t exactly know when the movers will show up. Adopting the same highly-beloved customer service strategy employed by the TV cable companies, today’s move will start promptly at noon. Unless, of course, it starts later. Or maybe even earlier; after all, we were required to have everything packed up a day in advance. If today’s column suddenly breaks off in the middle of a sentence, well, then you’ll know the movers came a little earlier than expected.

And because we may (or may not) be pressed for time today we decided to pick an easy question to answer. How easy? This easy:

Const xlXMLSpreadsheet = 46

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

k = 1

For i = 1 to 5
    For j = 1 to 3
        objWorksheet.Cells(i,j) = k
        k = k + 1
    Next
Next

objWorkbook.SaveAs "C:\Scripts\Test.xml", xlXMLSpreadsheet        

Sure, we can explain how this all works; we have lots of time. (Unless we don’t.) As you can see, we start out by defining a constant named xlXMLSpreadsheet and setting the value to 46; we’ll use this constant to tell Excel that we want to save the spreadsheet as XML. After defining the constant we create an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Excel that we can see onscreen.

Of course, it’s no fun to have a running instance of Excel unless we actually do something with that instance. Consequently, we use the following two lines of code to add a new workbook and a new worksheet to our running instance of Excel:

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

We then use this block of code to add some (in this case, meaningless) data to the worksheet:

k = 1

For i = 1 to 5
    For j = 1 to 3
        objWorksheet.Cells(i,j) = k
        k = k + 1
    Next
Next

Now we have an Excel spreadsheet filled with all sorts of interesting data. That’s nice, but how do we save that data as XML? Believe it or not, that takes only a single line of code:
objWorkbook.SaveAs "C:\Scripts\Test.xml", xlXMLSpreadsheet
As you can see, all we’re doing here is calling the SaveAs method and passing the method a pair of parameters: the complete path to our new XML file (C:\Scripts\Test.xml) and the constant xlXMLSpreadsheet. Will that really give us a file that can be opened not only by Excel but also by Notepad or by an XML editor? Of course it will; here’s an excerpt from the data section of the file we created:
<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="5" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">2</Data></Cell>
    <Cell><Data ss:Type="Number">3</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">4</Data></Cell>
    <Cell><Data ss:Type="Number">5</Data></Cell>
    <Cell><Data ss:Type="Number">6</Data></Cell>
   </Row>

Yes, we know; what you’re really curious about is this: after years of working out of tiny, windowless offices is the Scripting Guy who writes this column finally going to get the working environment he deserves? Let’s put it this way: the last thing the Scripting Guy who writes this column wants to get is what he deserves. If you know what we mean.