Problems putting Access data in Word doc

[DanielGreenwood]

Hi,

I’ve got a real problem with merging data stored in Access into a Word document.

The document is a report containing descriptions of work to be carried out on a particular site. At the end of the description is a section which summarises the predicted spend for that site. Currently the summary data is manually typed in using figures from my access database.

I am now trying to cut the manual process out and link the figures in the database to the word document dynamically. However I cannot seem to do this successfully.

I’ve been playing with the mail merge feature for some time now but to no avail. It doesn’t seem to have the functionality I’m after.

I’m using Word and Access 97, but have 2000 as well if its any better to solve my problem.

I do quite a bit of programming in VBA but have had little experience in automation.

Any ideas?

 

[clapper62]

I don't know if automation is the most efficient answer to your problem but here is some coding examples that might get you start. I tested this in Access 2000 but should work in 97 though

You need to set a reference to the
"Microsoft Word 9.0 Object Library"
for this code to work with the Dim Statements included(recommended)

Dim WordApp As Word.Application
Dim WordDoc As Document

page_num = 1
'this creates the top level object
Set WordApp = CreateObject("Word.Application&quot
'this adds a new document to WordApp
WordApp.Documents.Add
'the InsertAfter command simply inserts text after all other text in the document
WordApp.Documents(1).Range.InsertAfter ("Meters Ready To Return To Factory &quot & Now() & vbCr
WordApp.Visible = True

WordApp.Documents(1).Range.InsertAfter (" Page : &quot & page_num & vbCr

WordApp.Documents(1).Range.Font.Bold = True
WordApp.Documents(1).Range.Font.Size = 8
WordApp.Documents(1).Range.Font.Name = "Comic Sans MS"
WordApp.Documents(1).Range.InsertAfter vbCr
WordApp.Documents(1).Range.InsertAfter ("Meter Serial Number : &quot & vbCr

Once you get the document created and start adding to it the best place for answers is in MS Word Help Files under
Programming Information

 

[JoyInOK]

DanielGreenwood
Between playing with the datasource and Word mail merge, we should be able to do just about anything. It sounds like you need to use a query that looks something like this:

SiteNO SiteName CostForA CostForB FormulaOf$Expected
1 Hugh Inc. 45.00 55.00 $100.00

And you need the merged document to look like this:

Site No: 1
Site Name: Hugh Inc.

Summary: With a cost for A of <<CostForA>> and a cost for B of <<CostForB>>, the total expected cost for this project is <<FormulaOf$Expected>>.

You are probably having trouble formatting the currency in Word. Either use an expression in your query to format the field ( FormattedCostA: formatcurrency([CostForA])) or use &quot;switches&quot; in Word to format the number.

Is this close to the problem you're having?

 

[pgmm]

Hi.
I use an access database to produce invoices in word.
It works fine selecting the access database and table as the source data when I set up the word mail merge.
Regards