Hi all,

Conceptually, how should I do this?

I've got a database with several hundred fields, mostly 1s or 0s that I want to correspond to checkboxes in a *portion* of a Word document template. I'm trying to create "pre-formatted layouts" for data presentation, like "apartment name", "address", "Clubhouse 1 or 0", "Pool 1 or 0", etc.

What I'd like for it to do is to say "Ok, there are X records in the data file, so insert X pages with my pre-formatted layout, dump the data in (labels, checkmarks, etc.) and that's it.

To make life simple, assume there's only one "name" field and one "1 or 0" field.

I'm just learning about merging, but all I can figure out (thus far) is that what I'm doing, in practice, is just making something that will print out to a single page, over and over and over, and not "inserting" the requisite number of pages and filling in all of the data.

I've got the basic layout started (not going further, until I can figure out if this will work or not). I copied the layout to another page and hit "view merged data" and no matter how I do it, it either shows row #2's data twice, or it shows row #2's and row #3's data (as I want it to do), but it "checks" the wrong boxes off, like it's not really pulling that row's data.

Is there a certain method of telling it to start putting in the next record's data for the second, third, ... page? Furthermore, is there a way [probably a macro-based solution] to call this one-page template file, insert X pages (with X being the number of data fields), and auto-filling in the data?

Sorry for the lengthy post. It took forever to GET the data, now I'm stuck manually checking off stuff unless I can figure something out. If you're totally lost w/o an example, I'll upload a simple doc and xls file of what I'm talking about.

Thanks, as always,

Hoop

I'm not really familiar with Word's Fields. You'd probably be better off asking on the Office forum. That said, I'll see if I can't quickly whip something up in VBA.

This isn't much better than PoC code, but it should point you in the right direction. I'm also using database objects to get the job done. Depending on how you have the data set up, it might be easier to use CreateObject("Excel.Application"). If you want THAT sample, let me know.

Sub DoJunk()
Dim oCon As Object
Dim oCom As Object
Dim oRS As Object
Set oCon = CreateObject("ADODB.Connection")
Set oCom = CreateObject("ADODB.Command")
oCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\book1.xls;Extended Properties=""Excel 8.0;HDR=Auto;IMEX=1"";"
oCon.Open
Set oCom.ActiveConnection = oCon
oCom.CommandText = "SELECT * FROM [Sheet1$]"
Set oRS = oCom.Execute

Do Until oRS.EOF
AddPage oRS
oRS.MoveNext
Loop
If oCon.State Then _
oCon.Close
Set oCon = Nothing
Set oCom = Nothing
Set oRS = Nothing
End Sub

Sub AddPage(ByRef oRS As Object)

'Preformatting goes here

Selection.TypeText oRS(0) & vbTab
With Selection.FormFields.Add(Selection.Range, wdFieldFormCheckBox)
.CheckBox.Default = (VarType(oRS(1).Value) <> vbNull) And (oRS(0) = 1)
End With
Selection.TypeParagraph

'Postformatting goes here
Selection.InsertBreak wdPageBreak
End Sub

Thanks, Razor2.3

That works, but it only pulls in the first column's data and posts a checkbox.

What I've got is a word document with all of the mail merge fields created and ready to drop data in (put data ID # here, address there, name over here, check off this checkbox if the next column is a 1 or a 0, another checkbox, ...).

I guess what I'm looking for is code to

1) Open template document and copy its template/layout and its merge fields

2) "Propogate" [word's terminology] the layout and fields layout into an existing document, adding X number of pages for X number of records in the database

3) Close template document

Part of that process is, I'm thinking, browsing for the template document's location and name, instead of it being a "static" database file, because the database will change all of the time.

Anywho...

A copy of a simple document template and excel database are uploaded at: http://www.24hourgames.com/mailmerge/

Directory is unprotected for viewing.

data.xls is a simple database showing the types of fields (names, addresses, whether it has a ____ or not, etc.)
datadocumenttemplate.doc is the simplified layout.