Solved

Access to Word Automation

Posted on 2003-11-17
11
745 Views
Last Modified: 2012-06-27
Most of my experience in automating word from access has been using bookmarks and populating the contents of a single document with the contents of one record.  However I now have the need to do this on a mass basis.  That is, instead of one record at a time, it may be between 50 and 700 at a time.  Any time I have needed to do multiple records like this, I have done a mail merge manually.  I am wondering if the same methods I use with bookmarking with a single record can be reused when Im doing multiple records, or should I set this up as a mail merge instead.  Ideally I would like to set up the code so it can be used in any scenario.  The source for this merge will be a query, select statement, or recordset.  The document will be a one page word document with bookmarks or merge fields(whichever is necessary).  I need the results to reside in one word document.  Any thoughts/examples on this process would be appreciated.  I realize this question is vague, but I think I  need more help on the general direction and concepts of this operation than the actual code writing.

Bill
0
Comment
Question by:BillPowell
  • 6
  • 5
11 Comments
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9767512
If you have some general text on your page (hard coded text) like a letter or something, and between it some data from your database, it will become difficult to stay using Bookmarks ...

If you stay using BookMarks, I think you must for each record create a new document (based on a Template with the bookmarks).

I think for multiple records, the Mail Merge functionality fits better ... You create just one template, you connect it to your datasource and you can for example print all your records ...

What you could do:
1. Temp-Table
You could make a Temp-Table in your Access-DB where you store the result of your query (the records you want to print, ...). Connect this Temp-Table via Mail Merge to your Word-Doc. So, after filling the Temp-Table, you Word-Document shows all the necessary records.

2. Temp CSV-File
If you have a multi-user application, the above solution is a bit difficult, unless you store also the username in this temp-table. The solution I worked out once (it was not with Access, but a combination of a Progress Database and Word) ...
It was for creating letters to one or more clients with some text ... but always the same heading (name, address, ... of the client).
- The user first selects the template to use
- Then, the user selects the client or clients (so one or more records)
- For every new Word File (request), I first created a Temp-File (via Win-API, so your file is always unique!)
- I fill this Temp-File with all the requested records in CSV-format
  (so the first line stores the rows, the other lines are the different records.
- After the creation, I dynamically connected this file as datasource to the new Word Document
- Once connected, the document showed all the requested records.

Advantage of using the Temp-File functionality, is that you can open different Word-Docs to your database at the same time. If you would use always the same file as datasource, it will conflict when opening more then 1 document based on that datasource.

If you want some code ... just scream ;-)
0
 
LVL 11

Author Comment

by:BillPowell
ID: 9768336
Thanks for the response.  I suspected as much about the bookmarks.  In this particular case, although the db is multiuser, only one person will be performing merge operations, so a conflict is unlikely.  With the temp file functionality, why does it need to be a temp table.  Would a temp query not suffice.  Yes please on the code.  If you can give an example of the use of mail merge via code, that would be great.

Bill
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9768358
Or you use a Temp-Table (Solution 1), or you use a temp-file filled by a Query - Temp Query (Solution 2) ...
Indeed, you don't need a Temp Table together with the Temp File ...

OK, I will post some code tomorrow ...
0
 
LVL 7

Accepted Solution

by:
wsteegmans earned 500 total points
ID: 9770242
Here some code ...

I created the Mail Merge with an Text-file (CSV-file). An example of such a file could be this one:

ID;Lastname;Firstname;Street;City
1;Williams;John;Some Street;Some City
2;MyName;MyFirstName;MyStreet;MyCity

The code to open a Word-Doc and to Mail Merge it with a given DataSource is listed below.
Few remarks:
- I used for the different files (Word-Doc and MM-File) always a Scripting.File variable, ... So, I can show you where to use just the FileName, and where you should use the Full Path ...
- Because we use the Word Library and the Scripting Library, you have to reference them in your module-code
- When you will open a Word Doc with a Mail Merge ... This Word Doc should be opened once with a Mail Merge with the same format. So, the first time you build your Word Doc ... Once your Word Doc is styled, and the MM Fields are included, you can MM this document with new Datasources (same format, different data)
- Use the last Procedure (Test) to test some things ...

You can create now a new Module in Access, drop all code in it, and test it ...

Option Compare Database
Option Explicit
 
Private appWord As New Word.Application

'------------------------------------------------------------------------------
' Purpose: Open a Word Document through COM-automation
'          There are two parameters (both of the type Scripting.File
'          The first parameter is the WordDoc to open
'          The second parameter is optional and holds the Mail Merge File ...
'   Notes:
'------------------------------------------------------------------------------

Function OpenWordDocument(filWordDoc As Scripting.File, Optional MailMergeFile As Variant = Nothing) As Boolean
 
    On Error GoTo Err_OpenWordDocument
 
    Dim wdoc As Word.Document
    Dim filMailMergeFile As Scripting.File
   
    OpenWordDocument = True
 
    If Not IsObject(appWord) Then
        Set appWord = New Word.Application
    End If
   
' - Open the requested document ...
    If Not IsOpenDoc(filWordDoc) Then
        Set wdoc = appWord.Documents.Open(filWordDoc.Path)

' - - - Set the mail merge settings ...
        If Not (MailMergeFile Is Nothing) Then
            Set filMailMergeFile = MailMergeFile
            wdoc.MailMerge.OpenDataSource filMailMergeFile.Path, ConfirmConversions:=False, ReadOnly:=True, linktosource:=True
            wdoc.MailMerge.ViewMailMergeFieldCodes = False
        End If
    Else
        MsgBox "Document is already opened in Word ..." & vbNewLine & _
            "The document will not be opened again.", vbInformation, "Document already open ..."
        appWord.Documents.Item(filWordDoc.Name).Activate
    End If
   
    appWord.Visible = True
    appWord.Activate
    appWord.WindowState = wdWindowStateMaximize
 
Exit_OpenWordDocument:
   
    Set wdoc = Nothing
    Exit Function

Err_OpenWordDocument:
' - Do some errorhandling
    MsgBox Err.Number & " - " & Err.Description
    OpenWordDocument = False
    GoTo Exit_OpenWordDocument
   
End Function

'------------------------------------------------------------------------------
' Purpose: Checks if a Document is alreay open in the current Word Session ...
'   Notes:
'------------------------------------------------------------------------------

Function IsOpenDoc(filWordDoc As Scripting.File) As Boolean

    Dim docX As Word.Document
    Dim blnResult As Boolean
   
    blnResult = False

' - Do a loop through all the open docs, and try to find the one we're opening ...
    For Each docX In appWord.Documents
        If StrComp(docX.Name, filWordDoc.Name, vbTextCompare) = 0 Then
            blnResult = True
            Exit For
        End If
    Next
   
    IsOpenDoc = blnResult
   
End Function

'------------------------------------------------------------------------------
' Purpose: This function prints all the data located in the Mail Merge file
'          combined with the Word Document immediately ...
'          So, MS Word is hidden for the user, and the output of the
'          Mail Merge is send to the printer ...
'   Notes:
'------------------------------------------------------------------------------

Function PrintMailMergeDocument(filWordDoc As Scripting.File, filMailMergeFile As Scripting.File) As Boolean

    On Error GoTo Err_PrintMailMergeDocument
 
    Dim wdoc As Word.Document
   
    PrintMailMergeDocument = True

    If Not IsObject(appWord) Then
        Set appWord = New Word.Application
    End If

    If IsOpenDoc(filWordDoc) Then
        appWord.Documents.Item(filWordDoc.Name).Close SaveChanges:=False
    End If
   
    Set wdoc = appWord.Documents.Open(filWordDoc.Path)
   
' - Some states of the main Word App
    appWord.WindowState = wdWindowStateMinimize
    appWord.Activate
   
' - Mail Merge ...
    wdoc.MailMerge.OpenDataSource filMailMergeFile.Path, ConfirmConversions:=False, ReadOnly:=True, linktosource:=True
    wdoc.MailMerge.Destination = wdSendToPrinter
    wdoc.MailMerge.Execute
    wdoc.Close

' - Close Word, if no other docs are open ...
    If appWord.Documents.Count = 0 Then
        appWord.Quit
        Set appWord = Nothing
    End If

Exit_PrintMailMergeDocument:

    Set wdoc = Nothing
    Exit Function

Err_PrintMailMergeDocument:
' - Do some error handling
    MsgBox Err.Number & " - " & Err.Description
    PrintMailMergeDocument = False
    GoTo Exit_PrintMailMergeDocument
   
End Function

Sub Test()

    Dim fso As New Scripting.FileSystemObject
    Dim filDoc As Scripting.File
    Dim filMM As Scripting.File
   
    Set filDoc = fso.GetFile("e:\mydocs\doc1.doc")
    Set filMM = fso.GetFile("e:\mydocs\mm1.csv")
   
    OpenWordDocument filDoc, filMM
    PrintMailMergeDocument filDoc, filMM
   
    Set filDoc = Nothing
    Set filMM = Nothing
    Set fso = Nothing

End Sub
0
 
LVL 11

Author Comment

by:BillPowell
ID: 9776435
OK
Ill get back to you after Ive tried it out.
Thanks
Bill
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 11

Author Comment

by:BillPowell
ID: 9798943
Sorry for the delay.  Ive been able to implement a slimmed down version of your solution.  Thanks for the advice.

Bill
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9799002
What did you use as datasource: textfile, excel-sheet ... ??
0
 
LVL 11

Author Comment

by:BillPowell
ID: 9860339
Actually, I am just getting around to implementing this solution today.  Since there will only be one user at a time performing merges, I decided to use a query as a datasource.  At the beginning of the merge, I delete the query, recreate it and append it to the query collection, then begin the merge process.  What modifications would I have to make to this code to use a query from the current db as the datasource instead of an external source.  If you answer in this post, I will post some points for you as a new question later.  If you prefer I open a new question now to let the other experts in on it, I will do that.  I just figured it was easiest to ask the guy who wrote it first.

Regards,

Bill
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9865467
Hi Bill,

> What modifications would I have to make to this code to use a query from the current db
> as the datasource instead of an external source.

I think it will be more easy (the code). Because, in your Word Document, you must select once your RecordSource (Query), and that's it. Because you delete and recreate this query all the time, and the name of the query doesn't change, we don't have to set the MailMerge DataSource again and again. I did it in the above code, because we always create a file with a different name ...

So, setting the Mail Merge via code isn't necessary anymore ...

But, if you want to use different queries, with different names ... I changed the code a bit to do that. Changed only the OpenWordDocument function. More explanation it the code ...

Function OpenWordDocument(filWordDoc As Scripting.file, Optional QueryName As String = "") As Boolean
 
    On Error GoTo Err_OpenWordDocument
 
    Dim wdoc As Word.Document
   
    OpenWordDocument = True
 
    If Not IsObject(appWord) Then
        Set appWord = New Word.Application
    End If
   
' - Open the requested document ...
    If Not IsOpenDoc(filWordDoc) Then
        Set wdoc = appWord.Documents.Open(filWordDoc.Path)

' - - - Set the mail merge settings ...
        If Not (Len(QueryName) = 0) Then
' - - - - - We link the current database by Application.CurrentProject.FullName
' - - - - - As Connection, we use QUERY and the QueryName (Parameter)
' - - - - - Instead of using QUERY, you could also use TABLE, for MailMerging a table
            wdoc.MailMerge.OpenDataSource Name:=Application.CurrentProject.FullName, _
                ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
                AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
                WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
                Format:=wdOpenFormatAuto, Connection:="QUERY " & QueryName
            wdoc.MailMerge.ViewMailMergeFieldCodes = False
        End If
    Else
        MsgBox "Document is already opened in Word ..." & vbNewLine & _
            "The document will not be opened again.", vbInformation, "Document already open ..."
        appWord.Documents.Item(filWordDoc.Name).Activate
    End If
   
    appWord.Visible = True
    appWord.Activate
    appWord.WindowState = wdWindowStateMaximize
 
Exit_OpenWordDocument:
   
    Set wdoc = Nothing
    Exit Function

Err_OpenWordDocument:
' - Do some errorhandling
    MsgBox Err.Number & " - " & Err.Description
    OpenWordDocument = False
    GoTo Exit_OpenWordDocument
   
End Function
0
 
LVL 11

Author Comment

by:BillPowell
ID: 9892580
Sorry for the tardiness.  Its been crazy lately.

So far, so good, but I have a couple of questions about the code.  In the original module code there is a print function that hides word and sends to printer.  Does it send to default printer.  Can you specify the printer, tray to select from etc  (in some instances the user has special letterhead loaded in tray 2 of a certain printer)?

I intend to execute the mail merge in two ways, one the hidden word document you have given, the other, opens the mail merge document and performs the merge so the user can see it, print it, save to another document etc.  Can the print function you showed, be modified to do the latter?

Thanks again for your efforts.

Bill
0
 
LVL 11

Author Comment

by:BillPowell
ID: 9909283
Hey,

You can ignore my last post, I figured it out.  See this link for points:

http://oldlook.experts-exchange.com/Databases/MS_Access/Q_20821344.html

Thanks again.

Bill
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

759 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

23 Experts available now in Live!

Get 1:1 Help Now