none
Resource for beginning Word developer RRS feed

  • Question

  • I am a novice developer, and am trying to create a program in Excel VBA to take contents from my Excel spreadsheet, and inserts/(replaces) these contents as text into a Word document. (Into Paragraphs, as well as into Tables). Alternatively, I could write the program in Word VBA, and pull in data from an Excel spreadsheet. Either way would be fine for me.

    Does anyone have any code samples/fragments I can see related to these tasks? I'm having particular difficulty working with Word Tables, so any code showing how to access and modify the contents of Tables would be much appreciated!

    Many thanks in advance . . . 

     



    DragonForest

    Friday, April 20, 2012 3:24 AM

Answers

  • Use ranges and the table object.

    For instance, here is a code snippet I use to format all tables in a document:

    -------------

    Dim tblObject As Table

    For Each tblObject In Selection.Range.Tables
        With tblObject
            .Rows.LeftIndent = InchesToPoints(0)
            .TopPadding = InchesToPoints(0.03)
            .BottomPadding = InchesToPoints(0.03)
            .LeftPadding = InchesToPoints(0.04)
            .RightPadding = InchesToPoints(0.04)
            .Spacing = 0
            .AllowPageBreaks = True
            .AllowAutoFit = False
            .PreferredWidthType = wdPreferredWidthPercent
            .PreferredWidth = 100
            .Rows.HeightRule = wdRowHeightAuto
            .Range.ParagraphFormat.Style = "Table Text"
            .Range.Cells.VerticalAlignment = wdCellAlignVerticalCenter
        End With
    Next

    -------------

    Or, once you have focused on a table, use ranges. Here is some code I wrote a few years back. It was used to clean up various documents that were formatted as long tables, often with empty cells, so I wanted to remove all rows that had nothing but empty paragraphs...

    -------------

    Sub Aero_RemoveSingleCharaCell_inDoc()

    Dim tblCheck As Table
    Dim rw As Row
    Dim cl As Cell
    Dim rngCL As Range
    Dim myPara As Paragraph
    Dim lngCountDel As Long
    Dim lngThreeCount

    System.Cursor = wdCursorWait
    Application.ScreenUpdating = False

    lngThreeCount = 0

    For Each tblCheck In ActiveDocument.Tables
        With tblCheck
            For Each rw In .Rows
                lngCountDel = 0
                With rw
                    For Each cl In rw.Range.Cells
                        Set rngCL = cl.Range
                        With rngCL
                            For Each myPara In .Paragraphs
                                If Trim(myPara.Range.Text) = Chr(13) Or _
                                    myPara.Range.Characters.Count < 4 Then
                                    myPara.Range.Delete
                                ElseIf myPara.Range.Characters.Count = 4 Then
                                    myPara.Range.HighlightColorIndex = wdPink
                                    lngThreeCount = lngThreeCount + 1
                                Else
                                    Exit For
                                End If
                            Next
                            If .Characters.Count = 1 Then
                                lngCountDel = lngCountDel + 1
                            End If
                        End With
                    Next
                    If lngCountDel = .Range.Cells.Count Then .Delete
                End With
            Next
        End With
    Next

    Application.ScreenRefresh
    Application.ScreenUpdating = True
    System.Cursor = wdCursorNormal

    If lngThreeCount > 0 Then
        MsgBox "There are " & lngThreeCount & " 3-character paragraph(s) in this document.", vbInformation, "Three Count"
    End If

    End Sub

    -------

    For lots of good resources on getting started with code writing, check out this page...

    Word MVP's Site - Macros

    • Marked as answer by DragonForest Friday, April 20, 2012 2:29 PM
    Friday, April 20, 2012 12:30 PM

All replies

  • Use ranges and the table object.

    For instance, here is a code snippet I use to format all tables in a document:

    -------------

    Dim tblObject As Table

    For Each tblObject In Selection.Range.Tables
        With tblObject
            .Rows.LeftIndent = InchesToPoints(0)
            .TopPadding = InchesToPoints(0.03)
            .BottomPadding = InchesToPoints(0.03)
            .LeftPadding = InchesToPoints(0.04)
            .RightPadding = InchesToPoints(0.04)
            .Spacing = 0
            .AllowPageBreaks = True
            .AllowAutoFit = False
            .PreferredWidthType = wdPreferredWidthPercent
            .PreferredWidth = 100
            .Rows.HeightRule = wdRowHeightAuto
            .Range.ParagraphFormat.Style = "Table Text"
            .Range.Cells.VerticalAlignment = wdCellAlignVerticalCenter
        End With
    Next

    -------------

    Or, once you have focused on a table, use ranges. Here is some code I wrote a few years back. It was used to clean up various documents that were formatted as long tables, often with empty cells, so I wanted to remove all rows that had nothing but empty paragraphs...

    -------------

    Sub Aero_RemoveSingleCharaCell_inDoc()

    Dim tblCheck As Table
    Dim rw As Row
    Dim cl As Cell
    Dim rngCL As Range
    Dim myPara As Paragraph
    Dim lngCountDel As Long
    Dim lngThreeCount

    System.Cursor = wdCursorWait
    Application.ScreenUpdating = False

    lngThreeCount = 0

    For Each tblCheck In ActiveDocument.Tables
        With tblCheck
            For Each rw In .Rows
                lngCountDel = 0
                With rw
                    For Each cl In rw.Range.Cells
                        Set rngCL = cl.Range
                        With rngCL
                            For Each myPara In .Paragraphs
                                If Trim(myPara.Range.Text) = Chr(13) Or _
                                    myPara.Range.Characters.Count < 4 Then
                                    myPara.Range.Delete
                                ElseIf myPara.Range.Characters.Count = 4 Then
                                    myPara.Range.HighlightColorIndex = wdPink
                                    lngThreeCount = lngThreeCount + 1
                                Else
                                    Exit For
                                End If
                            Next
                            If .Characters.Count = 1 Then
                                lngCountDel = lngCountDel + 1
                            End If
                        End With
                    Next
                    If lngCountDel = .Range.Cells.Count Then .Delete
                End With
            Next
        End With
    Next

    Application.ScreenRefresh
    Application.ScreenUpdating = True
    System.Cursor = wdCursorNormal

    If lngThreeCount > 0 Then
        MsgBox "There are " & lngThreeCount & " 3-character paragraph(s) in this document.", vbInformation, "Three Count"
    End If

    End Sub

    -------

    For lots of good resources on getting started with code writing, check out this page...

    Word MVP's Site - Macros

    • Marked as answer by DragonForest Friday, April 20, 2012 2:29 PM
    Friday, April 20, 2012 12:30 PM
  • Many thanks. That's very helpful!



    DragonForest

    Friday, April 20, 2012 2:29 PM