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