1 / 18

Access-MS Word Integration

This document outlines innovative methods of integrating Microsoft Word with MS Access to streamline the creation of event brochures and other documents. The approach focuses on overcoming obstacles encountered in complex projects, ensuring rapid updates, and maintaining design integrity during document manipulation. It details the use of bookmarks, placeholders, and automated formatting to rapidly generate updated brochures while accommodating various event specifications. This practical integration strategy aims to enhance productivity for clients facing challenging project timelines.

tamika
Download Presentation

Access-MS Word Integration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 19.Nov.2012 Access-MS Word Integration By Barry Hynum, Ph.D. MaxQual Consulting, Inc. Multiware SW, Inc.

  2. MXQ Marketing Strategy • Goal is to take on harder projects • i.e. After the developer hits a wall • After painting oneself into a corner • Symptom: no progress for months/years • This project had apparently hit the wall in several dimensions… a multi-dimensional hypervolume

  3. Requirements & Approach • Assemble a “Proposal” from MS Access • Client templates called shells* • Bookmarks • Table place holders • Populate w/ event “brochure” documents* • Populate tables w/ MS Access data • Elements of the event for “adjustments” • Allow for rapid updates * Clients basic mode of operation. Brochures are art/designer intensive.

  4. Bookmarks used for place holders. (naming convention) Cvr_AE Cvr_AE_Email Cvr_AE_Title Cvr_AM Cvr_AM_Email Cvr_AM_Title Cvr_ClientName Cvr_ContactName Cvr_DateOfEvent Cvr_DateSent Cvr_EndClientName Cvr_Hotel Cvr_Office Co_Info Redacted

  5. Cover Page Bookmark Handlers Private Sub sb_InsertCoverPageData( _ ByRef oWd As Word.Application, _ ByRef oDoc As Word.Document) Dim oRng As Word.Range With oDoc '--- For non-table bookmarks, set alignment Set oRng = .Bookmarks("Cvr_ClientName").Range .Bookmarks("Cvr_ClientName").Range.Text = UCase(sClient4Title) oRng.Select oWd.Selection.Expand Word.WdUnits.wdLine oWd.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter .Bookmarks("Cvr_ContactName").Range.Text = sContact .Bookmarks("Cvr_DateOfEvent").Range.Text = rs!ProgramDates … .Bookmarks("Cvr_Hotel").Range.Text = Nz(rs!Hotel, "") … oRng.Select oWd.Selection.Expand Word.WdUnits.wdLine oWd.Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter oWd.Selection.Font.Size = 9 End With … End Sub

  6. Table of Contents Handler Private Sub sb_InsertTOC( _ ByRef oWd As Word.Application, _ ByRef oDoc As Word.Document) '--- TMM Top Level With oDoc.Styles("TOC 4") .AutomaticallyUpdate = True .BaseStyle = "Normal" .NextParagraphStyle = "Normal" End With With oDoc.Styles("TOC 4").Font .NAME = "Arial" .Size = 11 .Bold = True .Italic = True End With With oDoc .TablesOfContents(1).Range.Select .TablesOfContents(1).Delete .TablesOfContents.Add _ Range:=oWd.Selection.Range, _ RightAlignPageNumbers:=True, _ UseHeadingStyles:=True, _ UpperHeadingLevel:=4, _ LowerHeadingLevel:=6, _ IncludePageNumbers:=True, _ AddedStyles:="", _ UseHyperlinks:=True, _ HidePageNumbersInWeb:=True, _ UseOutlineLevels:=True .TablesOfContents(1).TabLeader = wdTabLeaderDots .TablesOfContents.Format = wdIndexIndent End With End Sub

  7. Boilerplate Pics & Bios

  8. Insert MS Word .doc file Handler Public Sub sb_MXQInsertFile( _ ByRef oWd As Word.Application, _ ByRef oDoc As Word.Document, _ ByRef rng As Word.Range, _ ByVal sFile As String) Dim oFS As New FileSystemObject Dim oSel As Word.Selection 'sFile Complete path to file If oFS.FileExists(sFile) Then rng.Select Set oSel = oWd.Selection oSel.InsertFile """" & sFile & """", , False oSel.Collapse Word.wdCollapseEnd oSel.InsertParagraph oSel.Collapse Word.wdCollapseEnd rng.End = oSel.End rng.Start = oSel.End '<< Range rtn'd is character after insert Else '--- No Insert File, use placeholder sb_LabelTable3 rng, "*** File NOT Available ***" sb_LabelTable3 rng, "*** Target File:" sb_LabelTable3 rng, vbTab & "[" & sFile & "]" rng.InsertParagraph rng.Collapse Word.wdCollapseEnd End If End Sub

  9. Category Wrapper Bookmark 6 Categories of Event Groupings Each can be replaced by Group or Event or by Package

  10. Insert Titles sBookmark = sBookmarkPrefix & "_Titles" Set rng = Doc.Bookmarks(sBookmark).Range rng.Delete sb_SetBookmark sBookmark, rng, Doc iRowCnt = 1 Set tbl = rng.Tables.Add(rng, 1, 1, wdWord8TableBehavior, wdAutoFitFixed) tbl.AutoFormat Word.WdTableFormat.wdTableFormatNone Set oRow = tbl.Rows(1) With oRow .Height = Wd.InchesToPoints(0.4) .Cells(1).Width = Wd.InchesToPoints(6.75) End With .MoveFirst Do Until .EOF 'iRowCnt = iRowCnt + 1 '--- Add Event Title tbl.Cell(iRowCnt, 1).Range.Text = !Event tbl.Cell(iRowCnt, 1).Range.Bold = True tbl.Cell(iRowCnt, 1).Range.Underline = False tbl.Cell(iRowCnt, 1).Range.Font.Size = 16 tbl.Cell(iRowCnt, 1).Width = Wd.InchesToPoints(6.75) tbl.Cell(iRowCnt, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter tbl.Rows(iRowCnt).Height = Wd.InchesToPoints(0.4) '--- Prep for next row Set oRow = tbl.Rows.Add iRowCnt = iRowCnt + 1 With oRow .Height = Wd.InchesToPoints(0.4) .Cells(1).Width = Wd.InchesToPoints(6.75) End With .MoveNext Loop '--- Add rows to top & bottom of table to fill page

  11. Packages, Events, Items Descriptions & Pricing Packages contain Events & Price Tables If a package needs to be updated It can simply be reconstituted

  12. Inserting an Event “Brochure” Document sFile = fn_GetRootFolder() & "TariffTemplates\" & Nz(!TariffEventFile, "") If oFS.FileExists(sFile) Then sb_MXQInsertFile Wd, Doc, rng, sFile oRngHdr.Start = oRngHdr.Start + 1 oRngHdr.End = oRngHdr.Start + 1 oRngHdr.Select Wd.Selection.Delete MoveForward1Line: oRngHdr.Start = oRngHdr.Start + 1 oRngHdr.End = oRngHdr.Start + 1 oRngHdr.Select Wd.Selection.Expand Word.WdUnits.wdLine If Wd.Selection.Characters.Count < 2 Then GoTo MoveForward1Line Wd.Selection.Paragraphs.OutlineLevel = wdOutlineLevel5

  13. Only Package Categories 3, 7 & 8 Have Price Tables. • Case 3, 7, 8 • '--- Insert Placeholder for PPP Table • rng.Bookmarks.Add "PPP" & !TariffEventID, rng • rng.InsertParagraph • rng.Collapse Word.wdCollapseEnd • rng.InsertParagraph • rng.Collapse Word.wdCollapseEnd

  14. iRowCnt = iRowCnt + 1 Set oRow = tbl.Rows(iRowCnt) oRow.Height = mWd.InchesToPoints(2 / 16) oRow.Range.Font.Size = 8 tbl.Rows.Add '--- Add Item Description iRowCnt = iRowCnt + 1 Set oRow = tbl.Rows(iRowCnt) oRow.Height = mWd.InchesToPoints(3 / 16) '--- Add Item Title & Price '!!! With does not work for Range tbl.Cell(iRowCnt, 1).Range.Text = !SupplierItemTariffTitle tbl.Cell(iRowCnt, 1).Range.Bold = True tbl.Cell(iRowCnt, 1).Range.Italic = False tbl.Cell(iRowCnt, 1).Range.Underline = False tbl.Cell(iRowCnt, 1).Range.Font.Size = 10 tbl.Cell(iRowCnt, 1).Width = mWd.InchesToPoints(6) tbl.Cell(iRowCnt, 2).Range.Text = Format(!SupplierItemUnitSell, "currency") tbl.Cell(iRowCnt, 2).Range.Bold = True tbl.Cell(iRowCnt, 2).Range.Italic = False tbl.Cell(iRowCnt, 2).Range.Underline = False tbl.Cell(iRowCnt, 2).Range.Font.Size = 10 tbl.Cell(iRowCnt, 2).Width = mWd.InchesToPoints(1) tbl.Cell(iRowCnt, 2).Range.ParagraphFormat.Alignment = wdAlignParagraphRight '--- Prep for next row tbl.Rows.Add Price Table Row Handler

  15. Inserting Images In some cases the client wanted Image inserts for items in a table.

  16. Insert Picture in Row Part 1 If !SupplierItemUseImage And (Not b4COS) Then sPicLocation = fn_GetRootFolder() & "SupplierItemGraphics\" & !SupplierItemImage '--- File Exists? If Not oFso.FileExists(sPicLocation) Then GoTo SkipPicture End If '--- Insert Pic & adjust Position Set oPic = tbl.Cell(iRowCnt, 1). _ Range.InlineShapes.AddPicture(sPicLocation, False, True, tbl.Cell(iRowCnt, 1).Range) oPic.Select With oPic sOrigHt = .Height .Height = mWd.InchesToPoints(1.33) .Width = .Width / (sOrigHt / .Height) End With Set oSel = mWd.Selection oSel.InlineShapes(1).ConvertToShape

  17. Insert Picture in Row Part 2 Swap Item Picture Left or Right Try_Selection_Again: With oSel.ShapeRange .Top = mWd.InchesToPoints(-0.02) If bLeftSide Then .Left = Word.wdShapeRight .WrapFormat.Side = Word.wdWrapLeft bLeftSide = False 'Switch next to right Else .Left = Word.wdShapeLeft .WrapFormat.Side = Word.wdWrapRight bLeftSide = True 'Switch next to left End If .WrapFormat.Type = wdWrapSquare End With ‘oSel.ShapeRange mWd.Selection.Collapse Word.wdCollapseEnd Set oPic = Nothing Set oSel = Nothing End If 'Image? SkipPicture: '--- END: Insert Picture in Row ---------------------------

  18. Project Abandoned! • Primary contact was a grunt • No executive oversight !!!! • (despite frequent & regular requests) • When the exec finally got involved • Wanted MUCH more than specified in Req. • “If it wasn’t in the original specification, WHY!?” See bullet 1 & 2 • “If it wasn’t in the original specification, YOU should have known!” See bullet 2 • Did not want to pay for further dev • Conclusion: Some people in charge should not be. • $80K, 12+ months of everything they wanted! • U.S. Model of Upper Management • It is not the cream that floats to the top in a septic tank

More Related