[Excel VBA] How to Create an Empty XML File with an XML Declaration

Sometimes, you may need to generate an XML file from scratch using VBA, for example, when creating data to integrate with external systems. The first step in creating a well-formed XML document is to write the “XML Declaration” at the beginning of the file.

In this article, I will explain the basic method for creating an empty XML file that contains only the XML declaration (<?xml ... ?>) using the MSXML2.DOMDocument object in VBA.

What is the MSXML2.DOMDocument Object?

MSXML2.DOMDocument is a component provided by Microsoft that allows programs like VBA to handle XML data structurally (like a tree). By using this, you can perform operations such as reading XML, adding/editing/deleting elements, and saving files.

For more stable performance, it is recommended to use the specific version: MSXML2.DOMDocument.6.0.

Completed VBA Code

Below is the VBA code to create a file containing an XML declaration.

Sub CreateXmlFileWithDeclaration()

    ' Declare variables
    Dim xmlDoc As Object
    Dim xmlDeclarationNode As Object
    Dim outputXmlPath As String
    
    ' Specify the path where the XML file will be saved
    outputXmlPath = ThisWorkbook.Path & "\NewXMLFile.xml"

    ' Create the XML document object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")

    ' Work with the xmlDoc object using a With block
    With xmlDoc
        
        ' 1. Create the XML declaration node
        '    CreateProcessingInstruction("Target", "Content")
        Set xmlDeclarationNode = .CreateProcessingInstruction("xml", "version=""1.0"" encoding=""UTF-8""")
        
        ' 2. Add the created node to the document
        .AppendChild xmlDeclarationNode
        
        ' 3. Save as a file
        .Save outputXmlPath
        
    End With
    
    ' Release objects
    Set xmlDeclarationNode = Nothing
    Set xmlDoc = Nothing

    MsgBox "XML file creation completed."

End Sub

Code Explanation

1. Creating the XML Document Object

Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")

We use the CreateObject function to create the main document object for manipulating XML and store it in the xmlDoc variable.

2. Creating the XML Declaration Node

Set xmlDeclarationNode = .CreateProcessingInstruction("xml", "version=""1.0"" encoding=""UTF-8""")

This is the core part of creating the declaration. In XML specifications, the <?xml ... ?> tag is classified as a “Processing Instruction”. Therefore, we use the .CreateProcessingInstruction method.

• First Argument ("xml"): Specifies the target of the processing instruction. For an XML declaration, this is always “xml”.
• Second Argument ("version..."): Specifies the content as a string. To represent a double quote " inside a VBA string, you must use two double quotes "".

3. Adding the Node and Saving

.AppendChild xmlDeclarationNode
.Save outputXmlPath

The node created by .CreateProcessingInstruction initially exists only in memory. We use the .AppendChild method to add it as the first child element of the xmlDoc object.

Finally, the .Save method writes the current state of the xmlDoc object from memory to the file at the specified path.

The Generated XML File

When you run the VBA code above, a file named NewXMLFile.xml is created in the same folder as your Excel file. If you open this file with a text editor, you will see the following content:

<?xml version="1.0" encoding="UTF-8"?>

Summary

The foundation of generating XML files in VBA is manipulating the MSXML2.DOMDocument object.

The first step is to understand the flow: create the XML declaration using the .CreateProcessingInstruction method and add it to the document using .AppendChild. From here, you can add more element nodes to programmatically build complex XML structures.