Saving and Opening XML Documents

To save a workbook as an XML document use the SaveAs() method of the Workbook object. The following example saves the workbook as an XML document with the name myFile.xml using two named arguments (Filename and FileFormat) with the SaveAs() method.

ActiveWorkbook.SaveAs Filename:= "myFile.xml", FileFormat:=xlXMLSpreadsheet

The constant xlXMLSpreadsheet assigned to the FileFormat argument specifies the Excel-XML format.

To open an XML document previously saved with the Excel-XML structure use either the Open() or OpenXML() methods of the Workbooks collection object.

Workbooks.Open Filename:= "myFile.xml"

If the structure of the XML document is Excel-XML, then the opened file will conform to that of a normal Excel spreadsheet; however, if the file is just a well-formed XML document (not structured as Excel-XML), then Excel will open it as tabular data. Figure 8.9 shows the result of opening the words.xml file with the Open() method of the Workbooks collection object. The words.xml file had not been previously saved using the Excel-XML structure.

An XML file opened in tabular form.

An XML file opened in tabular form.

The OpenXML() method of the Workbooks collection object includes an optional argument (LoadOption) that allows you to choose how to open the XML file. VBA-defined constants you can use with the LoadOption argument include: xlXmlLoadlmportToList, xlXmlLoadOpenXml, xlXmlLoadMapXml, and xlXmlLoadPromptUser. To import the document as a list, use xlXmlLoadlmportToList; otherwise xlXmlLoadOpenXml will open the document in tabular form. Using the constant xlXmlLoadMapXml will display the schema-map of the XML document file in the XML Source Task Pane, but will not import any data into the worksheet. Finally, the constant xlXmlLoadPromptUser displays a prompt (see Figure 8.3) to the user so he or she can choose how to open the file.

Workbooks.OpenXML Filename:= "myFile.xml", LoadOption:=xlXmlLoadImportToList

Was this article helpful?

0 0
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment