none
creating a word document with a table populated by data from an Access database

    Question

  • Hi,

    I have created an Microsoft Access database to try to streamline a process at work, however the report format required is a Microsoft Word document. I am having a bit of trouble establishing the best way of getting the data from Access to Word and would appreciate some help if possible.

    The crux of it is that I would like to be able to produce a number of word documents that display the database data in the format I normally use.

    Just to make it complicated each document would require data from about 6 database fields(including a picture and a hyperlink) to be imported into a table(although the number of rows in the table will depend on the number of records specific to each section).

    I am trying to learn VB/VBA and would like to use that, although my priority is to streamline the process.

    Thanks

    Monday, May 14, 2012 3:59 PM

Answers

  • Hi Mick

    I've been busy for a couple of days, and the situation doesn't look like it's going to improve the next few weeks...

    My advice would be to start a new question each time you've reached a "milestone" and one problem is solved. Many people won't look at longish threads, especially if they recognize the names of some answerers (competent reputation). But no one person knows everything, and sometimes "real life" catches up. So a new thread for a new question is never a bad idea. If there's information in a previous discussion that should be considered in a new one, you can provide a link in order to not retype everything.

    As to (1):
    Word has a "heading rows" feature for tables that pretty much does what you request: repeats the row(s) designated as "heading rows" at the top of each page. But it won't work if you insert any kind of manual break - this only functions if Word manages the page breaks.

    Look up the Row.HeadingFormat property in the Word Help :-)

    I'm not sure exactly what you're asking with (2) (header footer for a table, or the document) and where it should go. Please ask this in a new question in order to get the best coverage.


    Cindy Meister, VSTO/Word MVP

    • Marked as answer by MickYoung Friday, May 25, 2012 8:17 PM
    Wednesday, May 23, 2012 2:50 PM
    Moderator

All replies

  • I'm assuming that you are not satisfied with a Report that Access can easily create for you neither you want just copy and paste from Access to Word. Right?

    If you want use VBA from within Access to open Word and tell Word to write something in the cells of a Table in a Document open or a new one you can do it. It not very difficult but is not the first thing I would do if I never programmed in VBA.

    If you want I could give some examples.

    By Lauro

    Tuesday, May 15, 2012 8:58 PM
  • A place to start is the article at http://www.word.mvps.org/FAQs/InterDev/GetDataFromDB.htm. Instead of the "Selection.InsertAfter" statement, you will need a few statements to add a row to the table (assuming your template starts with a one-row table containing headers for the correct number of columns), and to insert each field from the current database record into the proper cell of the new row.

    Some sample code (not usable without modification)...

    Before the loops, insert something like this:

        Dim tbl As Table
        Dim rw As Row
        Set tbl = ActiveDocument.Tables(1)
    (assuming the table you're populating is the first one in the template)

    In place of the Selection.InsertAfter statement, something like this:

        Set rw = tbl.Rows.Add
        rw.Cells(1).Range.Text = myActiveRecord.Fields("Field_1")
        rw.Cells(2).Range.Text = myActiveRecord.Fields("Field_2")
        ActiveDocument.InlineShapes.AddPicture _
            FileName:=XXXXX*, _
            Range:=rw.Cells(3).Range
        rw.Cells(4).Range.Text = myActiveRecord.Fields("Field_4")
        rw.Cells(5).Range.Text = myActiveRecord.Fields("Field_5")

        rw.Cells(6).Range.Text = myActiveRecord.Fields("Field_6")

    *I'm not sure how to handle a picture coming from a database field; the InlineShapes.Add method wants a filename for a disk file. "This exercise is left to the interested student."


    Jay Freedman
    MS Word MVP  FAQ: http://word.mvps.org


    Tuesday, May 15, 2012 9:23 PM
  • Thanks Jay,

    I will look into this, am I right in thinking you are suggesting to use word VBA to pull data from the Access database rather than use Access VBA to push data to Word?

    With regard to the picture, I do think this will be the most difficult data field to transfer, but at this point I would consider it a good start to be able to generate the rest of the document, so thanks again for the suggestion.

    Wednesday, May 16, 2012 7:05 AM
  • Lauro,

    The main reason is the people receiving the reports want it as a word document.

    Your assassment of my ability is spot on, never used VBA before. I have wanted to learn for a while and to have a project will hopefully spur me on. I am going through a teach yourself VB book/study package at the moment to try to expand my knowledge.

    So far I have managed to get Access to open a word document and populate it with some data from a field in my database, a small start but immensely satisfying for a beginner.

    As always any examples or advice are greatly appreciated. 

    Wednesday, May 16, 2012 7:15 AM
  • Hi Mick,

    I expand Jay's sample code as follow, and it's for code run in Access. 

    Sub TestCreateWordTable()
    Dim oWord As Word.Application
    Dim oDoc As Word.Document
    Dim oTable As Table
    Dim oRow As Word.Row
    
    Set oWord = New Word.Application
    oWord.Visible = True
    Set oDoc = oWord.Documents.Add
    
    ' Initiate the table
    Set oTable = oDoc.Tables.Add(oDoc.Paragraphs(1).Range, 1, 3)
    
    ' Set the head of the table
    oTable.Cell(1, 1).Range.Text = "ID"
    oTable.Cell(1, 2).Range.Text = "BookName"
    oTable.Cell(1, 3).Range.Text = "Price"
    
    ' Get the data from the database
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ID As DAO.Field, BookName As DAO.Field, Price As DAO.Field
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tbBook")
    Set ID = rs.Fields("ID")
    Set BookName = rs.Fields("BookName")
    Set Price = rs.Fields("Price")
    
    ' Pass the data from recordset to table
    Do Until rs.EOF
        Set oRow = oTable.Rows.Add
        oRow.Cells(1).Range.Text = ID
        oRow.Cells(2).Range.Text = BookName
        oRow.Cells(3).Range.Text = Price
        rs.MoveNext
    Loop
    
    rs.Close
    End Sub

    Thanks.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, May 16, 2012 9:52 AM
    Moderator
  • Hi Mick It can work both ways, but the Word pulling data from Access is "better" in that no automation of an outside application would be involved. Setting up a database connection to Access will be faster and less "problematic" than automating Word from Access. As Jay mentions, there won't be any simple/easy way to transfer the picture - no matter which way you go. If you were a .NET programmer, I'd probably suggest extracting the MIME data from the database field, converting it to a graphic in memory, placing that on the Clipboard then using the Paste command in Word. Or converting it to valid Word Open XML and using the InsertXML method to bring it in. But VBA doesn't support any of that :-)

    Cindy Meister, VSTO/Word MVP

    Wednesday, May 16, 2012 12:46 PM
    Moderator
  • Thanks Yoyo

    That code was a useful example and I have been able to manipulate it to suit the fields that I wish to transfer (except the photo).

    A big help.

    Wednesday, May 16, 2012 2:03 PM
  • Cindy

    Thanks for the reply, I think based on your and Jays reply I will try to attack this problem from both ends.

    I have had a reasonable attempt (with Yoyo's help) at pushing the data to the document. I think it will be a valuable learning exercise trying to pull the data to Word.

    On your second point - OUCH!! - this could stop me before I have got started. The photo is a vital part of the document. Not being an expert I assume the MIME data is all the extra bits in the field, like file location, type, etc. If VBA doesn't support me accessing that it could be a stopper as accessing the picture file location from the database to put the photo into the document was my plan of attack for the picture move.

    Just when I thought I was getting somewhere, Is .NET similar to VBA or more difficult to get to grips with?

    PS I definitely wouldn't call myself a VBA programmer, or a programmer of any sort, I'm just a beginner trying to exercise my brain.

    Thanks


    • Edited by MickYoung Wednesday, May 16, 2012 2:19 PM
    Wednesday, May 16, 2012 2:18 PM
  • Hi Mick

    I think I misunderstood you on this point:

    <<the picture file location from the database to put the photo into the document was my plan of attack for the picture move.>>

    If you have the path to the picture in the database, then you're OK. I believed you had the picture in the database. But if you have the path, that's perfect :-)


    Cindy Meister, VSTO/Word MVP

    Thursday, May 17, 2012 7:29 AM
    Moderator
  • Cindy,

    I think I may have "muddied the waters" slightly. I think I do have the picture in the database, in that the field it exists in is an attachment field.

    If I look at the attachment field when I try to build a query I see something like this:

    -    Field1

              Field1.FileData

              Field1.FileName

              Field1.FileType

    I sort of hoped that I would be able to tap into this data, create a picture box in the document and use the FileName to drag the picture from the source folder and also create a hyperlink to the file in another table cell.

    Ultimately what I hope to achieve is a table with thumbnail size pictures to reference to with a hyperlink to view the full size picture with supporting text in the other table cells.

    I hope this makes a little more sense. I really appreciate the though you are giving my problem, so far I have managed (with help) to open the document, build a table, populate it with the relevant text from the database - just the photo and the hyperlink stand between me and success for a beginner!

    Mick

    Thursday, May 17, 2012 8:31 PM
  • Hi Mick

    I've worked with Access since its introduction, but never encountered the Attachments field type before. New in 2007, I see...

    After having read through the information in Help, about the only thing I can see is that your program will first need to save the graphics files to disk, then pass that file name to Word to insert the graphics file. Once all the files have been inserted you should be able to delete them again from disk.

    See this article for instructions on how to save attachments to disk:
    http://msdn.microsoft.com/en-us/library/bb257443(office.12).aspx


    Cindy Meister, VSTO/Word MVP

    Monday, May 21, 2012 9:46 AM
    Moderator
  • Thanks Cindy,

    I have come up with a work around, its not ideal but it will give me time to investigate your suggestion.

    I have created another textbox in the form where the user has to insert the photo file name and because the file folder structure will be fixed I have just used this line to add the picture

     oRow.Cells(1).Range.InlineShapes.AddPicture photoloc, linktofile:=False,_ savewithdocument:=True

    where photoloc is a string pointing to the folder with file name from field added on the end.

    A little scruffy but it has allowed me to progress.

    I would prefer to use just the image if it is achievable.

    Thanks

    Mick



    • Edited by MickYoung Tuesday, May 22, 2012 6:58 AM solved problem with columns
    Monday, May 21, 2012 1:33 PM
  • Sorry to keep asking for help, but....

    Using this project has actually helped me start to get to grips with VBA and  my task is progressing reasonably well. My word document is starting to take shape nicely.

    There are a couple of areas where I am struggling a bit and would appreciate some pointers if possible.

       1.   The table draws a new row for each record, when it reaches the end of page I would like to detect the over run onto the next page, insert a page break and redraw the headings row at the top of each new page. - Is this achievable?

       2. Insert a Header and Footer with a detail from another table within the database(Job Name and Date).

    Please do not think I wish to be spoon fed the answers, I am looking for some 'Sagely' advice.

    Mick

    Tuesday, May 22, 2012 10:32 AM
  • Hi Mick

    I've been busy for a couple of days, and the situation doesn't look like it's going to improve the next few weeks...

    My advice would be to start a new question each time you've reached a "milestone" and one problem is solved. Many people won't look at longish threads, especially if they recognize the names of some answerers (competent reputation). But no one person knows everything, and sometimes "real life" catches up. So a new thread for a new question is never a bad idea. If there's information in a previous discussion that should be considered in a new one, you can provide a link in order to not retype everything.

    As to (1):
    Word has a "heading rows" feature for tables that pretty much does what you request: repeats the row(s) designated as "heading rows" at the top of each page. But it won't work if you insert any kind of manual break - this only functions if Word manages the page breaks.

    Look up the Row.HeadingFormat property in the Word Help :-)

    I'm not sure exactly what you're asking with (2) (header footer for a table, or the document) and where it should go. Please ask this in a new question in order to get the best coverage.


    Cindy Meister, VSTO/Word MVP

    • Marked as answer by MickYoung Friday, May 25, 2012 8:17 PM
    Wednesday, May 23, 2012 2:50 PM
    Moderator