best way to create Word 2010 documents from Word 2003 templates with merge from SQL
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.
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.
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.
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.
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
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
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.
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.
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.
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.
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.
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.
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?
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
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?
What is the problem that you are trying to solve? Is there one?
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.
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.
What kind of field codes? If you hit Alt-F9, the field codes are displayed in full text. What do they look like?
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?
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?
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:
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
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.
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}
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
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
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial