It's important to note that this is a Module but not a Class. It can be called without being instantiated first.
Here are the first few functions:
Code:
Option Explicit
Enum reportTypes
CustomerDetail = 1
Addressing = 2
End Enum
Public Function Test()
BuildReport "Detailed Report", CustomerDetail
End Function
Public Sub BuildReport(fileName As String, report As reportTypes)
Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Set wordApp = New Word.Application
Set wordDoc = wordApp.Documents.Add()
SetLayout wordDoc, wordApp
wordDoc.Tables.Add wordDoc.Paragraphs(wordDoc.Paragraphs.Count).Range, 1, 1
Dim tablePosition As Integer
tablePosition = wordDoc.Tables.Count
Select Case report
Case CustomerDetail:
BuildCustomerDetailReport wordDoc
Case Addressing:
BuildAddressingReport wordDoc
End Select
Dim exportPath As String
exportPath = "" 'your path here
wordDoc.ExportAsFixedFormat fileName & ".pdf" _
, wdExportFormatPDF, True, wdExportOptimizeForOnScreen, wdExportAllDocument, _
, , wdExportDocumentContent, False, True, wdExportCreateHeadingBookmarks _
, True, True, False
wordDoc.Close False
wordApp.Quit False
Set wordDoc = Nothing
Set wordApp = Nothing
End Sub
Private Sub SetLayout(wordDoc As Word.Document, wordApp As Word.Application)
With wordDoc.Paragraphs.TabStops
.ClearAll
.Add position:=InchesToPoints(0.75), Alignment:=wdAlignTabRight
.Add position:=InchesToPoints(0.8), Alignment:=wdAlignTabLeft
.Add position:=InchesToPoints(4.5), Alignment:=wdAlignTabRight
.Add position:=InchesToPoints(4.55), Alignment:=wdAlignTabLeft
End With
'wordDoc.Paragraphs.LineSpacingRule = wdLineSpaceExactly
'wordDoc.Paragraphs.LineSpacing = 10
With wordDoc.PageSetup
.TopMargin = wordApp.InchesToPoints(0.2)
.BottomMargin = wordApp.InchesToPoints(0.5)
.LeftMargin = wordApp.InchesToPoints(0.5)
.RightMargin = wordApp.InchesToPoints(0.5)
End With
With wordDoc.Styles("Heading 2").Font
.Bold = True
.Size = 18
.name = "Calibri"
.Color = RGB(31, 73, 125)
End With
With wordDoc.Styles("Heading 3").Font
.Bold = True
.Size = 14
.name = "Calibri"
.Color = RGB(31, 73, 125)
End With
With wordDoc.Styles("Heading 4").Font
.Bold = True
.Italic = False
.Size = 12
.name = "Calibri"
.Color = RGB(31, 73, 125)
End With
With wordDoc.Styles("Heading 1").Font
.Bold = True
.Italic = True
.Size = 20
.name = "Calibri"
.Color = RGB(23, 54, 93)
End With
With wordDoc.Styles("Normal").Font
.Bold = False
.Size = 11
.name = "Calibri"
.Color = wdColorBlack
End With
End Sub
This works by creating a new, blank word document, setting styles, and then calling functions to build the appropriate report. Once the report is finished building it saves it as a PDF in the specified folder (you could add a reference to pass a path directly or code it in. We want all reports to always go to one folder so I hard coded it here). Then it closes the word document, closes Word, and unsets them. The SetLayout function is used to customize the different Headers and set tabs. This way, all reports follow a standardized layout.
If you just want to show the Word doc instead of saving it, wordApp.visible = true instead of killing the document and app should do the trick - and you can delete the ExportToFixedFormat function out of there.
I am still working on this - actually likely what I'll be doing for most of today - but here are some of the 'builder' functions I use:
Code:
Private Sub BuildCustomerDetailReport(wordDoc As Word.Document)
wordDoc.Paragraphs.Add
With wordDoc.Paragraphs(wordDoc.Paragraphs.Count)
.Alignment = wdAlignParagraphCenter
With .Range
.Bold = True
With .Font
.Size = 20
.Color = wdColorBlack
.name = "Calibri"
End With
.text = "Customer Management and Resources"
End With
End With
wordDoc.Paragraphs.Add
With wordDoc.Paragraphs(wordDoc.Paragraphs.Count)
.Alignment = wdAlignParagraphCenter
With .Range
.Bold = True
With .Font
.Size = 14
.Color = wdColorBlack
.name = "Calibri"
End With
.text = [redacted]
End With
End With
CreateCustomerTier "Platinum", wordDoc
CreateCustomerTier "Gold", wordDoc
CreateCustomerTier "Silver", wordDoc
End Sub
Private Sub CreateCustomerTier(custTier As String, wordDoc As Word.Document)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim def As DAO.QueryDef
Set db = CurrentDb
Set def = db.QueryDefs![qryReport_CustomerDetails_PARAM]
def.Parameters![tier] = custTier
Set rs = def.OpenRecordset
rs.MoveFirst
Delimit wordDoc, rs!strTier & " Customers", HeadingOne
wordDoc.Tables.Add wordDoc.Paragraphs(wordDoc.Paragraphs.Count).Range, 1, 4
Dim customerTable As Integer
customerTable = wordDoc.Tables.Count
Dim currentColumn As Integer
Do Until rs.EOF
With wordDoc.Tables(customerTable)
currentColumn = currentColumn + 1
If currentColumn = 5 Then
currentColumn = 1
.Rows.Add
End If
.Cell(.Rows.Count, currentColumn).Range.text = rs!strCustName
.Cell(.Rows.Count, currentColumn).Range.Style = "Heading 3"
End With
CreateHeader wordDoc, rs
FillCustomerTables wordDoc, rs!strCustName
rs.MoveNext
Loop
rs.Close
End Sub
That last function, CreateCustomerTier, is probably most relevant to your exact question - but it seemed like a broader answer would be helpful to you.
If you're already successfully launching a template document, you need to know several things:
Which table are you manipulating and how to reference it - in this case it is almost always working on the last created table. Note though that the first table created in each "CreateCustomerTier" function is tracked and added to as the report builds each page.
You also need to know which cell of the table you want to populate, what row and column it's in.
Hope that helps 
if nothing else, I know just seeing context for some of these calls can be immensely helpful for me.
Windows 7 64bit 
Access 2013
Reply With Quote
Originally Posted by DepricatedZero
