Access data into Word

[ianforest]

Hi there,

Having searched this forum for many many weeks looking for information for my access project, one of the many queries I have come across from other users is how to merge access data from a currently loaded form/record into Word via a command button.

Every plea from every user has always been unanswered. So, I wonder if any kind soul on this forum would be able to write an idiots guide to doing this. Or provide some example code to help along.

It strikes me that something as simple as what these guys are asking for seems to be ever-so-hard to do in Access. I thought all these apps talked the same language!

So, can someone please, please come up with the goods!

Thanks!

 

[richary]

Hi

Set-by-step guide. Hope I haven't missed anything. I assume you already have a word document with all of the merge fields and formatting etc set up.

1. Create a command button on your form. In its OnClick event type the following code.

strFilename = "C:\Full\Path\to\your\word\doc\mailmerge.doc"

   Dim objWord As Word.Document
   Set objWord = GetObject(strFilename, "Word.Document")
   ' Make Word visible.
   objWord.Application.Visible = True

'filter the recors to the primary key on your form
   objWord.MailMerge.DataSource.QueryString = "SELECT     YourTableOrQueryName.*,  FROM YourTableOrQueryName" & _
        "WHERE (([YourPrimaryKey]= " & Me.YourPrimaryKey & "))"

   objWord.MailMerge.Destination = wdSendToNewDocument
   objWord.MailMerge.Execute

2. Save your code (just in case!)

3. Set a reference to the word object libray using Tools -> References and selecting Word 9.0 Object Library (or similar depending on your system).

4. Run your form and select the recors you wish to merge.

5. Press the button created in step 1.

After a short while you shoud find a new document has been created in Word with the data desired. If you find that Access hangs, switch over to word and check that it isn't complaining about something.

Let me know if you have any problems or if anything in the above isn't clear.

Richard

 

[ianforest]

Hi Richard,

Ok, I decided to give it a whirl, with unfortunately not the results I was expecting. Here is the code I used...

strFilename = "S:\My Documents\Ian\DISCOfever\Sample.doc"

   Dim objWord As Word.Document
   Set objWord = GetObject(strFilename, "Word.Document")
   ' Make Word visible.
   objWord.Application.Visible = True

'filter the recors to the primary key on your form
   objWord.MailMerge.DataSource.QueryString = "SELECT     tblBookings.*,  FROM tblBookings" & _
        "WHERE ((BookingID = " & Me.BookingID & "))"

   objWord.MailMerge.Destination = wdSendToNewDocument
   objWord.MailMerge.Execute

Just to give you a bit of background about my particular access application. The main form where the "word" command button is based on the main table - tblBookings and not a query.

The sample word document I have created has the table tblBookings selected as it's data source.

The field, BookingID is the primary key in tblBookings.

When I click on the button, the whole system just packs in and returns an error. When I run the debugger it highlights the following section of code, therefore I think it may not be right.

'filter the recors to the primary key on your form
   objWord.MailMerge.DataSource.QueryString = "SELECT     tblBookings.*,  FROM tblBookings" & _
        "WHERE ((BookingID = " & Me.BookingID & "))"

Any ideas?

Many thanks.

 

[asid]

Currently I am doing lots with Word automation from Access, but not with Mail-Merging, I did find this article that may help you...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba00/html/MailMergePartI.asp

Is Mail-Merging what you really require? You can insert data easily at bookmarks

example using a string "Los Angeles"
Function FindBMark()
Dim WordObj As Word.Application

' Start Microsoft Word and open the document.

Set WordObj = CreateObject("Word.Application")
WordObj.Documents.Open "C:\test\test.doc"

' Find the bookmark and insert the text.
WordObj.ActiveDocument.bookmarks("Book-Mark A").select
WordObj.selection.Text = ("Los Angeles")

' Close and save the document.
' WordObj.ActiveDocument.Close SaveChanges:=wdSaveChanges

' Quit Microsoft Word and release the object variable.
WordObj.Quit
Set WordObj = Nothing
End Function


Cheers

 

[Bat17]

If you have a look at Helen Feddeama's code page you will find quite a few samples of working with Word froam Access

http://www.helenfeddema.com/CodeSamples.htm

HTH

Peter

 

[richary]

Yes, Helen's page is very good.

Just looking at your SQL I think you need to change

   objWord.MailMerge.DataSource.QueryString = "SELECT     tblBookings.*,  FROM tblBookings" & _
        "WHERE ((BookingID = " & Me.BookingID & "))"

to

   objWord.MailMerge.DataSource.QueryString = "SELECT     tblBookings.*,  FROM tblBookings" & _
        " WHERE ((BookingID = " & Me.BookingID & "))"

ie insert a space before the 'WHERE'.

If that doesn't work, post back and I'll try to help.

 

[ianforest]

Ok a slight deviation from the original plan - rather than doing a mailmerge i'm inserting the data using bookmarks instead. I am using the following code:

Dim wrdApp As Object
    Dim wrdDoc As Object
   
    Set wrdApp = CreateObject("Word.application")
    Set wrdDoc = wrdApp.Documents.Open("C:\Documents and Settings\Ian\My Documents\Test.doc")
    With wrdDoc
      .Bookmarks("Ref").Range.Text = Me.Form.BookingID
      .Bookmarks("Date").Range.Text = Me.Form.EventDate
      .Bookmarks("DJ").Range.Text = Me.Form.ID
      .Bookmarks("Client").Range.Text = Me.Form.ClientName
      .Bookmarks("Venue").Range.Text = Me.Form.VenueName
      .Bookmarks("Fee").Range.Text = Me.Form.Fee
   End With
    wrdApp.Visible = True

Now I have a further problem

The field "ID" is a number field, however it grabs the name of a DJ from another table. The row source is as follows:

SELECT tblDJs.ID, tblDJs.DJName, tblDJs.ShowName FROM tblDJs;

However when I push the data into the Word bookmark it sends the number instead of the name. Is there anyway to send the name or do I need to add some complex SQL?

 

[richary]

from the information you give, I'm taking a guess that the ID field is a combo box which displays the ID (hidden) and the name (displayed)?

If so, replace

Me.Form.ID

with

Me.Form.ID.Column(1).

If not, can you be a bit more specific about the field displayed on the form that you wish to push into word. If all the data needed in the word doc is displayed on the form there should be no need for any SQL.

Richard

 

[ianforest]

from the information you give, I'm taking a guess that the ID field is a combo box which displays the ID (hidden) and the name (displayed)?

Good guess Miss Marple!

And your suggested change worked an absolute treat! I did however have to remove the trailing "." from your code suggestion as the "." caused a compile error.

There's a big cheesy smile in Hove tonight.

 

[richary]

Well spotted Poirot! You spotted my Hastings-like error and as a result, the inhabitants of Hove can look forward to many good nights out!

 

[ianforest]

Well the bookmarks are working great...

however...

Can I format the bookmarks? For example, i'm passing a couple of time fields, dates and currency and would like some control of how they appear in Word, but alas they just appear in a format I don't really like.

Can you control their appearance?

 

[richary]

Formatting, as I'm sure you are aware is the repsonsibility of Word.

Maybe in your document at the bookmark the text is formatted differently?

In Word try using Goto -> Bookmar and selecting one of your bookmarks. If you then start typing text does it use this other unwanted format? If so, check to mak sure that the area around you bookmarks is formatted as you'd wish.

FWIW, I have used bookmarks to insert text into Word from Access and this is certainly possibly without losing formatting.