Hi Jabba,
My Access DB creates weekly Checklists, my customer has a variable number of items in a daily, and weekly section and has an optional table at the bottom of the document. My program inserts a header and footer and then uses the entire remaining space with tables which contain checklist items. Inserts my branding and customer branding if available in the customer file. It used to take me 40 mins after entering the data to have my final document if the customer made a change (minor meltdown as another 30mins to fix - it now takes the computer under 3 mins to generate or regenerate and I can go and pick up a coffee while the computer is busy doing the work.
I have quickly gone through my code to get the main points, this is not a working copy but if you are in any way competent with VB in Access then you should be able to get operational very quickly. Below I have the elements of code to create a reference to MS Word and open a new blank document. How to modify a Style and Create a new Style, save the file, export to PDF, set page size and margins. If you have a specific how to question please feel free to get back to me.
To get the code as to how to perform a specific task I went back to my Manual documents, use the ‘Record a Macro’ and then manually do the individual tasks. Look at the resulting macro in word and edit to my needs. You need to put the ‘Wordapp.’ In front of the word macro lines where you see ‘SELECTION’ or ‘ACTIVEDOCUMENT’ when putting them into VBcode in access.
I have references to
Microsoft Word xx.x Object Library
Microsoft Office xx.x Object Library
Microsoft Activex Data objetcs x.x library
You also need to create and connect to your recordset which are accessed normally
‘Reference Word
Dim wordApp As Word.Application
Dim MyDoc As Word.Document
Dim mySection As Word.Section
Set con = Application.CurrentProject.Connection
Set wordApp = CreateObject("Word.application")
Set MyDoc = wordApp.Documents.Add
With MyDoc
.EmbedTrueTypeFonts = True
wordApp.Visible = True
‘if the resultant documents are very large then having these setting turned off improves performance, set them to true again before closing your program.
With wordApp.Options
.CheckGrammarAsYouType = False
.CheckGrammarWithSpelling = False
.CheckSpellingAsYouType = False
.SaveInterval = 0
End With
End With
‘PgOrientation comes from my database
With wordApp.ActiveDocument
With .PageSetup
.PaperSize = wdPaperA4
'create the landscape page
If PgOrientation = "L" Then
.Orientation = wdOrientLandscape
.TopMargin = 20 ‘These measurements are all in Points 1 cm = 28.346456693 points
.LeftMargin = CentimetersToPoints (.7) ‘7mm = 20 points approx
.HeaderDistance = 34
Else
'Create the portrait page
.Orientation = wdOrientPortrait
.TopMargin = 20
.LeftMargin = 34
.HeaderDistance = 20
End If
.SectionStart = wdSectionNewPage
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.VerticalAlignment = wdAlignVerticalTop
.BottomMargin = 20
.RightMargin = 20
.FooterDistance = 20
End With
‘Edit an existing style
'Modify Normal Style - regular document text
With .Styles("Normal")
With .Font
.Name = "Arial Narrow"
.Size = 10
.Underline = wdUnderlineNone
.UnderlineColor = wdColorAutomatic
.Color = wdColorAutomatic
End With
With .ParagraphFormat
.SpaceBefore = 0
.SpaceAfter = 0
.LineSpacingRule = wdLineSpaceSingle
.Alignment = wdAlignParagraphLeft
.FirstLineIndent = 0
.OutlineLevel = wdOutlineLevelBodyText
.LineUnitBefore = 0
.LineUnitAfter = 0
End With
End With
'Create a new style
With .Styles.Add("Normal Indent1").Font
.Name = "Arial Narrow"
.Size = 10
.Underline = wdUnderlineNone
.UnderlineColor = wdColorAutomatic
.Color = wdColorAutomatic
End With
With .Styles("Normal Indent1").ParagraphFormat
.LeftIndent = 85
.SpaceBefore = 0
.SpaceAfter = 2
.LineSpacingRule = wdLineSpaceSingle
.Alignment = wdAlignParagraphLeft
.FirstLineIndent = -85
.OutlineLevel = wdOutlineLevelBodyText
.LineUnitBefore = 0
.LineUnitAfter = 0
End With
End With
‘this line types my customers address on a single line
wordapp.Selection.TypeText Text:=rs2!CustName & (", " + rs2!CustAddress1) & (", " + rs2!CustAddress2) & (", " + rs2!CustTown) & (", " + DLookup("County", "Counties", "Countyid = " & rs2!CountyId))
wordapp.Selection.TypeParagraph ‘create a new para
‘Apply a style - Always type the text first and apply the style after
wordapp.Selection.Style = ("Heading 1")
‘insert a Word Date field (Insert Date/Time from the word menus)
wordapp.Selection.Fields.Add Range:=wordApp.Selection.Range, Type:=-1, Text:="DATE \@ ""yyyy"" ", PreserveFormatting:=True
‘Save the file
wordApp.ActiveDocument.SaveAs2 FileName:= “My file Path\My New File Name.docx”
‘Export to PDF
wordApp.ActiveDocument.ExportAsFixedFormat OutputFileName:='Path\fileame', ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
wdExportOptimizeForPrint, Range:=wdExportAllDocument, FROM:=1, To:=1, Item:=wdExportDocumentContent, IncludeDocProps:=False, KeepIRM:=False, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:=False, UseISO19005_1:=False
'Exit and close and clean up
wordapp.activedocument.close 'options to save changes or blank for user prompts
wordapp.quit
set wordapp = nothing