How to import data from sql into Word custom properties with vba

0
votes

I'm evaluating ways of importing field data from a sql database into Word. Each field has small amounts of text in them for company name, legal status, address line, etc. I can pull it into a table, but I don't want the user to see it. I would really like to import the data into a set of custom properties in a template, and then use those to reference in the Word doc using codes. Does anyone have any links or examples that could help me do that?

I have no problem in doing the custom properties side of things. I am not sure how I can get the data into a custom property.

You help would be appreciated.

I haven't found a satisfactory solution to this, and I need to keep it all in VBA, given a few specific restrictions by the client.

I now know that the source is a MSSQL2008 server. I want to source company address and level details in the table, but ideally I want to pot these details into a set of custom properties in the document. I can then reference these into the document, and update the custom properties when needed.

1
sqlvbams-word
Sounds like a Word Mail Merge to me – cha
The term sql database is vague. Lot's of database engines use sql. Which one are you using? – Dan Bracuk
Good question. I don't know exactly at the moment. This request is part of a proposal document I am evaluating, so the information is a little vague until the next stage. I have set up a MySQL database to play with, as I at least need to know what the general issues are. This is one small part of a very large list of things, all of which I am familiar with. – Boyplunder
It may well be Microsoft SQL Server, given some other vague references elsewhere in the document. – Boyplunder

1 Answers

2
votes

You can do it using an add-in written in for instance C#. With VSTO, you can easily and reliably download facts from most databases and put them in custom properties. An alternative is the use of Invantive Composition, which allows you to do this by specifying it in a model of the template, see this model definition: model definition. Please note that I work at the company that made it, so I'm biased.

If you want to go down the road with C#, use code similar to in for instance ApplicationUtility.cs of your project (code taken from Invantive Control, but put in public here for your convenience):

Getting property value

private object GetWordBuiltInProperty(_Word.Document wordDocument, _Word.WdBuiltInProperty property)
{
    try
    {
        if (wordDocument != null)
        {
            return wordDocument.BuiltInDocumentProperties[property].Value;
        }
    }

Or changing it

private void CreateDocumentProperty(_Word.Document wordDocument, string name, MsoDocProperties type, object value)
{
    Debug.Assert(wordDocument != null, "Word document cannot be null!");
    Debug.Assert(!string.IsNullOrEmpty(name), "Name cannot be null!");

    object property = this.GetDocumentProperty(wordDocument, name);

    if (property == null)
    {
        Type t = wordDocument.CustomDocumentProperties.GetType();

        t.InvokeMember("Add", BindingFlags.InvokeMethod, null, wordDocument.CustomDocumentProperties, new object[] { name, false, type, value });
    }
}

private void CreateOrReplaceDocumentProperty(_Word.Document wordDocument, string name, MsoDocProperties type, object value)
{
    Debug.Assert(wordDocument != null, "Word document cannot be null!");
    Debug.Assert(!string.IsNullOrEmpty(name), "Name cannot be null!");

    object property = this.GetDocumentProperty(wordDocument, name);

    if (property == null)
    {
        //
        // Add property.
        //
        this.CreateDocumentProperty(wordDocument, name, type, value);
    }
    else
    {
        //
        // Set value.
        //
        this.SetDocumentPropertyValue(wordDocument, name, value);
    }
}
    private void SetDocumentPropertyValue(_Word.Document wordDocument, string name, object value)
    {
        object property = this.GetDocumentProperty(wordDocument, name);

        if (property != null)
        {
            Type t = property.GetType();

            t.InvokeMember("Value", BindingFlags.SetProperty, null, property, new object[] { value });
        }
    }