Creating Word or XML document with VBA

Question

I need to create documents, (~150 reports, letters) mostly from pre-existing snippets and local databases, with VBA, in Word 2003. I then need to change specific parts of those snippets, such as dates, phrases, user information, &c. There are also, obviously, some bits that I will generate in my program.

Currently, this same task is done (sort of) by some hideous legacy VBA. (Nested IFs and FORs, and over 200 textboxes all called TextBox#) It gets the job done SOMETIMES, and does so by sending instructions to the Word application (Selection.MoveDown Unit:wdLine, Count:=1, for example.) Consequently, generating even a simple letter takes 30 seconds, and locks up Office.

I'm perfectly able to do the same thing with ranges and bookmarks and improved logic, but my gut feeling is that this should be easy to do this with XML and that doing so will have advantages in the future, as these reports/letters are to be accessed, and likely read programmatically, many times by many different users and applications.

I have been reading about XML and the WordML schema, but feel like I'm missing something.

What I want the code to do on Submit is this:

1. Grab predefined xml snippets (header, footer, etc.)
2. Generate new strings and concatenate predefined XML strings
3. Save as XML
4. Change value of existing tags in XML file (date, username, etc)

I can do this.

The issues I have are:

• I been having trouble getting my head around schema and namespaces. The data is already validated by the VBA code, so what do I need a schema for?

• Is it better to create the document by creating one long concatenated string containing the XML for the entire document, bearing in mind in my model much of the XML would be from existing snippets, or to use the XML Dom and create it programmatically:

20 Set oElementName = oDOM.createElement("Name")

30 oElement.appendChild oElementName

40 oElementName.Text = "This is the text of name"

• HOW DO I STYLE THE DOCUMENT? (This has been causing me the most trouble, conceptually.) I don't particularly want to reverse engineer it from the WordML that Word generates when it saves. I take it I need to convert it to HTML first? Can I create a stylesheet in Word so it automatically formats it?

Does any of this make sense? Should I just use ranges and bookmarks instead?

XML seems powerful to me, but it could be a twisted case of the hammer and nail problem, i.e. "I want to learn Hammer, so every problem looks like a nail, giving me a good reason to learn Hammer."

(I know I shouldn't be learning how to do things as I do them! But I'm just a mindless drone who complained enough about the quality of our document generation system that they said "Shut up and fix it then! Oh, you don't know any VBA? Well, we can't afford to send you on a course or anything, so learn as you go." It's been the best and worst 2 months of my working life.)

I appreciate you reading through all of that, if you did, and any help/advice you might have!

Answer 1

I don't know the circumstances of your assignment and there may be good reasons to move ahead with your technology stack, but it seems that such a big investment (150 reports is big) into Office 2003 should be reconsidered. While Microsoft is saying that VBA will not go away, I think that the Open XML SDK has a brighter future.

Here is an excellent introduction into the XML markup.

Answer 2

Okay, so I've found an answer to the styling part of my problems. (I always find these things shortly after I submit questions!)

And having read this it makes MUCH more sense to me!

From: http://www.tkachenko.com/blog/archives/000024.html

<?xml-stylesheet type="text/xsl" href="style.xsl"?>
<chapter title="XSLT Programming">
    <para>It's <i>very</i> simple. Just ask <link
url="http://google.com">Google</link>.</para>
</chapter>

Then XSLT stylesheet (quite big one due to verbose element-based WordML syntax):

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:w="http://schemas.microsoft.com/office/word/2003/2/wordml">
    <xsl:template match="/">
        <xsl:processing-instruction 
name="mso-application">progid="Word.Document"</xsl:processing-instruction>
        <w:wordDocument>
            <xsl:apply-templates/>
        </w:wordDocument>
    </xsl:template>
    <xsl:template match="chapter">
        <o:DocumentProperties>
            <o:Title>
                <xsl:value-of select="@title"/>
            </o:Title>
        </o:DocumentProperties>
        <w:styles>
            <w:style w:type="paragraph" w:styleId="Heading3">
                <w:name w:val="heading 3"/>
                <w:pPr>
                    <w:pStyle w:val="Heading3"/>
                    <w:keepNext/>
                    <w:spacing w:before="240" w:after="60"/>
                    <w:outlineLvl w:val="2"/>
                </w:pPr>
                <w:rPr>
                    <w:rFonts w:ascii="Arial" w:h-ansi="Arial"/>
                    <w:b/>
                    <w:sz w:val="26"/>
                </w:rPr>
            </w:style>
            <w:style w:type="character" w:styleId="Hyperlink">
                <w:rPr>
                    <w:color w:val="0000FF"/>
                    <w:u w:val="single"/>
                </w:rPr>
            </w:style>
        </w:styles>
        <w:body>
            <w:p>
                <w:pPr>
                    <w:pStyle w:val="Heading3"/>
                </w:pPr>
                <w:r>
                    <w:t>
                        <xsl:value-of select="@title"/>
                    </w:t>
                </w:r>
            </w:p>
            <xsl:apply-templates/>
        </w:body>
    </xsl:template>
    <xsl:template match="para">
        <w:p>
            <xsl:apply-templates/>
        </w:p>
    </xsl:template>
    <xsl:template match="i">
        <w:r>
            <w:rPr>
                <w:i/>
            </w:rPr>
            <xsl:apply-templates/>
        </w:r>
    </xsl:template>
    <xsl:template match="text()">
        <w:r>
            <w:t xml:space="preserve"><xsl:value-of 
select="."/></w:t>
        </w:r>
    </xsl:template>
    <xsl:template match="link">
        <w:hlink w:dest="{@url}">
            <w:r>
                <w:rPr>
                    <w:rStyle w:val="Hyperlink"/>
                    <w:i/>
                </w:rPr>
                <xsl:apply-templates/>
            </w:r>
        </w:hlink>
    </xsl:template>
</xsl:stylesheet>