How to Extract Comment information from Word VBA

How to Extract Comments Text and Related Information from Word Document using VBA

Here is a hint of accessing the comments and related information using VBA

Sub Get_Comment_Information()

Dim oComment As Comment
Dim oCommentRange As Range

For i1 = 1 To ActiveDocument.Comments.Count
    Set oComment = ActiveDocument.Comments(i1)
    Set oCommentRange = oComment.Scope.Paragraphs(1).Range
    Debug.Print "Page : " & oCommentRange.Information(wdActiveEndPageNumber) & vbTab _
                     & "Line : " & oCommentRange.Information(wdFirstCharacterLineNumber) & vbTab

    

Next i1

End Sub

How to Identify and Tag Numbered Lists using VBA

How to Identify and Tag Bullet Lists using VBA

Following snippet identifies a Bulleted List and Tags all Bullet List items and the Bulletted List as a whole

Sub Tag_Lists()

Dim oBL As ListFormat
Dim oList As List
Dim oLI

    For Each oList In ActiveDocument.Lists
        If oList.Range.ListFormat.ListType = WdListType.wdListBullet Then
            For Each oLI In oList.ListParagraphs
                oLI.Range.InsertBefore "
"                  oLI.Range.InsertAfter "
"
            Next oLI
            oList.Range.InsertBefore "
"             oList.Range.InsertAfter "
"
        End If
    Next oList


 End Sub

Compare Word Documents with Headers and Footers using VBA

How to Compare Word Documents Programatically using Word VBA

Word Documents are everywhere .. proposals, tenders, notes, technical papers. In many cases there are more than one authors and more than five reviewers. There is a devil in everyone which comes out when reviewing the document. Suggest some changes.. boldface  some text, markup some paragraph and screw the document. If you are  the author it is your responsibility to ensure that the changes get reflected. There are many document management solutions that are available for parallel working.

Just in case you get a document reviewed by your boss (and without track changes) and you want to know what he/she has done use the following

Sub CompareDoc()

Dim oDoc1 As Document
Dim oDoc2 As Document

Set oDoc1 = Documents.Open("D:\Changed Header.doc")
Set oDoc2 = Documents.Open("D:\Original Header.doc")
Application.CompareDocuments oDoc1, oDoc2, wdCompareDestinationNew, , , , , , True, True

End Sub

This compares two documents and creates a new document with Track Changes showing the changes.


There are lot of parameters to CompareDocuments method. The notable being CompareFormatting, CompareHeaders, CompareFootnotes. The last two ones are used if you want to know the changes made in Headers and Footers. Who knows you would have kept the same header from the document you cloned and your boss would have noticed and changed it. Do you want to take risk of ignoring that

See also
Comparing two Word Documents using Word VBA
Compare Files by Date

How to Convert Word Table to PDF using VBA

Export Word Table as PDF using VBA

Anyone who is using Word for quite sometime will agree that Tables and Images are bit scary when it comes to viewing across versions or machines. A Table which looks great in your machine might not look so if he uses a different version of Word.

In that case it is better to have the Table converted as PDF in your machine and circulate the same. In last post we saw how to export part of text to a new document using ExportFragment method. Here we export a Table as PDF using ExportAsFixedFormat method.

The following snippet does exactly the same:

Sub Table2PDF()

Dim oTab As Word.Table
Dim oRange As Word.Range

Set oTab = ActiveDocument.Tables(1)

oTab.Range.ExportAsFixedFormat "D:\Documents and Settings\Admin\My Documents\Tab_PDF.pdf", wdExportFormatPDF

End Sub

See also:
Convert Word to PDF using VBA

How to Export Parts of Document using Word VBA

Copy Content with Formatting to New Document using Word VBA

Not all the tens and hundreds of pages in a Word document interests you or matters to you. There are some documents, which we use for reference. All we need is a paragraph/section from the document. If it is a book we used to take a photo-copy of the same and keep it in a folder. How to do the same in a Word document - and in an automated way with all the formatting intact?

 ExportFragment method in Word VBA provides the solution. It creates a new document from the existing one for the Range of your choice.

Here is an example where it exports eleventh paragraph of the document to a new one.

Sub PartofText()

Dim oWDRange As Word.Range

Set oWDRange = ActiveDocument.Paragraphs(11).Range
oWDRange.ExportFragment "D:\Documents and Settings\Admin\My Documents\Reference_11.docx", wdFormatDocumentDefault

End Sub

See also

How to export Word Range as RTF using VBA

How to Format Part of Content Controls in Word VBA

Word VBA - Format Some portion of Rich Text Content Control Programatically

ContentControls have become ubiquitous with Word documents nowadays. Rich Text Content Control is used by many developers and authors to represent useful information.

At times there is a necessity to highlight / format some part of the Text in that control. You can either search for the text and highlight it or Highlight them based on position

The following example shows how to boldface certain portion of ContentControl

Sub FormatContentControl()

Dim oCC As ContentControl
Dim oCCRange As Range
Dim oCCRngFormat As Range
Dim oChr As Range

Set oCC = ActiveDocument.ContentControls(1)
oCC.Type = wdContentControlRichText
Set oCCRange = oCC.Range
Set oCCRngFormat = oCCRange.Duplicate

oCC.LockContentControl = False
oCC.LockContents = False

oCCRngFormat.Start = 20
oCCRngFormat.End = oCCRange.End

For Each oChr In oCCRngFormat.Characters
    oChr.Font.Bold = True
Next oChr

oCCRngFormat.Font.Bold = -1
oCCRngFormat.Font.Underline = WdUnderline.wdUnderlineSingle

oCCRngFormat.Start = oCCRange.End
oCCRngFormat.Font.Bold = 0
oCCRngFormat.Font.Underline = WdUnderline.wdUnderlineNone


End Sub

See also
How to retrieve value from Content Controls using Word VBA
How to add Content Controls using VBA

How to Search and Highlight/Tag a string in Word VBA

How to Search Content for Specific String/Text using Word VBA

This action is performed often by programmers - there are couple of ways to do

1. Selection.Find
2. Content.Find

We will have a look at how to search a string, highlight the string and tag the same using Word VBA. This needs document to be open

Sub Highlight_Tag_Found_Word()

Dim sFindText As String

sFindText = "Olympics"

Selection.ClearFormatting

Selection.HomeKey wdStory, wdMove

Selection.Find.ClearFormatting

Selection.Find.Execute sFindText

 

Do Until Selection.Find.Found = False

        Selection.Range.HighlightColorIndex = wdPink
        
        Selection.InsertBefore "< FoundWord >"
        
        Selection.InsertAfter < /FoundWord >
        
        Selection.MoveRight
        
        Selection.Find.Execute

Loop

 

End Sub

How to create a Trendline Chart using Excel VBA

Excel VBA - Trendline Charts

Here are some snippets useful to create a TrendLine Chart in Excel

Have used the entire data from the given sheet to create the chart. Have used the UsedRange function to get that.

If you want to have a specified range you can pass that also

Sub Create_TrendLine_Chart_Excel_2003(ByRef oRep As Worksheet, ByVal iLeft As Double, ByVal iTop As Double, ByVal sChartTitle As String, ByRef oSource As Range)
Dim oChts As ChartObjects           '* Chart Object Collection
Dim oCht As ChartObject             '* Chart Object
On Error GoTo Err_Chart
    Set oChts = oRep.ChartObjects
    Set oCht = oChts.Add(iLeft, iTop, 400, 450)
   
    oCht.Chart.SetSourceData oSource, PlotBy:=xlColumns
    oCht.Chart.ChartType = xlLineMarkers
   
    oCht.Chart.HasTitle = True
    oCht.Chart.ChartTitle.Text = sChartTitle
   
    oCht.Chart.Legend.Position = xlLegendPositionRight
   
    oCht.Chart.HasAxis(XlAxisType.xlCategory) = True
    oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).HasTitle = True
    oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).AxisTitle.Characters.Text = ""
   
    oCht.Chart.HasAxis(XlAxisType.xlValue) = True
    oCht.Chart.Axes(XlAxisType.xlValue, xlPrimary).HasTitle = True
    oCht.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percentage Done" '.Axes(Type:=XlAxisType.xlValue).AxisTitle.Text = "% Done"
    oCht.Chart.Axes(xlValue).MaximumScale = 1
   
    oCht.Chart.Axes(xlCategory).TickLabelSpacing = 1
    oCht.Chart.Axes(xlCategory).TickLabels.Font.Size = 8
   
    'oCht.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
    If Not oCht Is Nothing Then Set oCht = Nothing
    If Not oChts Is Nothing Then Set oChts = Nothing

Err_Chart:
If Err <> 0 Then
   Debug.Assert Err = 0
   Debug.Print Err.Description
   If Err.Number = 94 Then  'Invalid Use of Null Error
        Err.Clear
        Resume Next
   Else
        Err.Clear
        Resume Next
   End If
End If

End Sub

For some reason the above was creating a problem in Excel 2007 and above. Hence created a separate snippet for it

Sub Create_TrendLine_Chart_Excel_2007(ByRef oRep As Worksheet, ByVal iLeft As Double, ByVal iTop As Double, ByVal sChartTitle As String)
Dim oChts As ChartObjects           '* Chart Object Collection
Dim oCht As ChartObject             '* Chart Object
On Error GoTo Err_Chart
    Set oChts = oRep.ChartObjects
    Set oCht = oChts.Add(iLeft, iTop, 400, 450)
   
    oCht.Chart.ChartWizard Source:=oRep.UsedRange
    oCht.Chart.ChartType = xlLineMarkers
   
    oCht.Chart.HasTitle = True
    oCht.Chart.ChartTitle.Text = sChartTitle
   
    oCht.Chart.Legend.Position = xlLegendPositionRight
   
   
    oCht.Chart.HasAxis(XlAxisType.xlCategory) = True
    oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).HasTitle = True
    oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).AxisTitle.Characters.Text = ""
   
    oCht.Chart.HasAxis(XlAxisType.xlValue) = True
    oCht.Chart.Axes(XlAxisType.xlValue, xlPrimary).HasTitle = True
    oCht.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percentage Done" '.Axes(Type:=XlAxisType.xlValue).AxisTitle.Text = "% Done"
    oCht.Chart.Axes(xlValue).MaximumScale = 1
    'oCht.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)

Err_Chart:
If Err <> 0 Then
   Debug.Assert Err = 0
   Debug.Print Err.Description
   If Err.Number = 94 Then  'Invalid Use of Null Error
        Err.Clear
        Resume Next
   Else
        Err.Clear
        Resume Next
   End If
End If

End Sub

ChartType = xlLineMarkers makes this Chart a TrendLine. You can try your luck by selecting other types