Tag Archives: word

VBA XML

Working with XML files in VBA (VBA XML)

1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 5.00 out of 5)
Loading...

XML files are one of the most common type of data files apart from text and CSV (comma-separated values) files. Reading data files which are not hierarchical (as XML files or JSON) is relatively easy. You can read in the data row by row and process columns separately. With XML (and JSON) the task is not as easy as the data is hierarchical (parent-child relationships exist between records in the schema) and the number of underlying nodes may vary as opposed to tabular data which usually has a constant number of columns separated with a specific delimiter.

Fortunately, we can use the MSXML2.DOMDocument object in VBA. Let’s however, as always, start with a short introduction as to how XML files a structure before we dive into the examples.

Loading XML document in VBA

The MSXML2.DOMDocument object allows you to easily traverse through an XML structure an extract any XML node and/or attribute needed. Let’s look at the example below.

Below we start by loading the XML document. Notice that I am selecting the load to be performed synchronously and not validation be carried out on parsing the document. Feel free to change these options if needed.

Sub TestXML()
    Dim XDoc As Object, root as Object
    
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load (ThisWorkbook.Path & "\test.xml")
    Set root = XDoc.DocumentElement
    '... 
End Sub

Alternatively load an XML from a string:

Sub TestXML()
    Dim XDoc As Object, root as Object
    
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.LoadXML ("<root><child></child></root>")
    Set root = XDoc.DocumentElement
    '... 
End Sub

That’s it. You have loaded the XML document into memory into the DOMDocument object. The document has been parsed and you can easily traverse the enclosed elements. See next section.

XML DOM nodes in VBA

The XML document will provide you with the root of the entire DOM (of type XDoc.DocumentElement). Each DocumentElement (XML DOM node) facilitates the following node references:

  • parentNode [XDoc.DocumentElement] – the parent node, one node higher in the DOM hierarchy
  • firstChild [XDoc.DocumentElement] – the first child node, first node lower in the DOM hierarchy
  • lastChild [XDoc.DocumentElement] – the last child node, last node lower in the DOM hierarchy
  • childNodes [Array of type XDoc.DocumentElement] – all child nodes of the current node, all nodes lower in the DOM hierarchy
  • nextSibling [XDoc.DocumentElement] – next sibling node i.e. node on the same level in the DOM hierarchy, having the same parent node
  • previousSibling [XDoc.DocumentElement]– previous sibling node i.e. node on the same level in the DOM hierarchy, having the same parent node

All the above references allow you to free move within the XML DOM.

ChildNodes

Let’s start by extracting the first list and printing it’s XML and text contents. The basics to moving around the XML DOM is using ChildNodes.

Sub TestXML()
    Dim XDoc As Object
    
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load (ThisWorkbook.Path & "\test.xml")
    
    'Get Document Elements
    Set lists = XDoc.DocumentElement
    
    'Get first child ( same as ChildNodes(0) )
    Set getFirstChild = lists.FirstChild
    'Print first child XML
    Debug.Print getFirstChild.XML
    'Print first child Text
    Debug.Print getFirstChild.Text

    Set XDoc = Nothing
End Sub

This is the result

'Print first child XML
<List>
    <Name>Recon</Name>
    <TO>John;Bob;Rob;Chris</TO>
    <CC>Jane;Ashley</CC>
    <BCC>Brent</BCC>
</List>
'Print first child Text
Recon John;Bob;Rob;Chris Jane;Ashley Brent

Traversing through the whole XML in VBA

Now that we got the basics let’s print out the whole contents of the XML DOM including the basenames (node names).

Sub TestXML()
    Dim XDoc As Object
    
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load (ThisWorkbook.Path & "\test.xml")
    
    'Get Document Elements
    Set lists = XDoc.DocumentElement
    
    'Traverse all elements 2 branches deep
    For Each listNode In lists.ChildNodes
        Debug.Print "---Email---"
        For Each fieldNode In listNode.ChildNodes
            Debug.Print "[" & fieldNode.BaseName & "] = [" & fieldNode.Text & "]"
        Next fieldNode
    Next listNode
    
    Set XDoc = Nothing
End Sub

This is the result:

---Email---
[Name] = [Recon]
[TO] = [John;Bob;Rob;Chris]
[CC] = [Jane;Ashley]
[BCC] = [Brent]
---Email---
[Name] = [Safety Metrics]
[TO] = [Tom;Casper]
[CC] = [Ashley]
[BCC] = [John]
---Email---
[Name] = [Performance Report]
[TO] = [Huck;Ashley]
[CC] = [Tom;Andrew]
[BCC] = [John;Seema]

Easy right? Using the basics above we can easily move around the document. But this still seems like a lot of coding right? Well there is an easier way of moving / extracting items using the DOMDocument object – called XPath.

XML Document example node references

Now that we have a hang of our XML document. Let’s work on a simple example. Below an XML document assigned with references to each node/nodes:

Example XML Document

<?xml version="1.0" encoding="utf-8"?>
<DistributionLists>
    <List>
        <Name>Recon</Name>
        <TO>John;Bob;Rob;Chris</TO>
        <CC>Jane;Ashley</CC>
        <BCC>Brent</BCC>
    </List>
    <List>
        <Name>Safety Metrics</Name>
        <TO>Tom;Casper</TO>
        <CC>Ashley</CC>
        <BCC>John</BCC>
    </List>
    <List>
        <Name>Performance Report</Name>
        <TO>Huck;Ashley</TO>
        <CC>Tom;Andrew</CC>
        <BCC>John;Seema</BCC>
    </List>
</DistributionLists>

 
 
 

XML Document nodes reference examples

  • DistributionLists [FirstChild]
    • List [ChildNodes(0)]
      • Name: Recon [ChildNodes(0).ChildNodes(0).innerText]
      • TO: John;Bob;Rob;Chris [ChildNodes(0).ChildNodes(1).innerText]
      • CC: Jane;Ashley
      • BCC: Brent
    • List
      • Name: Safety Metrics
      • TO: Tom;Casper
      • CC: Ashley
      • BCC: John
    • List [ChildNodes(1)]
      • Name: Performance Report [ChildNodes(1).ChildNodes(0).innerText]
      • TO: Huck;Ashley
      • CC: Tom;Andrew
      • BCC: John;Seema

 
 
 

XPath in VBA

Instead of traversing the elements/nodes in your XML using the .ChildNodes/.FirstChild/NextChild properties we can also use XPath. XPath is a query language used for selecting XML nodes in an XML document. It is represented by a single string. It allows you to extract any number of nodes (0 or more) which match the specified XPath query.

If you want to learn XPath I can recommend this overview:
http://www.w3schools.com/xpath/xpath_syntax.asp

Now let’s jump into an example:

Example 1: Extract all Lists

Sub TestXML()
    Dim XDoc As Object
    
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load (ThisWorkbook.Path & "\test.xml")
    
    Set lists = XDoc.SelectNodes("//DistributionLists/List")

    Set XDoc = Nothing
End Sub

Example 2: Extracting all TO fields

Set toFields = XDoc.SelectNodes("//DistributionLists/List/TO")
End Sub

Example 3: Extracting the first and last Name field

Set firstNameField = XDoc.SelectNodes("//DistributionLists/List[0]/Name")

Set lastNameField = XDoc.SelectNodes("//DistributionLists/List[2]/Name")

Example 3: Extracting all child List nodes (Name, TO, CC, BCC)

Set listChildrenField = XDoc.SelectNodes("//DistributionLists/List/*")

XML Attributes in VBA

Let’s tackle one last example – attributes. Let’s slightly modify the XML above and include an example attribute named attribute.

<?xml version="1.0" encoding="utf-8"?>
<DistributionLists>
    <List>
        <Name attribute="some">Recon</Name>

Using XPath (or traversing the DOM) we can easily extract the attribute as shown below.

Set firstNameField = XDoc.SelectNodes("//DistributionLists/List[0]/Name")
Debug.Print firstNameField(0).Attributes(0).Text
'Result: "some"

Creating XML documents

Creating documents is also quite straight forward in VBA.

Dim XDoc As Object, root As Object, elem As Object
Set XDoc = CreateObject("MSXML2.DOMDocument")
Set root = XDoc.createElement("Root")
XDoc.appendChild root
 
'Add child to root
Set elem = XDoc.createElement("Child")
root.appendChild elem
    
'Add Attribute to the child
Dim rel As Object
Set rel = XDoc.createAttribute("Attrib")
rel.NodeValue = "Attrib value"
elem.setAttributeNode rel
    
'Save the XML file
XDoc.Save "C:\my_file.xml"
survey in word

Making a VBA Automated Survey in Word

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Recently I stumbled upon an issue of creating a quick survey for clients/users. Usually you would want surveys set up in a dedicated web application like Google Forms or Survey Monkey. However, in this case I was limited to MS Word – the doc / docx survey was to be sent via email to clients/users and consolidated. Seemed like a lot of manual work. But why bother when you can use VBA? I created the survey using MS Word ContentControls (text fields, checkboxes etc.) and ActiveX objects (for option / radiobuttons only). Collecting the results was quite easy using VBA. The code is reusable for virtually any survey created this way. Below I explain my approach of making a survey in Word and how to easily consolidate the results using VBA macros (next page of this post).

Designing the survey

Most people don’t put enough effort into designing their surveys – sloppy designs, messed up document structures or simply frequent typo’s and misspelled questions. All these will heavily deteriorate the quality of your survey. Skip this section if you want just the solution/code. If you are still developing your survey, however, I encourage you to at least review the steps for designing a good survey.

Survey in Word: Well structured question survey in Word question
Example: Well structured survey question

When designing your survey always start with the following:

  • Draft your targets/objectives – what do you want to achieve with your survey? What information do you want to collect? Make a list of your objectives
  • Develop you questions and map them to your list of objectives for completeness. Remove questions that do not map to any objectives. Check if there are any duplicates – questions that provide results for the same objectives in a similar way. Are there any objectives not covered by a single question?
  • Review your list of questions – limit the survey to a reasonable amount of questions. Make sure your survey is balanced – not too many questions for a single objective
  • Decide each type of question – should they be open questions (text) and which ones should have a limited set of answers (multiple or single choice)
  • Draft answers to closed questions
  • Review all answers for completeness. Standardize questions with scales. Make sure to organize the answers and sort them appropriately
  • Make sure the survey looks clean and structured
  • Spelling and grammar – make sure there are no no typos/misspelled words etc.

Insert Word form controls

When you have completed drafting your survey and structuring it you can move on to adding the actual form controls to make you survey interactive and enable automatic results extraction.

Inserting form controls to Word
Inserting form controls to Word

Here is a list of different mapping types of question to the various controls you may use:

Type of question Form control to use Instructions
Open questions
  • Rich Text Content Control
  • Plain Text Content Control
  • Set the TAG and TITLE to be able to extract answers automatically
  • Use the Rich control only if you want the user to be able to add formatting. I would, however, advise going with the Plain control.
Open questions – Dates
  • Date Picker Content Control
  • Set the TAG and TITLE to be able to extract answers automatically
Singlechoice closed questions
  • Option Button (ActiveX Controls) / Radiobuttons
  • Combo Box Content Control / ListBox
  • Dropdown List Content Control / DropDown
  • Set the TAG and TITLE to be able to extract answers automatically
  • Use the Option Buttons when you have a small set of answers e.g. max 5. Use the Combo Box or Dropdown controls for questions will a large set of questions
  • When using Option Buttons (Radiobuttons) be sure to set the same GroupNameto all Option Buttons for all answers within a single question
Multichoice close question
  • Check Box Content Control
  • Set the TAG and TITLE to be able to extract answers automatically

Below a short example of how to correctly configure a Option Button (Radiobutton). Be sure to name the control appropriately and assign the same GroupName to all answers of a single set.

Option Button (ActiveX Control): Configuring
Option Button (ActiveX Control): Configuring

Be sure to test the survey and double-check for any typos and other editing errors twice before dispatching it to your respondents.

Automatically consolidating the results

Now that you have your survey ready it’s time for consolidating the results. If you used the form controls mentioned above you can quickly consolidate your results using the following VBA macro.

Sub ExtractResults()
    Dim folder As String, currFile As String, col As Long, _
        ws As Worksheet, row As Long, isHeader As Boolean, currDoc As Object, wApp As Object
    Set wApp = CreateObject("Word.Application")
    On Error GoTo Finally
    'Get folder with survey results
    folder = BrowseForFolder(ActiveWorkbook.Path)
    Set ws = ActiveSheet

    currFile = Dir(folder & "/")
    'Loop through survey results
    row = 2: col = 1: isHeader = False
    Do While currFile <> ""
        Set currDoc = wApp.Documents.Open(folder & "/" & currFile, ReadOnly:=True)
        LoadFile ws, currDoc, isHeader, col, row
        currDoc.Close SaveChanges:=False
        isHeader = True
        row = row + 1
        col = 1
        currFile = Dir()
    Loop
Finally:
    wApp.Quit
End Sub
Sub LoadFile(ws, currDoc As Object, isHeader As Boolean, col As Long, row As Long)
    Dim contCtrl
    'Section for content controls
    For Each contCtrl In currDoc.ContentControls
        If Not isHeader Then ws.Cells(1, col).Value = contCtrl.Title
        'Text content controls
        If contCtrl.Type = 0 Or contCtrl.Type = 1 Then ws.Cells(row, col).Value = contCtrl.Range.Text
        'Checkbox
        If contCtrl.Type = 8 Then ws.Cells(row, col).Value = contCtrl.Checked
        'Dropdown or Listbox
        If contCtrl.Type = 3 Or contCtrl.Type = 4 Then ws.Cells(row, col).Value = contCtrl.Range.Text
        'Date
        If contCtrl.Type = 6 Then ws.Cells(row, col).Value = contCtrl.Range.Text
        col = col + 1
    Next contCtrl

    'Section for ActiveX radiobuttons (add more if needed)
    For Each fItem In currDoc.Fields
        If fItem.OLEFormat.ClassType = "Forms.OptionButton.1" Then
            If Not isHeader Then ws.Cells(1, col).Value = fItem.OLEFormat.Object.Name
            ws.Cells(row, col).Value = fItem.OLEFormat.Object.Value
        End If
        col = col + 1
    Next fItem
End Sub
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
    Dim ShellApp As Object
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0
    Set ShellApp = Nothing
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = ""
        If Not Left(BrowseForFolder, 1) = "" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select

    Exit Function
Invalid:
    BrowseForFolder = False
End Function
Consolidating results in Excel
Consolidating results in Excel

How to consolidate results

  • Make sure that all survey results are in a single folder and the consolidation Excel file is in a parent folder
  • Execute the ExtractResults VBA macro or click the Consolidate Survey Results in the Excel xlsm file above
  • Select the folder with the survey results
  • All results will be uploaded to the Excel file

Download example

Want an example of the above survey? Simply download the example below:

Conclusions for making a survey in Word

That’s it! Hopefully making a survey in Word will be much easier for you! I would especially like to stress on the design phase of your survey. As usually you will only get 1 hit for the survey, it is crucial to focus on you targets/objectives when drafting your questions.

Next Steps

Learn how to work with VBA in Word:
Word: Word VBA Tutorial

word vba tutorial

Word VBA Tutorial

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)
Loading...

Welcome to the Word VBA Tutorial. VBA is a great tool not only to be leveraged in MS Excel. Often it is worth to save some time doing repeatable tasks by adopting some VBA macros in Word or PowerPoint too. Today I wanted to focus a little bit on starting you off in Word VBA macro programming.

vba word tutorialWhen moving to macro programming in Word VBA you will stumble upon issues you would normally not expect in Excel. Lets take the example of moving around the Word file – in Excel you have spreadsheets which are easy to navigate around. In Word, however, you have a lot of different content e.g. text, objects like images and charts, tables etc. Navigating around Word file in VBA is the greatest challenge you will face. Today I would like to focus on that and other frequently used features in Word VBA. So let’s kick off this Word VBA Tutorial.

CONTENTS

WordVBA – navigating around Word documents, formatting etc.

word vba
Navigating / Moving
word vba
Text formatting
word vba
Tables

VBA Word Navigating

Let’s start with adding content to the most common places the start and end of a Word document in VBA. Know if you Google for this you will get tons of non-sense methods of navigating around Word files. I was truly amazed at how poorly it is documented.

Beginning and End of the Word Document

Go to the Beginning of a Word Document:

Go to the End of a Word Document:

Finding and replacing text in a Word Document with VBA

Finding and replacing text are basic functions that you will probably need to leverage every now and then.

VBA Word Text formatting

One of the first things you would want to do is probably text formatting in Word VBA.

Let’s start by adding some text to our document:

Bold & Italic

To change the font weight to bold see below:

To change the text decoration to italic see below:

Below the final result of the code above:

Word VBA Tutorial: Bold and Italic
The result: Bold and Italic

Font size and name

Using the “Hello World!” example above we can similarly change the text font name and font size as shown below:

VBA Word Tables

When editing Word files you might want to leverage tables as it is much easier to navigate around them in an automated way. My approach is to insert/modify tables in Word without and borders (invisible). This way you can guarantee a consistent and easy to navigate structure in Word. Let’s go through some of the basic functions around tables.

Add a table

Let’s add a table to the beginning of the Word document:

Edit cell text

Working on rows and columns

Formatting borders

In progress…