Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2010
    Posts
    22

    Unanswered: Create Word Document From Access

    Hello All,

    I believe my question is simple, but I am struggling just the same.

    I want to export data from an Access query to a Word document in a tabular format (not addresses, envelopes, or paragraph format ... which I have found a number of examples) using VBA. I would like to use bookmarks in Word so that I can direct the data to a particular location in the document. I want to set up tables in a Word template that already have the desired header row and formatting. Also, as the data changes in the database, I will need to update the Word document. Ideally I would like to use grouping so that a column in the Word table doesn't have to be wasted repeating it for all data. This common data would not be in the table but like a preface to it. I have hundreds of Access records so a grouping mechanism would be great.

    Any help, suggestions, books, or electronic resources that venture past the basics are greatly appreciated.

    Thanks!

  2. #2
    Join Date
    Dec 2010
    Posts
    22

    Update - Creating Separate Tables for Dynamic Data?

    Have made great strides since last post. Can now get Access to communicate with Word placing a single table at the desired place in the document. Still can't figure out multiple tables.

    Example:
    In my Access query, I have John, Amy, and Sid as employees.
    Each employee has attributes such as title, salary, address.

    With my current Access VBA and Word bookmarks, I can create a "header" for John (bookmark 1) and then title, salary, address information (bookmark 2) appears for John, Amy, and Sid. Instead I want a separate heading for each employee with table data appearing after the heading.

    *Current*

    John
    Title Salary Address
    Entry 1 Entry 1 Entry 1
    Entry 2 Entry 2 Entry 2
    Entry 3 Entry 3 Entry 3

    *Desired*

    John

    Title Salary Address
    Entry 1 Entry 1 Entry 1

    Amy

    Title Salary Address
    Entry 2 Entry 2 Entry 2

    Sid

    Title Salary Address
    Entry 3 Entry 3 Entry 3

    Any thoughts? Thanks in advance.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Without providing any code how do you expect to receive suggestions about what you're trying to achieve?

    As far as having multiple headers (one for each record), I was confronted with the same problem a few years ago, but the target document was an Excel sheet. The solution consisted in copying the symbolic information of the target (named cells in Excel, bookmark in Word) then pasting the data into the copy, this for each row in the data set (table or query).
    Have a nice day!

  4. #4
    Join Date
    Jul 2006
    Posts
    30
    There is a software company in Europe that sells an add-on to do just what you want. I used it with an Access 2003 database about 8 years ago and it worked but costs about $500 per user. We now create the document as an Access report and it works much better for 1 - 6 page docs.

  5. #5
    Join Date
    Dec 2010
    Posts
    22

    Access & Word Document Update

    Thank you both for suggestions. Initially yesterday I didn't have code that worked to the point to post anything. It's for a work application, so purchasing an add-in from a vendor likely won't be possible. I initially looked at reports but I will have at least a hundred page of results plus I need additional text in the document.

    Here's the Access VBA:

    Private Sub test2()

    Dim apWord As Word.Application
    Dim doc As Word.Document
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Dim strTemplateName As String

    Set apWord = GetObject(, "Word.Application")
    Set doc = appWord.Documents.Add
    strTemplateName = "d:\data\Template_New.dotx"

    apWord.Visible = True

    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset("qry_test")
    Set doc = apWord.Documents.Add(strTemplateName)

    With apWord.Selection
    .Goto what:=wdGoToBookmark, Name:=" Emp_Header"

    *Do While Not rs.EOF
    .TypeText "Name: " & rs![empName]

    With appWord.Selection
    .Goto what:=wdGoToBookmark, Name:="Table_Start"

    Do While Not rs.EOF
    With apWord.Selection
    .TypeText rs![empTitle]
    .MoveRight unit:=wdCell, Count:=1
    .TypeText rs![empSalary]
    .MoveRight unit:=wdCell, Count:=1
    .TypeText rs![empAddress]
    .MoveRight unit:=wdCell, Count:=1

    End With

    rs.MoveNext

    Loop

    apWord.Selection.Rows.Delete

    End With

    *rs.MoveNext

    *Loop

    End With

    MsgBox "For Testing Only - The End"

    End Sub

    *Lines marked this way have no impact on the program. This was my attempt to group the data like I noted in the previous post.

    Any help is appreciated!

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    My suggestion is macros. I did something similar to what you are trying to do but it is not quite the same. I ended up with pages and pages of code to do it. But I didn't know anything about Word VBA. So I found that recording Macros and then checking the code in the Visual Basic editor really helped me figure out how to do things.

    When you are stuck on something, start recording a macro, then do the action in Word and the stop recording the macro. The code is created by Word and saved as a macro. Very handy.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •