How can I export Form and subform (datasheet) to word template ?

Question

Solutionaccess 2010 and word 2010 mailmerge using vba

Answer 1 · 2015-02-25T06:27:48-0800

GrahamSkanRetired

Trusted Expert

Top Expert 2012

Commented: 2015-02-25

This code should create a table in the document and step through the recordset adding and filling a row for each record.

'...
    Dim tbl As Word.Table
    Dim rs As Recordset 
'...
        .FormFields("fldPrepation").Result = Me.Preparation_NbrOfMonth
        .FormFields("fldPrepationDate").Result = Me.Preparation_StartDateH
        Set rs = Forms!MyMainform!MySubForm.Form.RecordsetClone ' needs the names of the main and the sub form in here
        .Unprotect 'password
        Set tbl = .Tables.Add(.Bookmarks("\EndOfDoc").Range, 1, 3)
        With tbl.Rows.First
            .Cells(1).Range.Text = "PaymentNumber"
            .Cells(2).Range.Text = "DueDateG"
            .Cells(3).Range.Text = "AmountDue"
        End With
        rs.MoveFirst
        Do Until rs.EOF
            Set rw = tbl.Rows.Add
            With tbl.Rows.First
                .Cells(1).Range.Text = rs.Fields("PaymentNumber")
                .Cells(2).Range.Text = rs.Fields("DueDateG")
                .Cells(3).Range.Text = rs.Fields("AmountDue")
            End With
            rs.MoveNext
        Loop
        rs.Close
        .Protect wdAllowOnlyFormFields ',password
    .SaveAs "C:\Rent_Sys\Development_20150215\" & FileRef
 '...

Open in new window

Answer 2 · 2015-02-25T08:21:56-0800

TAB-000

Author

Commented: 2015-02-25

Dear Graham,

First of all Thank you very much for your participation.

I've add the additional lines to vba. and It show only the last record from subform in the word document.

Could you please elaborate more if any steps needs to be done in the word documents and please how to?

Once again Thank you.

Answer 3 · 2015-02-25T08:50:49-0800

GrahamSkanRetired

Trusted Expert

Top Expert 2012

Commented: 2015-02-25

Oops. I see that I created a new row and then just write to the first.

'...
    Dim tbl As Word.Table
    Dim rs As Recordset 
'...
        .FormFields("fldPrepation").Result = Me.Preparation_NbrOfMonth
        .FormFields("fldPrepationDate").Result = Me.Preparation_StartDateH
        Set rs = Forms!MyMainform!MySubForm.Form.RecordsetClone ' needs the names of the main and the sub form in here
        .Unprotect 'password
        Set tbl = .Tables.Add(.Bookmarks("\EndOfDoc").Range, 1, 3)
        With tbl.Rows.First
            .Cells(1).Range.Text = "PaymentNumber"
            .Cells(2).Range.Text = "DueDateG"
            .Cells(3).Range.Text = "AmountDue"
        End With
        rs.MoveFirst
        Do Until rs.EOF
            Set rw = tbl.Rows.Add
            With rw
                .Cells(1).Range.Text = rs.Fields("PaymentNumber")
                .Cells(2).Range.Text = rs.Fields("DueDateG")
                .Cells(3).Range.Text = rs.Fields("AmountDue")
            End With
            rs.MoveNext
        Loop
        rs.Close
        .Protect wdAllowOnlyFormFields ',password
    .SaveAs "C:\Rent_Sys\Development_20150215\" & FileRef
 '...

Open in new window

Answer 4 · 2015-02-25T21:45:36-0800

TAB-000

Author

Commented: 2015-02-25

Dear Graham,

Wonderful !!

It works perfectly and extract all records from the subform to the word documents . But I have notice these records was placed at last page of the documents.

How can I relocate the results at specific place within the documents (say page 3 or Page xx or between two paragraph) ?

I understand if you want me to open a new topic to answer this point. But I seek your usual cooperation which is highly appreciated.

Thanks and regards.

Answer 5 · 2015-02-26T01:52:01-0800

GrahamSkanRetired

Trusted Expert

Top Expert 2012

Commented: 2015-02-26

You would need some way of identifying the position. I specifically chose the end of the document via its virtual bookmark.
In Word, the page numbers are fluid, so make an unreliable way of specifying the position. Obviously they can change if text is removed or inserted earlier in the document. Not so obvious is the fact that they can change with the current printer driver or even between versions of a font file.

If you are working with a pre-designed template, you can insert bookmarks beforehand and use them to position incoming text.

Here is how to insert after the third paragraph:

Dim rng As Range

Set rng = doc.Paragraphs(3).Range
rng.Collapse wdCollapseEnd
Set tbl = .Tables.Add(rng, 1, 3)

Open in new window

If you need more help, then yes, please post another question

How can I export Form and subform (datasheet) to word template ?

Premium Content

Premium Content

View Solution Only