Using VBA to merge complex data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JBob - 27 Feb 2005 15:08 GMT

I have a Word XP document with merge codes (converted from WP). The data
source is several tables from my database. Rather than construct a complex
query to get all of the data into a single table to use as my merge data
source, I want to use VBA to populate the document. How can I identify the
Mergefields in code to achieve this?

Reply to this Message

Doug Robbins - 27 Feb 2005 22:17 GMT

I would suggest that creating a query to combine the data from the tables
would be far simpler than what you are proposing.

Signature

Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

>I have a Word XP document with merge codes (converted from WP). The data
>source is several tables from my database. Rather than construct a complex
>query to get all of the data into a single table to use as my merge data
>source, I want to use VBA to populate the document. How can I identify the
>Mergefields in code to achieve this?

Reply to this Message

JBob - 28 Feb 2005 01:15 GMT

I am able to use the following construct to achieve what I need (that is,
using VBA to populate merge fields):

'
'-- replace all occurrences of Analyst (mergefield in main document)
'
sSQL = "select FirstName, LastName, FullTitle from Employees where
Initials='" & sAnalystInitials & "'"
rsADO.Open sSQL, gsConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
sAnalyst = rsADO!Firstname & " " & rsADO!lastname & ", " & rsADO!FullTitle
rsADO.Close
Dim fieldLoop As Field
For Each fieldLoop In ActiveDocument.Fields
If InStr(1, fieldLoop.Code.Text, "Analyst", 1) Then
fieldLoop.Select
Selection.TypeText sAnalyst
End If
Next fieldLoop

Some Questions:
1. Is there a better way?
2. Am I in the right newsgroup for this problem?

>I would suggest that creating a query to combine the data from the tables
>would be far simpler than what you are proposing.
[quoted text clipped - 4 lines]
>>merge data source, I want to use VBA to populate the document. How can I
>>identify the Mergefields in code to achieve this?

Reply to this Message

Rate this thread: