We help IT Professionals succeed at work.

best way to create Word 2010 documents from Word 2003 templates with merge from SQL

GordonPrince
GordonPrince asked
on
I've inherited several hundred Word 2003 templates that are full of field codes. My users now have Word 2010. My data is in SQL-2005. I'm an experienced VBA & SQL programmer.

There are only about 25 fields in the 200 documents, so I'd like to get creating new documents from the templates to work as quickly as possible.

I have experience using Word templates with bookmarks, then running VBA to step through the bookmarks and paste data at each bookmark. But I haven't used merge with Word field codes for many years.

Any guidance from anyone who has done both about how to proceed?

1. Step through the template stopping at field codes and pasting data there (treat the field codes the same as bookmarks).
2. Convert all the templates replacing field codes with bookmarks, then using bookmarks as bookmarks.
3. Use VBA to do a Word merge setting the data source to be SQL with a where clause to only select the one row that should go into the document.

These are only going to be generated one at a time, on demand by users, the documents look to be mostly 1-3 pages long, so I don't think performance is going to be an issue. I would like to be able to reuse the documents in as close to the state they have been given to me as possible (jto get into production as quickly as possible), so I'm wondering if anyone has enough experience to offer guidance on the best way to go at this.

Thanks.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
the bookmarks idea is pretty solid.. and works.. you can use vba easily for it..

you can also go for a bit more robust and try with reporting services which can output to word as well. but that's more graphical than just text.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

what exactly do you want to change? Field codes work the same in Word 2010. Why not just save the files in the new format?

cheers, teylyn
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
It isn't clear what you are trying to achieve. Documents and templates written in Word 2003 should still work the same in Word 2010.

What sort of fields are they? If they are Merge fields, there doesn't seem to be much point in replacing them with bookmarks and then writing something to fill them from a database. Mail merge will work directly.

Author

Commented:
It looked to me like Word 2003 field codes should work in Word 2010 documents. I thought there might be better mail merge options in Word 2010, but maybe nothing new has happened there, either.

They are merge fields, not bookmarks. So I guess just do a merge with the one row of my SQL data. Are there options on how to connect to the data? ODBC? Other choices in Word 2010 that anyone has any experiences with? Maybe they're all the same with only one record and I'll just do whatever looks simplest to program.
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
There has long been potential for improvements to mail merge, but most changes have been to the interface.

You can use 'Edit Recipient List' to run a merge for a single record. There are other options on the dialogue. All the options work by editing the query sql that the merge uses.

You can use ODBC or OLEDB for the connection.

Author

Commented:
Ok, so how about this: work with the document's VARIABLES collection, which is all the fields in the document, it appears (shown in the code below).

The code appears to work, but the new variables don't display where the fields are in the Word document. Any ideas how to do that part?
Dim wordDoc As Word.Document
Dim wordVars As Word.Variables
Dim wordVar As Word.Variable
    Set wordDoc = appWord.Documents.Add(strDoc)
    Set wordVars = wordDoc.Variables
    For Each wordVar In wordVars
        wordVar.Value = rst.Fields(wordVar.Name)
        Debug.Print wordVar.Value
    Next wordVar

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
I still don't understand what you're trying to achieve. The Word 2003 merge files will work exactly the same when you save them as 2010 files.

What is the problem that you are trying to solve? Is there one?

Author

Commented:
It turns out the templates weren't created to do merges. They were created with a collection of field codes, but there's no data source in any of the documents. So trying to use them with my new database via merge isn't likely to work. Hence my change in direction.

What I'm trying to accomplish is: use the 200 document templates I've inherited (that are full of the same field codes, but it turns out for which there is no data source) with my new database. I want to use the SQL database record I'm currently displaying (via a Microsoft Access form) to create a new document. Using Access VBA, I  have an ID number that enables me to select exactly one row from a SQL query. I want to put the 25-50 fields from that SQL query into the 25-50 variables in any of my 200 Word templates, based on the template I choose, to create one new document. Then print & save the new document.

I'm trying to come up with a way to do this without having to edit all the existing templates.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
What kind of field codes? If you hit Alt-F9, the field codes are displayed in full text. What do they look like?

Author

Commented:
Here's a screen shot of what it the document looks like when the field codes are displayed. screenshot of field codes
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
OK, so these are DOCVARIABLE fields.

Question: What happens if these documents are opened with Word 2003? Will the fields update and show data?

Docvariables are normally set with VBA in the document.

Is there VBA code in the 2003 docs?
Retired
CERTIFIED EXPERT
Top Expert 2012
Commented:
Morning. AFAIK, the only way to update document variables is with code. Check in the document's template otr in the old Norma.dot. There is a possibility it is an .exe or VBA in another office product such as Access database or an Excel Workbook.

The code would be something like this:
Sub SetDocVariables(CaseID As Long)
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConn As String
    Dim docA As Document
    'Dim docvar1 As Variable
    
    Set docA = ActiveDocument
    
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFolder\DataBase1.mdb;User Id=admin;Password=;"
    cn.Open strConn
    
    rs.Open "Select * from MyCases Where [MyCases].[CaseId] = " & CaseID, cn, adOpenDynamic, adLockOptimistic, adCmdText
    If Not rs.EOF Then
        docA.Variables("_DIName") = rs.Fields("DIName")
        docA.Variables("_BankruptcyCaseNumber") = rs.Fields("CaseNumber")
        '''
    End If
    rs.Close
    cn.Close
    docA.Fields.Update
End Sub

Open in new window

Author

Commented:
Graham -- that's got it.

One small issue in the code though -- it looks like I should have a set the value for the document variable that's either "_" to "Blank", or something along those lines.

What I'm getting in the new document is the variable +
ERROR! NO DOCUMENT VARIABLE SUPPLIED.

I created document variable "Blank", "[Blank]", etc., but keep getting this error message in the new document for the fields with this IF statement in them. This looks like the last thing. Advice from an experienced document variable user much appreciated.



{IF {DOCVARIABLE "_D2Name"}="[Blank]"} "" 
"{DOCVARIABLE "_D2Name"}"\*
UPPER}

Open in new window

GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
That code tests the value of the document variable for "[Blank]". It looks like a mechanism to ensure that the fields in the True part of the IF are not set twice.

Also all the document variable names seem to start with an underscore. This may be because they were designed with some sort of automated document creation application, or it may be a style preference by the designer.

In the bits that we can see they are:

_CourtDistrict
_CourtState
_CourtDistrict
_CourtDivision
_D1Name
_D2_Name
_BankruptcyCaseNumber
_Chapter
_ClientName


Author

Commented:
So is it testing for a literal string (of what's inside the quotes) "[Blank]", do you think? In which case, I should send that literal string in the data, or set it inside my loop that sets the document variables from the recordset fields.

In my loop that assigns the variables from the data, I'm simply skipping the first character of all the variable names, as the recordset's fields don't have the underscore as their first character. Otherwise they all match up.

Author

Commented:
This makes it work.
wordVar.Value = Nz(.Fields(Mid(wordVar.Name, 2)), "[Blank]")

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.