Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Best way to export to Word

Posted on 2004-11-02
12
Medium Priority
?
2,827 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:rouxjean
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 8

Accepted Solution

by:
JonoBB earned 2000 total points
ID: 12472544
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
 
LVL 58
ID: 12472563
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
 

Author Comment

by:rouxjean
ID: 12472707
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:Bat17
ID: 12474038
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
 
LVL 8

Expert Comment

by:JonoBB
ID: 12474774
Yeah, you design the demo word document and save it as a templete.....it then fills in the the formfields via the code.......
0
 

Author Comment

by:rouxjean
ID: 12475655
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
 
LVL 8

Expert Comment

by:JonoBB
ID: 12480926
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
 

Author Comment

by:rouxjean
ID: 12484909
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
 

Author Comment

by:rouxjean
ID: 12486911
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
 

Author Comment

by:rouxjean
ID: 12497248
I need to export my details, which is going to be dynamic...Can be from 0 to 100 records !!!
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12502362
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
 

Author Comment

by:rouxjean
ID: 12503690
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question