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)