CODEWITHSUNDEEP
vbaexcelms-word
tincanfury
tincanfury

Reputation: 161

VBA Writing to Word, changing font formatting

I'm writing a VBA script in Excel to output text based on some tables to a Word document. For the most part everything is working out beautifully (I'm teaching myself as I go with lots of help from stackoverflow). I've got a rather long code so copying it all here would be difficult, I'm going to try and show the pertinent parts.

The issue I'm running into is in trying to change the formatting of my font as I go through. I've created a few different Styles to assist in standardizing the formatting options,

With wrdDoc
    .Styles.Add ("SectionHeader")
    .Styles.Add ("NoFormat")
    .Styles.Add ("Marginal")
    .Styles.Add ("Failed")
    .Styles.Add ("Unknown")
    .Styles.Add ("Bold")
    With .Styles("SectionHeader")
        .Font.Name = "Calibri"
        .Font.Size = 12
        .Font.Underline = True
        .Font.Bold = False
        .Font.Italic = False
        .Font.Strikethrough = False
        .Font.Subscript = False
        .Font.Superscript = False
        .Font.Color = RGB(0, 0, 0)
    End With
    With .Styles("NoFormat")
        .Font.Name = "Calibri"
        .Font.Size = 12
        .Font.Underline = False
        .Font.Bold = False
        .Font.Italic = False
        .Font.Strikethrough = False
        .Font.Subscript = False
        .Font.Superscript = False
        .Font.Color = RGB(0, 0, 0)
    End With
    With .Styles("Marginal")
        .Font.Name = "Calibri"
        .Font.Size = 12
        .Font.Underline = False
        .Font.Bold = True
        .Font.Italic = False
        .Font.Strikethrough = False
        .Font.Subscript = False
        .Font.Superscript = False
        .Font.Color = RGB(0, 0, 255)
    End With
    With .Styles("Failed")
        .Font.Name = "Calibri"
        .Font.Size = 12
        .Font.Underline = True
        .Font.Bold = True
        .Font.Italic = False
        .Font.Strikethrough = False
        .Font.Subscript = False
        .Font.Superscript = False
        .Font.Color = RGB(255, 0, 0)
    End With
    With .Styles("Unknown")
        .Font.Name = "Calibri"
        .Font.Size = 12
        .Font.Underline = False
        .Font.Bold = True
        .Font.Italic = False
        .Font.Strikethrough = False
        .Font.Subscript = False
        .Font.Superscript = False
        .Font.Color = RGB(0, 176, 80)
    End With
    With .Styles("Bold")
        .Font.Name = "Calibri"
        .Font.Size = 12
        .Font.Underline = False
        .Font.Bold = True
        .Font.Italic = False
        .Font.Strikethrough = False
        .Font.Subscript = False
        .Font.Superscript = False
        .Font.Color = RGB(0, 0, 0)
    End With
End With

Then, I'm trying to do this, where the output text will have a single word in the middle of the sentence change format,

With wrdApp.Selection
    .Style = wrdDoc.Styles("NoFormat")
    .TypeText Text:="The start of this sentence is "
    .Style = wrdDoc.Styles("Unknown")
    .TypeText Text:="unknown"
    .Style = wrdDoc.Styles("NoFormat")
    .TypeText Text:=" so we will keep trying..."
    .TypeParagraph
End With

But what I get is the sentence without the change if format for that word.

More curiously, I also have a few components which should apply my "Bold" style to an entire sentence, one of which does apply the Bold, the other which does not,

With wrdApp.Selection
    .Style = wrdDoc.Styles("Bold")
    .TypeText Text:="This one doesn't work"
End With

With wrdApp.Selection
    .Style = wrdDoc.Styles("Bold")
    .TypeText Text:="this one works!"
    .TypeParagraph
End With

I'd also like to be able to apply 

    .ParagraphFormat.SpaceAfter = 0
    .ParagraphFormat.SpaceBefore = 0

to some of these Selections, but I can't figure out how do do that. I can get it to do it for a "Range" to the Document, but that ends up applying it to the existing sections of the document, but not to any of the new text.

Upvotes: 2

Views: 8319

Answers (2)

user1379931
user1379931

Reputation:

One of the problems that you have is that when you create a Style, by default it is a Paragraph style (or in recent versions of Word, it will typically be a "Linked style", which are sometimes thought of as Paragraph/Character Styles).

So when you execute

.Style = wrdDoc.Styles("NoFormat")

the second time, for example, the style is applied to the whole paragraph. The fact that you tried to apply "Unknown" to part of the paragraph is forgotten.

If you create the same test styles as character styles, e.g.

.Styles.Add "NoFormat", wdStyleType.wdStyleTypeCharacter

and similarly for the "Unknown" style

and run your first test example again, you should see the difference.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166790

I'm always a bit confused a bout ranges in Word, but try something like this:

Sub TestAdd()
    AddWithStyle "This is a normal ", "Normal"
    AddWithStyle "but this is different", "Heading 1"
    AddWithStyle " and this is back to normal ", "Normal"
End Sub


Sub AddWithStyle(sText As String, sStyle As String)
    Selection.TypeText sText
    Selection.MoveLeft unit:=wdCharacter, Count:=Len(sText), Extend:=wdExtend
    Selection.Style = ActiveDocument.Styles(sStyle)
    Selection.Collapse Direction:=wdCollapseEnd
End Sub

Upvotes: 0

Related Questions