Access DB Queries to MS Word Document, Applying Styles

M

Mark Jerde

Office 2003. Sorry if these are the wrong newsgroups or I missed finding
the answers in my Google and Office help searches. ;-) I _did_ look quite
awhile before posting...

I'm about 20% done with a research project. I'm assembling the data in a
multi-table Access DB because it's a lot easier to work with the data in
Access than Word. The Access forms and reports are exactly what is needed
for this phase of the project.

But one of the main *required* outputs of the project is a Word document.
In Access, the toolbar option "OfficeLinks | Publish It with Microsoft
Office Word" works nicely, but the resulting document requires lots of
formatting to apply the desired styles. Since I'm already well over 20
pages, and I expect to have to produce many iterations of the document as
work progresses, I don't want to apply the formatting manually.

Could someone provide links to building a Word document from Access data,
applying styles and formatting in VBA as the document is constructed? I
think it's just a matter of figuring out the objects needed, because I
created poster-size Excel 2000 worksheets from Access 2000 DBs a few years
ago. "I know what I want to do, but where in this tree is it?" <g>

Thanks in advance.

-- Mark
 
L

Larry Daugherty

Hi Mark,

If you find links to helpful solutions, please share them, eager minds want
to know ...

Beside "Send"ing from Access to Word there are several ways to achieve some
part of what you want; Word mail merge on an Access table, Word template
applied to text input from Access and Automation; creating a Word document
from Access.

You can get the most complete solution via Automation. The downside is that
you must learn a lot about the Word object model and then write and test
your program in Word then take your code over to Access and write the
documents from there. Even a long document can be done quite a few times
for less work than getting the nuances of Access to Word Automation working
just the way you want it. You can just save copies you like and then put
new information into the formatted document.

HTH
 
D

Doug Robbins - Word MVP

Without some information on what the raw document looks like and what the
finished document is required to look like, it's not very easy to give you
much advice.

If providing that advice does not turn night in to day, it will sure throw a
bit more light on the problem.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
 
M

Mark Jerde

Comments inline.

Larry said:
Hi Mark,

If you find links to helpful solutions, please share them, eager
minds want to know ...

Will do.
You can get the most complete solution via Automation. The downside
is that you must learn a lot about the Word object model and then
write and test your program in Word then take your code over to
Access and write the documents from there.

Why do you have to write in Word? I have created complex Excel workbooks
from Access, and the workbooks included poster-sized sheets (24" x 36"),
sheets for 8.5" x 11" handouts, and charts. Is there something different
about Access/Word vs. Access/Excel?
Even a long document can
be done quite a few times for less work than getting the nuances of
Access to Word Automation working just the way you want it. You can
just save copies you like and then put new information into the
formatted document.

Unlike (perhaps <g>) most Word users, I like using styles instead of
applying individual formatting. I'm confident the right set of styles will
work fine if I can figure out how to dump the data in the desired styles.

This project has a lot of cooks stirring the pot. I expect multitudes of
revisions. <g>

Thanks.

-- Mark
 
M

Mark Jerde

Starting very simply:

<Access Pig VBA>

CreateNewWordDocument(Template="MyTemplate")

ForEach Record in qry_FeaturesAssertions
' First the header line
Word.Style = "FeatureHeader" ' Specific tabs
With qry_FeaturesAssertions.CurrentRecord
WriteToWord( .ItemNum & vbTab & .Feature & vbTab & _
.Reference & vbTab & .Priority & vbTab & _
.Comments & vbCrLf)
' Now write the documentation
Word.Style = "FeatureDocumentation"
WriteToWord( .Documentation)
End With


</Access Pig VBA>

Thanks.

-- Mark
 
C

Cindy M -WordMVP-

Hi Mark,
Could someone provide links to building a Word document from Access data,
applying styles and formatting in VBA as the document is constructed? I
think it's just a matter of figuring out the objects needed
yes, certainly that's the case :)

For starters, you may want to look at WdAcc97.zip on my website (mail
merge/special merges section). It's a bit
old, but will give you all the basics. Then you'll be able to ask more
specific questions about what you need that it doesn't cover.

I can't remember whether it covers applying formatting and styles, but
here's a tip:

Work with Word's RANGES (and not with Select and Selection stuff like the
macro recorder would give you). To dump in some text and format as you go:

Dim rng as Word.Range

Set rng = Document.Range

rng.Text = "First page title"
rng.Style = wdStyleTitle
rng.Collapse wdCollapseEnd
rng.Text = "Heading 1 on new page"
rng.Style = wdStyleHeading1 'uses Format/Paragraph/Page Break before
rng.Collapse wdCollapseEnd
rng.Text = "Here's some body text" & vbCR & "And more text in a second
paragraph"
rng.Style = "My own style"
'etc.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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 :)
 
L

Larry Daugherty

Hi Mark,

Of course you don't have to write the code in Word first. I explained it
that way because that's the way I did it on my one and only major project
automating Word via Access.

It seems that you are already much more knowledgeable in the Word
environment than I'd assumed. If you wrote things in VBA in Excel to get
things done you can certainly write the same things in VBA in Access to get
the same things done.
 
M

Mark Jerde

Cindy said:
Hi Mark,

yes, certainly that's the case :)

For starters, you may want to look at WdAcc97.zip on my website (mail
merge/special merges section). It's a bit
old, but will give you all the basics. Then you'll be able to ask more
specific questions about what you need that it doesn't cover.

Yes, this works nicely. Thanks! It doesn't appear to use styles but I'll
take a stab at figuring them out before asking more questions.

Notes for future googlers: When converting the db to Access 2000 file
format you will get conversion errors

Missing or broken VBE reference to the file 'MSJETSQL.TLB'.
There were compilation errors during the conversion or enabling of this
database.

I removed the reference and everything worked fine. This thread was helpful
(tiny and full URLs).
http://tinyurl.com/2rl4y
http://groups.google.com/groups?hl=...id=navclient&ie=UTF-8&oe=UTF-8&q=MSJETSQL.TLB

-- Mark
 
D

david epsom dot com dot au

Missing or broken VBE reference to the file 'MSJETSQL.TLB'.
I removed the reference and everything worked fine. This thread

It is an index file, so that you can get context sensitive
help about JET SQL. Context sensitive help on Jet SQL was
removed when ODBC Direct and Pass Through Queries were
implemented (Access 2.0???). The TLB itself has no usable
content, and the reference may be removed.

(david)


news:%23%[email protected]...
 
C

Cindy M -WordMVP-

Hi Mark,
It doesn't appear to use styles but I'll
take a stab at figuring them out before asking more questions.
Styles is easy enough, once you have the range where you put the
text:
rng.Style = "Style name"

Trickier, is if you're going to be dropping in longer strings,
and want to format "this bit, then that bit". In that case,
break the strings up into groups that use the same formatting,
and:
rng.Text = "This is a paragraph, all the same style" & vbCR
rng.Style = wdStyleHeading1
rng.Collapse wdCollapseEnd
rng.Text = "Text in the next paragraph, but stop here... "
rng.Style = "My Body text"
rng.Collapse WdCollapseEnd
rng.Text = "some text formatted with a character style"
rng.Style = "styleItalic"
rng.Collapse WdCollapseEnd
rng.text = "more plain text"
rng.Style = wdStyleDefaultParagraphFont

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8
2004)
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 :)
 
C

cindy ductan

jiplko
Mark Jerde said:
Yes, this works nicely. Thanks! It doesn't appear to use styles but I'll
take a stab at figuring them out before asking more questions.

Notes for future googlers: When converting the db to Access 2000 file
format you will get conversion errors

Missing or broken VBE reference to the file 'MSJETSQL.TLB'.
There were compilation errors during the conversion or enabling of this
database.

I removed the reference and everything worked fine. This thread was helpful
(tiny and full URLs).
http://tinyurl.com/2rl4y
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=#QYOiaRgCHA.355
6%40tkmsftngp08&rnum=6&prev=/groups%3Fsourceid%3Dnavclient%26ie%3DUTF-8%26oe
%3DUTF-8%26q%3DMSJETSQL.TLB

-- Mark
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top