2008-02-22

Word Tables and cells with VBA

I cannot believe how hard this was (took me 3 to 4 hours) - working with Word tables and cells, and here's a synopsis of what I had to go through.

Objective:

INPUT FILE

Header text to be copied on every output file

Second line of header text to be copied on every output file (if having more than one line screws things up I can have just one)

Column Heading 1

Column Heading 2

Column Heading 3

Column Heading 4

Column Heading 5

Column Heading 6

Column Heading 7

Column Heading 8

98000

John

data

data

data

data

data

data

98000

Cyndee

data

data

data

data

data

data

98001

Kevin

data

data

data

data

data

data

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

OUTPUT FILE 1 (i.e. has all data where the first column’s data = 98000)

Header text to be copied on every output file

Second line of header text to be copied on every output file (if having more than one line screws things up I can have just one)

Column Heading 2

Column Heading 3

Column Heading 4

Column Heading 5

Column Heading 6

Column Heading 7

Column Heading 8

John

data

data

data

data

data

data

Kevin

data

data

data

data

data

data

OUTPUT FILE 2 (i.e. has all data where the first column’s data = 98001)

Header text to be copied on every output file

Second line of header text to be copied on every output file (if having more than one line screws things up I can have just one)

Column Heading 2

Column Heading 3

Column Heading 4

Column Heading 5

Column Heading 6

Column Heading 7

Column Heading 8

Cyndee

data

data

data

data

data

data




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

  1. So, I got around to read the input word document, and wanted to then create an "array" of word documents, and thought I could use the entry in the first column (such as "98000"), as the key - especially with the examples I saw on the web. Alas, none of them worked, so I had to use a Scripting.Dictionary object.
  2. Second, I had trouble browsing or navigating a Word table, one row and one cell at a time.
  3. When I finally got around that, I then had to worry about ensuring the Font styles were copied across. Again, turns out there isn't a direct mechanism in VBA to do that.
  4. When I do copy the text across, the table's Autofit behaviour no longer remains the same - and has to be reapplied before saving.
  5. If that wasn't enough, the documents created for output were by default saved in "Outline" mode - one of the vagaries of word that I would never understand.

There were other problems faced, not worth mentioning here..but here's the key snippet of the code:

'Add rows to table as required
Private Sub PopulateAndExtendTable(tbl As Word.Table, aData As Word.Range)
Dim nrRows As Long, nrCols As Long
Dim oCell As Cell
Dim oRow As Row


'tbl.Range.FormattedText = aData.FormattedText
'tbl.Rows(tbl.Rows.Count).Range.FormattedText = aData.FormattedText
tbl.Rows(tbl.Rows.Count).Range.Font = aData.Font
tbl.Rows(tbl.Rows.Count).Range.Font.Bold = aData.Font.Bold
For Each oRow In aData.Rows
For Each oCell In oRow.Cells
'MsgBox "Hi" & oCell.Range.Text
tbl.Cell(oRow.Index, oCell.ColumnIndex).Range.Text = oCell.Range.Text
tbl.Cell(oRow.Index, oCell.ColumnIndex).Range.Font = oCell.Range.Font
Next
If aData.Rows.Count > tbl.Rows.Count Then tbl.Rows.Add
Next

tbl.Rows.Add

End Sub 'Populate existing rows





Finally, here are some useful links


3 comments:

  1. Hey I must thank you for the perl one liner for deleting empty directories.

    A search for that led me to your blog.
    Nice work

    amit kak
    Are you on LinkedIN? It would be great to be in touch

    ReplyDelete
  2. Thanks - glad I could help.

    ReplyDelete
  3. Sorry I'm not on linkedIN but you can find me on facebook

    ReplyDelete