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 |
---------------
- 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.
- Second, I had trouble browsing or navigating a Word table, one row and one cell at a time.
- 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.
- When I do copy the text across, the table's Autofit behaviour no longer remains the same - and has to be reapplied before saving.
- 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
- Word and VBA Applications FAQ
- Word VBA Tables, Fields and Forms
- Sample MS Word VBA scripts
- Automating Word Tables for Data and Extraction
