How can I export Form and subform (datasheet) to word template ?
Hi,
I would need to export a main form and subform (ONE subform is a datasheet with multiple entries\records) to a word template. The main and subform form exports fine by using bookmarks -as shown below- .
However, I'm struggling with exporting all the records from the subform (datasheet with multiple records).
The subform name is (frm00_tblScheduleT) and the requited field to be exported is :
- PaymentNumber
- DueDateG
- AmountDue
The number of records in this subform is varies based on number of repayments, it might be x record and up to xx records.
Any help on this would be greatly appreciated.
A sample of the code used below.
I would need to export a main form and subform (ONE subform is a datasheet with multiple entries\records) to a word template. The main and subform form exports fine by using bookmarks -as shown below- .
However, I'm struggling with exporting all the records from the subform (datasheet with multiple records).
The subform name is (frm00_tblScheduleT) and the requited field to be exported is :
- PaymentNumber
- DueDateG
- AmountDue
The number of records in this subform is varies based on number of repayments, it might be x record and up to xx records.
Any help on this would be greatly appreciated.
A sample of the code used below.
Private Sub cmdPrintContract_Click()
'Print customer Contract for current customer.
Dim appWord As Word.Application
Dim doc As Word.Document
Dim FileRef As String
'Avoid error 429, when Word isn't open.
On Error Resume Next
Err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'If Word isn't open, create a new instance of Word.
Set appWord = New Word.Application
appWord.Visible = True
End If
Set doc = appWord.Documents.Open("C:\Rent_Sys\Development_20150215\20150224_FINAL_CONTRACT_TEST.docx", , True)
FileRef = Me.Contract_Ref_ID ' Used to Save word doc name as Contract Number
With doc
.FormFields("fldContractNbr").Result = Me.Contract_Ref_ID
.FormFields("fldPart2Name").Result = Me.P2_FullName
.FormFields("fldProjectName").Result = Form!frm00_tblProjectUnit_sub!Project_Name_A
.FormFields("fldProjectUnitNBR").Result = Form!frm00_tblProjectUnit_sub!ProjectUnit_No
.FormFields("fldContractDateHijra").Result = Me.StartDHijri
.FormFields("fldContractDateGreg").Result = Me.StartDGreg
.FormFields("fldPart1Name").Result = Me.P1_FullName
.FormFields("fldPart1CR").Result = Me.P1_CR
.FormFields("fld_P1_CR_DATE").Result = Form!frm00_tblPart1_sub!P1_CR_Date
.FormFields("fldPart1Address").Result = Form!frm00_tblPart1_sub!P1_Address
.FormFields("fldPart1POBOX").Result = Form!frm00_tblPart1_sub!P1_POBox
.FormFields("fldPart1City").Result = Form!frm00_tblPart1_sub!P1_City
.FormFields("fldPart1ZipCode").Result = Form!frm00_tblPart1_sub!P1_ZIPCode
.FormFields("fldPart1Telephone").Result = Form!frm00_tblPart1_sub!P1_Phone
.FormFields("fldPart1FAX").Result = Form!frm00_tblPart1_sub!P1_Fax
.FormFields("fldPart1RepsName").Result = Form!frm00_tblPart1_sub!P1_Reps_Name
.FormFields("fldPart1RepsID").Result = Form!frm00_tblPart1_sub!P1_Reps_ID
.FormFields("fldPart1RepsPosition").Result = Form!frm00_tblPart1_sub!P1_Reps_Position
.FormFields("fldPart1RepsAuthority").Result = Form!frm00_tblPart1_sub!P1_Reps_Authority_Type
.FormFields("fldPart2Name_2").Result = Me.P2_FullName
.FormFields("fldPart2CR").Result = Me.P2_CR
.FormFields("fldPart2CRDate").Result = Form!frm00_tblPart2_sub!P2_CR_Date
.FormFields("fldPart2Address").Result = Form!frm00_tblPart2_sub!P2_Address
.FormFields("fldPart2POBOX").Result = Form!frm00_tblPart2_sub!P2_POBox
.FormFields("fldPart2City").Result = Form!frm00_tblPart2_sub!P2_City
.FormFields("fldPart2ZipCode").Result = Form!frm00_tblPart2_sub!P2_ZIPCode
.FormFields("fldPart2Telephone").Result = Form!frm00_tblPart2_sub!P2_Phone
.FormFields("fldPart2Mobile").Result = Form!frm00_tblPart2_sub!P2_Mobile
.FormFields("fldPart2Fax").Result = Form!frm00_tblPart2_sub!P2_Fax
.FormFields("fldPart2RepsName").Result = Form!frm00_tblPart2_sub!P2_Reps_Name
.FormFields("fldPart2RepsID").Result = Form!frm00_tblPart2_sub!P2_Reps_ID
.FormFields("fldPart2RepsPosition").Result = Form!frm00_tblPart2_sub!P2_Reps_Position
.FormFields("fldPart2RepsAuthorit").Result = Form!frm00_tblPart2_sub!P2_Reps_Authority_Type
.FormFields("fldPart2Activities").Result = Form!frm00_tblPart2_sub!P2_Activities
.FormFields("fldProjectName_2").Result = Form!frm00_tblProjectUnit_sub!Project_Name_A
.FormFields("fldProjectUnitNBR_2").Result = Form!frm00_tblProjectUnit_sub!ProjectUnit_No
.FormFields("fldProjectDistrict").Result = Form!frm00_tblProjectUnit_sub!Project_District
.FormFields("fldProjectStreet").Result = Form!frm00_tblProjectUnit_sub!Project_Street
.FormFields("fldProjectUnitNBR_3").Result = Form!frm00_tblProjectUnit_sub!ProjectUnit_No
.FormFields("fldSQM").Result = Form!frm00_tblProjectUnit_sub!ProjectUnit_SQM
.FormFields("fldNumOfYrs").Result = Me.NumberOfYears
.FormFields("fldStartHijri").Result = Me.StartDHijri
.FormFields("fldEndtHijri").Result = Me.EndDHijri
'.FormFields("fldRenewalNotice").Result =
.FormFields("fldLeaseAMT").Result = Me.LeaseAmount_No
.FormFields("fldLeaseAMTWORDS").Result = Me.LeaseAmount_Words
.FormFields("fldRepaymentPeriod").Result = Me.RePaymentPeriod
.FormFields("fldPayInAdvance").Result = Me.Pay_In_Advance_Month
.FormFields("fldP2CheqName").Result = Me.P1_FullName
.FormFields("fldInsuranceAMT").Result = Me.InsuranceAmount
.FormFields("fldInsuranceAMTWORDS").Result = Me.InsuranceAmount_Words
.FormFields("fldParking").Result = Form!frm00_tblProjectUnit_sub!ProjectUnit_Parking
.FormFields("fldPrepation").Result = Me.Preparation_NbrOfMonth
.FormFields("fldPrepationDate").Result = Me.Preparation_StartDateH
appWord.Visible = True
appWord.Activate
.SaveAs "C:\Rent_Sys\Development_20150215\" & FileRef
End With
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub
Who is Participating?
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
'...
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.
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.
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.
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.
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:
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)
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
Open in new window