CODEWITHSUNDEEP
htmlvbams-wordms-access-2010
Eric
Eric

Reputation: 323

Access Rich Text to Word via VBA: Formatting Ignored and Added Linebreak

Goal

I'm publishing data from our access database to word via VBA. Some of the content being published is in rich text format, and some of it is being formatted on the fly using HTML, and I'm using .InsertFile and a temporary HTML file to accomplish this. This works and I'm able to insert the data into the tables in word, however all of the formatting used in my template is ignored. I've fixed the font size and family but I also require the space-before/after to be a specific size and the extra line to be removed.

Problem

I cannot change the space-before or after for the content that I publish using .InsertFile and there is always a trailing blank line after the content is inserted.

Examples

This is what the content would typically look like upon publishing.

enter image description here

Note the trailing space and how the list sits right against the cell border.

This is an example of the content that is generated on the fly using HTML.

<html>
<ul>
<li>Lorem ipsum dolor sit amet</li>
<li>Lorem ipsum dolor sit amet</li>
<li>etc...</li>
</ul>
</html>

This is what it looks like before the content is published to the template.

enter image description here

Code

Where the magic happens, or rather doesn't happen. The htmlToRich function checks for null values, removes Unicode characters and wraps the string in <html></html> tags before saving to a temporary file 

    With oWord.selection
        .Collapse Direction:=wdCollapseEnd
        .Move Unit:=wdCharacter, Count:=-1

        ' Using htmlToRich function, format the string then wrap in
        ' HTML tags
        .InsertFile _
            filename:=htmlToRich("<ul>" & sReference & "</ul>")

        ' Format, unsuccessfully...
        .ParagraphFormat.SpaceBefore = 6
        .ParagraphFormat.SpaceAfter = 6
        .Font.Size = 10
    End With

Final Words

I've been working with VBA for about a year now, but formatting word has consistently proven to be my weak point. If there is any more information you'd need to help with this issue, please let me know.

Edit

I've had another go at removing the extra line after the HTML is inserted and managed to come up with this.

    With oWord.selection
        ' Using htmlToRich function, format the string then wrap in
        ' HTML tags
        .InsertFile _
            filename:=htmlToRich("<ul>" & sReference & "</ul>")
        ' Move the cursor to the end of the cell
        .move wdCharacter
        ' move the cursor back two characters
        .MoveEnd count:=-2
        ' Select the last two cell characters, which should always be a 
        ' line break and a blank cell character.
        .MoveEnd
        ' delete selected
        .Range.Delete
    End With

This works well in all of my testing so far, even if it isn't elegant. This just leaves me with a space-before/after issue which I can't seem to fix using this same method. I was able to select the first line of the list and apply oWord.Selection.ParagraphFormat.SpaceBefore = 6 and no changes were made. Back to the drawing board...

Upvotes: 2

Views: 1397

Answers (2)

Eric
Eric

Reputation: 323

Shortly after solving the trailing line break character I found a solution for my space-before and after issue.

I've created a public sub that allows me to format the document's table cells (all of my content is being published into tables). I'll most likely be updating it soon to remove all trailing and leading line breaks as they can cause unsightly issues. 

Public Sub formatRichText(oWord As Word.Application, oDoc As Word.Document, iTable As Integer, _
    iRow As Integer, iCol As Integer)

    oDoc.Tables(iTable).Cell(iRow, iCol).Select
    ' Delete trailing paragraph character
    With oWord.selection
        .Move wdCharacter
        .MoveEnd wdCharacter, -2
        .MoveEnd
        .Delete
    End With

    oDoc.Tables(iTable).Cell(iRow, iCol).Select
    ' Fix space before
    With oWord.selection
        .Move Count:=-1
        .MoveEnd wdLine
        .ParagraphFormat.SpaceBeforeAuto = False
        .ParagraphFormat.SpaceBefore = 6
    End With

    oDoc.Tables(iTable).Cell(iRow, iCol).Select
    ' fix space after
    With oWord.selection
        .Move Count:=1
        .MoveEnd Count:=-1
        .ParagraphFormat.SpaceAfterAuto = False
        .ParagraphFormat.SpaceAfter = 6
    End With

    oDoc.Tables(iTable).Cell(iRow, iCol).Select
    ' Format font face and size
    With oWord.selection
        .Font.Name = "Arial"
        .Font.Size = 10
    End With

End Sub

I'd apply the space-before/after to the whole selection, but when applied to lists the individual items have their spacing adjusted.

Upvotes: 2

Arya
Arya

Reputation: 341

i have tries with this html

<html>
<table width="100%">
<tr>
<td>test1</td><td>test1</td><td>test1</td>
<td rowspan="3">
<ul>
<li>Lorem ipsum dolor sit amet</li>
<li>Lorem ipsum dolor sit amet</li>
<li>etc...</li>
</ul>
</td>
</tr>
<tr>
<td>test1</td><td>test1</td><td>test1</td>
</tr>
<tr>
<td>test1</td><td>test1</td><td>test1</td>
</tr>
</table>
</html>

imported html in word and there is no extra paragraph is coming. for the formatting if you have already a template the u can use style in word.

Upvotes: 0

Related Questions