Open XML
Open XML SDK 2.5
Download from Microsoft
link - learn.microsoft.com/en-us/previous-versions/office/developer/office-2010/hh180830(v=office.14)
link - microsoft.com/en-gb/download/details.aspx?id=30425
link - exceloffthegrid.com/vba-cod-to-zip-unzip/
link - github.com/davecra/OpenXmlFileViewer
link - https://jkp-ads.com/articles/excel2007fileformat02.asp
The SDK (Software Development Kit) is built on top of the System.IO.Packaging API.
It provides strongly typed part classes to manipulate Open XML documents.
This simplifies the task of manipulating the zip packages and the underlying schema elements.
Open XML Productivity Tool for Office
This will help you to learn and work with the SDK and Open XML files.
Features include:
* Ability to generate Open XML source code based on document content
* Compare source and target Open XML files to reveal differences
* Document validation
Office Open File Formats
In the earlier form of these formats, prior to Ecma standardization, the Microsoft Office 2003 XML formats used a single monolithic file with embedded items like pictures as binary encoded blocks within the XML. Office Open XML no longer supports those bu
This format uses the ZIP file format and contains the individual files that form the basis of the document. In addition to Office markup, the package can also include embedded (binary) files in formats such as PNG, BMP, AVI or PDF.
Document markup languages
An Office Open XML file may contain several documents encoded in specialized markup languages corresponding to applications within the Microsoft Office product line. Office Open XML defines multiple vocabularies (using 27 namespaces and 89 schema modules.
Shared markup language materials include:
Office Math Markup Language (OMML)
Extended properties
Custom properties
Variant Types
Custom XML data properties
Bibliography
In addition to the above markup languages custom XML schemas can be used to extend Office Open XML.
Open XML Format - excel/xml/open-xml-format
[_rels]
extended properties - docProps/app.xml
core-properties - docProps/core.xml
officeDocument - xl/workbook.bin
custom-properties - docProps/custom.xml
app.xml - include snippet
Displays total number of worksheets / named ranges
core.xml - snippet
Date the file was originally created
custom.xml - custom document properties
open xml format
xl
charts
drawings
printerSettings
theme
worksheets
Removing all VBA macros
xl/_rels/workbiij.bin.rels.xml
delete the reference to vbaproject.bin
xl/vbaproject.bin
delete this file
Opening an XML File
Dim oDoc As DOMDocument
oDoc = New DOMDocument
oDoc.Load "C\temp\myfile.xml"
Set oDoc = Nothing
Processing
Every XML file defines the type of character scheme it is using.
This is the known as the encoding declaration.
This tells the parser which types or groups of characters and symbols to expect in the document.
The DOMDocument class has a method called "createProcessingInstruction" to help with this
oDoc.loadXML "<?xml version="1.0" encoding="utf-8"?>
Dim oInstruction As IXMLDOMProcessingInstruction
Set oInstruction = oDoc.createProcessingInstruction("xml","Version='1.0'")
oDoc.appendChild oInstruction
Root Element
Every XML file has at least one element
This element acts as the parent for all the other elements.
This element is referred to as the root element
Dim oElement As IXMLDOMElement
Set oElement = oDoc.createElement("Root")
Set oDoc.documentElement = oElement
This produces
<?xml version="1.0"?>
<root/>
You can get the name of a tag using the tagName property
oElement.tagName
Value of an Element
The value of an element is called the text
oElement.Text = "some text"
Getting all Elements
You can get all the values from an XML document using the XML property.
oDoc.Load "C\temp\myfile.xml"
Call MsgBox(oDoc.XML)
Empty Element
Elements can be empty
Number of top level nodes
oDoc.childNodes.length
Number of elements below the root element
oDoc.childNodes(1).childNodes.length
Nodes
A Node object is the primary data type
A node can be any of the following:
element node - consists of a start and an end tag
attribute node -
text node -
other type of node -
<root>
<element1>some text</element1>
<element1>some text</element1>
</root>
Element1 is an Element node
some text is a Text node
Elements
An element is the only type of Node that can have child elements or attributes
appendChild
You can create an element as a child of another element using the appendChild method
Dim oElement As IXMLDOMElement
Set oDoc = New DOMDocument
Set oElementRoot = oDoc.createElement("root")
Set oDoc.documentElement = oElementRoot
Set oElementChild = oDoc.createElement("element1")
oElementChild.Text = "some text"
oElementRoot.appendChild(oElementChild)
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext