Big ask - need help formatting data from vb to word doc

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Blackberry - 22 Sep 2007 12:20 GMT

Hi All

I know this is a big ask, but I could really do with some help making the
below reliable and as fast as possible.

In essence I have a word doc consisting of 1 table, which has about 20 cols
and 6 rows.

In my VB app, I have a list of say 30 - 35 people, who have to go into
specific cells in this word doc table.

I have managed to do this by using a CASE statement to concat the names (+ a
carriage return) into an array (let's say of 20 values, ie 20 cols for 1
row) and then I squirt the content of each value in the array into the
relevant cell bookmarks in the Word doc.

I have this bit working perfectly. The problem is that these people are
flagged with certain attributes, which in turn need to be graphically
represented on the word doc either by text background colours, text
foreground colours or styles (ie bold, italic & underline).

I have all of this info available to me in an array, but my problem is that
I don't APPEAR to be able to 'tag' these styles to the people. For example
if I could use tags like I can in HTML, I could do the following in my
array:

Array(0) = "<red><bold>Fred Smith</bold></red>" & vbCrLf &
"<green><italic>Fred Smith2</italic></green>" & vbCrLf
.... etc...

Array(1) = "<yellow>Fred Smith3</yellow>" & vbCrLf &
"<underline>Fred Smith4</underline>" & vbCrLf .... etc...

and so on...

What I'm trying to say is that the tags allow me to format each person in
the array, but I don't think Word VBA can do this - am I right?

Can you think of another way round this, bearing in mind that I don't know
how many people will be in each cell and each person can have different
attributes to the others in the same cell.

Thanks for any pointers you can give.

Reply to this Message

Shauna Kelly - 22 Sep 2007 14:34 GMT

Hi Blackberry

I would do this as follows:

1. Create a character style in the document for each person. For example,
for Jim it might be:

Dim oDoc As Word.Document
Dim oStyle As Word.Style

'Get a reference to your document in some appropriate way
Set oDoc = ActiveDocument

'Create a new character style
On Error Resume Next 'in case the style already exists
Set oStyle = oDoc.Styles.Add(Name:="Jim", Type:=wdStyleCharacter)
On Error GoTo 0

With oStyle.Font
'Set the attributes of the font
'See VBA help for more options
.Underline = wdUnderlineSingle
.Color = wdColorBrightGreen
.Bold = True
End With

2. Don't concatenate your data into one string for each cell. Instead, do
each name individually, something like this:

Dim oTable As Word.Table
Dim oCell As Word.Cell
Dim rng As Word.Range

Set oTable = whatever is appropriate in your circumstance

set oCell = however you identify the cell to add the text to

'Get the range of the cell
Set rng = oCell.Range

'Collapse to the end
rng.Collapse wdCollapseEnd
rng.MoveEnd wdCharacter, -1 'get back into the cell

'Add the name of a person
rng.InsertAfter " Jim"

'rng will have expanded to encompass the text so
'we can format the text
rng.Style = "Jim"

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

> Hi All
>
[quoted text clipped - 43 lines]
>
> Thanks for any pointers you can give.

Reply to this Message