180 likes | 301 Vues
19.Nov.2012. Access-MS Word Integration. By Barry Hynum, Ph.D. MaxQual Consulting, Inc. Multiware SW, Inc. 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
E N D
19.Nov.2012 Access-MS Word Integration By Barry Hynum, Ph.D. MaxQual Consulting, Inc. Multiware SW, Inc.
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
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.
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
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
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
Boilerplate Pics & Bios
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
Category Wrapper Bookmark 6 Categories of Event Groupings Each can be replaced by Group or Event or by Package
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
Packages, Events, Items Descriptions & Pricing Packages contain Events & Price Tables If a package needs to be updated It can simply be reconstituted
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
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
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
Inserting Images In some cases the client wanted Image inserts for items in a table.
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
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 ---------------------------
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