Best way to export to Word

Hi !

I'd like to know what would be my best bet when trying to export data from a table and/or query to a Word document. Data exported will actually be an invoice so there will be a fixed header and a dynamic content depending of how much items there will be ? Code snippet appreciated !

Thanks
rouxjeanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JonoBBCommented:
You can use bookmarks in word, but once the data hasbeen populated, you lose the bookmark for some reason.

Therefore, I use form fields and give each formfield the same name as the field from access. Formfields are not lost once populated, so you can still refer to them.

Here is my full code, which should get you started. Let me know if you need any assistance.

Option Compare Database
Const DOC_PATH As String = "\\Full\Pathto\YourWord\Document\"
Const DOC_NAME As String = "Standard Offer Letter" 'Name of your word document, saved as a Word Template
Option Explicit

Dim appWord As Word.Application
Dim doc As Word.Document
Dim rsModel As ADODB.Recordset
Dim rsContactDetails As ADODB.Recordset
Dim strSQLModel As String
Dim strSQLContactDetails As String
Dim msgA
Dim strDocName As String
Dim blnContactDetailsID As Boolean

On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
    Set appWord = New Word.Application
    Err = 0
End If

'Set up the Word variables
    With appWord
        Set doc = .Documents(DOC_NAME)
        If Err = 0 Then
            If MsgBox("Do you want to save the current document " _
                & "before updating the data?", vbYesNo) = vbYes Then
                    .Dialogs(wdDialogFileSaveAs).Show
            End If
        doc.Close False
        End If
   
        On Error GoTo ErrorHandler
       
        Set doc = .Documents.Open(DOC_PATH & DOC_NAME, , True)
        Set rsModel = New ADODB.Recordset
       
        strSQLModel = "SELECT tblModelUnits.*, tblModelSite.ProjectName" & _
                    " FROM tblModelUnits " & _
                    " INNER JOIN tblModelSite ON tblModelUnits.ModelID = tblModelSite.ModelID" & _
                    " WHERE tblModelUnits.ModelID =" & Me.txtModelID & ";"
   
        rsModel.Open strSQLModel, CurrentProject.Connection, _
                adOpenStatic, adLockReadOnly
       
       
            With doc
            'Populate word
            .FormFields("FormField1").Result = Nz(rsModel!Field1, 0)
            .FormFields("FormField1").Result = Nz(rsModel!Field1, 0)
             'Etc
           End With
   
       
        .Visible = True
        .Activate
       
    End With

'Tidy up
    Set rsModel = Nothing
    Set rsContactDetails = Nothing
    Set doc = Nothing
    Set appWord = Nothing
    Exit Sub

ErrorHandler:
MsgBox Err & Err.Description
End Sub

By the way, this uses ADO recordsets, so you will need to define a reference in access vba: Tools>References and then make sure that Microsoft Active X Data Objects 2.X is selected
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Hi rouxjean,

  I have a sample database of the basic technique that JonoBB described.   It's a fully automated interface to word from the Access side using bookmarks in the target document.  Drop me a line at jimdettman"at"earthlink.net and I'll forward a copy.

Jim.
0
rouxjeanAuthor Commented:
JonoBB: does that mean I can create the demo word file and then insert the field where I want to ? Or do I have to design the Word document on the fly with code ??? (How can I design the word document at first ??? using office97).
Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bat17Commented:
Helen Feddema has done several articles on working with Word and has code samples on here site as well. It is well worth the effort of having a read there :-)

http://www.helenfeddema.com/

Peter
0
JonoBBCommented:
Yeah, you design the demo word document and save it as a templete.....it then fills in the the formfields via the code.......
0
rouxjeanAuthor Commented:
JonoBB: why can't I save it as a doc ? I tried your code but it didn't do nothing...I tried both setting a field and a bookmark, both names FormField1 ....My sql was returning values...All I got was a blank page !
0
JonoBBCommented:
You can save it as a doc if you want...no problems with that, but might cause a sharing violation if a couple of poeple try access the same doc at the same time.

First, make sure that your recordset (and not just the sql) is returning values...if you are 100% sure that the recordset is returnign records, then the problem is with the word doc.

To set up the formfields, get the forms toolbar running (right click on any existing toolbar and select Forms toolbar), then click Text form field on the toolbar.
Once the field is actually inserted in word, double click the field and in the popup box go to the sectin called "Field Settings" and set up the bookmark name there.

You can also set up the format of the text field, i.e. text, date, percent, integer, etc

That should be it....just try it with a single record first until you get it right...then bring in all the data
0
rouxjeanAuthor Commented:
JonoBB:SELECT * FROM [Quote-Details] returns 6 records...however I got a recordcount of only 1 !!!
Here si the code, i know it's still dirty but i'll clean it up later :
=====================================================================
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rsModel As ADODB.Recordset
Dim rsContactDetails As ADODB.Recordset
Dim strSQLModel As String
Dim strSQLContactDetails As String
Dim msgA
Dim strDocName As String
Dim blnContactDetailsID As Boolean
Dim dbs As DAO.Database
Dim rs As DAO.Recordset

Const DOC_PATH As String = "C:\access\"
Const DOC_NAME As String = "model2.dot" 'Name of your word document, saved as a Word Template

On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
    Set appWord = New Word.Application
    Err = 0
End If

'Set up the Word variables

        Set doc = appWord.Documents(DOC_NAME)
        If Err = 0 Then
            If MsgBox("Do you want to save the current document " _
                & "before updating the data?", vbYesNo) = vbYes Then
                    appWord.Dialogs(wdDialogFileSaveAs).Show
            End If
        doc.Close False
        End If
   
       
        Set dbs = CurrentDb()
        strSQL = "SELECT * FROM [Quote-Details]"
        Set rs = dbs.OpenRecordset(strSQL)
        If rs.RecordCount = 0 Then  'Aucun client trouvé
            MsgBox "Customer Not Found"
        Else                        'Client trouvé
             
        Set doc = appWord.Documents.Open(DOC_PATH & DOC_NAME, , True)
       
       MsgBox rs.RecordCount
       
            With doc
            .FormFields("FormField1").Result = Nz(rs![QuoteNo], 0)
           End With
   
        appWord.Visible = True
        appWord.Activate

End If
    Set rsModel = Nothing
    Set rsContactDetails = Nothing
    Set doc = Nothing
    Set appWord = Nothing
    Exit Sub
=====================================================================
Any ideas, it does import but only one record...
0
rouxjeanAuthor Commented:
Ok got it fixed by a rs.movelast and rs.movefirst. However it is not dynamic....a bookmark is only good for one record....what do you suggest then ?
0
rouxjeanAuthor Commented:
I need to export my details, which is going to be dynamic...Can be from 0 to 100 records !!!
0
JonoBBCommented:
OK, gotcha,

I think that the formfields method only really supports one record at a time (which we can get around), but in your case, I would then strongly suggest that you set up a mail merge document.

This should get you pointed in the right direction:
http://www.tek-tips.com/faqs.cfm?fid=3237
0
rouxjeanAuthor Commented:
Hi the link you posted makes a copy of the word document for each record ! What I want is the detail to be repeated in the same document, if it fits !!! Any ideas as of how to modify this code ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.