BLOG CATEGORIES



VB Macro Library useful for RPE

Seeing that a lot of RPE’s users have a minimal or none VB knowledge, I’ve decided to create a small library with some of the most used macros in RPE. We thought that will be a good idea to have all the VB Macros for RPE stored in a single place. Not all the macros are not the property of GEBS Reporting
Each macro will have associated a short description and below the pure VB code. The macros list will be continuously improved, and each week will try to update this list with new macros or with requested macros.

1. Updates ToC

This macro will update all the Table of Contents from the active document

Sub updateTOCs()
    On Error Resume Next
    Dim toc As TableOfContents
    For Each toc In ActiveDocument.TablesOfContents
        toc.Update
        toc.UpdatePageNumbers
    Next
End Sub

2. Updates ToF

This macro will update all the Table of Figures from the active document

Sub updateTOFs()
    On Error Resume Next
    Dim tof As TableOfFigures
    For Each tof In ActiveDocument.TablesOfFigures
        tof.Update
        tof.UpdatePageNumbers
    Next
End Sub

3. Updates ToT

This macro will update all the Table of Tables from the active document

Sub updateTOTs()
    On Error Resume Next
    Dim tot As TableOfTables
    For Each tot In ActiveDocument.TablesOfTables
        tot.Update
        tot.UpdatePageNumbers
    Next
End Sub

4. Update all fields

This VB macro will Update all the fields in the active document

Public Sub updateFields()
    ActiveDocument.Fields.Update
End Sub

5. Extends content of OLE’s

This macro will extends the content of WORD OLE object in a document

Public Sub extendOLE()
Dim doc As Document
Set doc = ActiveDocument
Dim iShape As InlineShape
nFld = doc.InlineShapes.Count
For i = nFld To 1 Step -1
    Set iShape = doc.InlineShapes.Item(i)
    If iShape.Type = wdInlineShapeEmbeddedOLEObject Then
        iShape.Select
        Selection.InlineShapes(1).OLEFormat.DoVerb VerbIndex:=1
        Selection.WholeStory
        Selection.Cut
        ActiveDocument.Close
        Selection.PasteAndFormat (wdPasteDefault)
    End If
Next i
End Sub

6. Embed OLE objects and Unlink

The macro will search for embedded OLE objects, and for the Word type OLE’s, will copy their document content and insert it in the place of the OLE. After this, all fields within the main document are unlinked.

Sub includeOLEContent()

    Dim index As Long
    Dim wrdActDoc As Document
    Dim str As String
    Dim oDoc As Word.Document
    Dim oDocObj As Object
    Dim oDocInlineShape As Word.InlineShape
    Dim content As String
    Dim rng As Word.Range
    Dim oFld As Word.field
    Set wrdActDoc = ActiveDocument

    For index = 1 To wrdActDoc.InlineShapes.Count
        If wrdActDoc.InlineShapes(index).Type = wdInlineShapeEmbeddedOLEObject Then
               str = wrdActDoc.InlineShapes(index).OLEFormat.ClassType
               
                If InStr(1, str, "Word", 0) <> 0 Then
                    Set oDocInlineShape = wrdActDoc.InlineShapes(index)
                    oDocInlineShape.OLEFormat.Edit
                    Set oDoc = oDocInlineShape.OLEFormat.Object
                    oDoc.Range.Copy
                    oDocInlineShape.Range.Paste
                End If
        End If
    Next index
    
   For Each rng In wrdActDoc.StoryRanges
        Do
            For Each oFld In rng.Fields
                oFld.Unlink
            Next
            Set rng = rng.NextStoryRange
        Loop Until rng Is Nothing
   Next

End Sub

7.Shade row

Shade every odd row within a table

Sub FindTables()
  Dim iResponse As Integer
  Dim i As Integer
  Dim tTable As Table
  'If any tables exist, loop through each table in collection.
  For Each tTable In ActiveDocument.Tables
    With tTable
    'Shade columun header(s)
    .Rows(1).Shading.BackgroundPatternColor = wdColorGray20
       For i = 2 To .Rows.Count
         With .Rows(i).Shading
         'Shade odd row(s)
         If i Mod 2 = 1 Then
            .BackgroundPatternColor = wdColorGray05
        Else
            .BackgroundPatternColor = wdColorAutomatic
        End If
        End With
     Next
  End With
  Next
End Sub

5 Comments to “VB Macro Library useful for RPE”

  1. David says:

    The updateTOC and updateTOF work great. Thanks. But the updateTOT fails to compiles (Word 2003). Word says “User-defined type not defined” and points to the “TableofTables” (line 3).

    Looking at Microsofts library, I see no reference to a TableofTables. Any suggestions for how to update the TOT?

  2. Calin says:

    In the rpe.dot file that can be used as stlesheet, there is an macro named peUpdateFields that will update the TOC, TOF and also TOT.
    Regards, Calin

  3. Sherry says:

    What about OLE’s of type PDF or non Word type?
    In item 6. Embed OLE objects and Unlink, you stated
    “embedded OLE objects, and for the Word type OLE’s”. But we have ole embedded as icons for pdf which require Adobe Reader and they do not convert to image when using the rpe macros

  4. Mark Forrester says:

    Can you say who has access to the password-protected Export-to-DOORs routine that’s in the RPE.dot and the DOORS.dot? Are they identical? I called IBM, but it seems no one there even knows the password. There’s a callback as well as an export macro. It’s supposed to get put into the Word Add-Ins when DOORS is installed first, but my client has the two on different (virtual) servers!

  5. Gray Settle says:

    Since I cannot get the rpe macro in the RPE installation to work to copy the OLE content into the Word file to make a self-contained file, I tried the includeOLEContent macro on this site. It works to some extent. Upon execution for the first time, whether by RPE during the document generation or in the Word document, the macro “errors out”. Running it again, produces the embedded OLE content for every other embedded OLE but “errors out”. If I continue to run the macro multiple times, it eventually gets the content for all the embedded OLEs into the document and executes without error. Does anyone know what the problem is? This is not very satisfactory method for achieving the desired result.

Leave a Reply

(required)

(required)