Best way to fill in a Word table from Access

[Paul Gross]

I want to create a table in Word that have 4 colums each
of which is filled from a single table in Access but each
column represents a slightly different query. Ideally,
this would be a macro that is invokable from Access with
a few arguments.

What is the best source of information as to how to
figure this out? I have never used the Word object model
and have spent about a year programming in Access 2002.

Paul

 

[Cindy M -WordMVP-]

Hi Paul,

You'd need to concatenate the data into a delimited string,
drop that into a Word document range, then convert it to a
table. That would be the fastest and "cleanest" way. The
code would work something like this:

'in ACCESS
For each rec1 in rst1
sData = sData & rec.Fields(0).Value & vbTAB
For each rec2 in rst2
sData = sData & rec.Fields(0).Value & vbTAB
Next rec2
For each rec3 in rst 3
sData = sData & rec.Fields(0).Value & vbTAB
Next rec3
sData = sData & vbCR
Next rec1

'in Word
rng.Text = sData
rng.ConvertToTable

Note that this is completely untested, just meant to give
you an idea of how to loop to put the data together, and to
let you know what objects you need to look up in Word's
help. "rng" = a Word.Range in a Word.Document

I want to create a table in Word that have 4 colums each
of which is filled from a single table in Access but each
column represents a slightly different query. Ideally,
this would be a macro that is invokable from Access with
a few arguments.

What is the best source of information as to how to
figure this out? I have never used the Word object model
and have spent about a year programming in Access 2002.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Paul Gross]

Thanks Cindy.

I ended up finding a "How To" KB article for mail merge
from VB that had enough code to let me do what I needed to
populate the table from VBA in Access.

Now I have two tweaking questions:

1) How do I turn off horizontal inside borders after the
first row, i.e., I want the column headings to be
separated by a horizontal line but not each of the cells
below? The HasHorizontal property seems to be read-only.

2) Outside the table, I was trying to turn on a gray
background for a little bit of text. It worked as I
expected by doing: wrdSelection.font.backgroundcolor =
wdColorgray05 followed by a typetext but a subsequent set
to wdColorWhite didn't reset it. The rest of my text
seemed to carry that background. Did I miss something?

Paul

 

[Cindy M -WordMVP-]

Hi Paul,

I ended up finding a "How To" KB article for mail merge
from VB that had enough code to let me do what I needed to
populate the table from VBA in Access.

Fine, but be warned: it's probably "nasty" macro-recorder
type code that 1. will be awfully slow in execution and 2.
won't optimally use Word's object model. Most of the "HowTo"
articles for automating Word contain absolutely abominable
code...

1) How do I turn off horizontal inside borders after the
first row, i.e., I want the column headings to be
separated by a horizontal line but not each of the cells
below? The HasHorizontal property seems to be read-only.

You need something along the lines of the following. Since I
don't know how your code looks, I can't tell you exactly how
to reference the table (row), but:

ActiveDocument.Tables(1).Rows(2).Borders.InsideLineStyle =
wdLineStyleNone

AND

ActiveDocument.Tables(1).Rows(2).Borders(wdBorderHorizontal).
LineStyle = wdLineStyleSingle

(Yes, borders are complicated!)

2) Outside the table, I was trying to turn on a gray
background for a little bit of text. It worked as I
expected by doing: wrdSelection.font.backgroundcolor =
wdColorgray05 followed by a typetext but a subsequent set
to wdColorWhite didn't reset it. The rest of my text
seemed to carry that background.

Mmmm. Well, you really should be using ranges, and not
selection, then you wouldn't have any problems with
"turning formatting off"... But the Word object model doesn't
have anything like font.backgroundcolor, so I don't know
where to start looking in order to help you? What do you
really have? How about copying/pasting that relevant section
of code?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail