Solved

Best way to export to Word

Posted on 2004-11-02
2,775 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
Question by:rouxjean
    12 Comments
     
    LVL 8

    Accepted Solution

    by:
    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 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    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
    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
     
    LVL 9

    Expert Comment

    by:Bat17
    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
    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
    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
    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
    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
    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
    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
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    846 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now