Consulting

Results 1 to 20 of 20

Thread: Transfer data from Access to Word document

  1. #1

    Transfer data from Access to Word document

    I have a sample database in Access 2003 that I wish to have a command button that will open a word doc. and fill it with data from the database. I've searched through google and have not found any useful examples. I also wish the command button if the file is not found to open a find file command and save the path. Any help would be greatly appreaciated. Also I have several word documents to be filled with data from the database. Or point me in the right direction.

  2. #2
    VBAX Regular
    Joined
    Jun 2009
    Location
    Dorset
    Posts
    60
    Location
    You can do this 2 ways.

    1. By doing a mailmerge in Word that links to a table or query and you can bring through any fields you require.
    2. Use VBA to write data in to the Word document either straight in or using bookmarks.
    Regards
    JD
    ------------------------------------
    Software-Matters
    Dorset

  3. #3
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    Both mailmerge and writing to bookmarks work very well. Mail Merge is great if you want to send the contents of a query that contains multiple records to your word document. The bookmarks method is great if you only want to send over a single record to the document.

    Let us know which one you want, and we'll provide some example code.
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  4. #4
    Bookmarks since my database has no queries.

  5. #5
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    This code will actually create work letters using Bookmarks for every account in a query via a Loop, but you can adapt the code for your own uses.

    You'll need to setup your document with bookmarks ahead of time.

    [vba]Sub ExportQueryToWord()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim doc As Word.Document
    Dim MyWord As Word.Application
    Dim FirstName As String
    Dim LastName As String
    Dim fso As Object
    Dim strFolder As String
    'Check to see if folder exists
    strFolder = '<<<Place the filepath to the folder that contains your word doc
    Set fso = CreateObject("Scripting.FileSystemObject")
    .create the folder if it doesn't exist
    If Not fso.FolderExists(strFolder) Then
    fso.CreateFolder (strFolder) '<<<Create folder if not exist
    End If
    Set db = CurrentDb '<<<Opens connection to current database
    'create a connection to the query that shows the record you want to work with
    Set rst = db.OpenRecordset("QueryName")
    Set MyWord = New Word.Application
    rst.MoveFirst '<<<Implicity move to first record in query
    Do Until rst.EOF
    'document that you want to push records to that contains bookmarks
    Set doc = MyWord.Documents.Open("C:\TestDoc.doc")
    MyWord.Visible = False '<<<works without showing the document
    FirstName = rst!First_Name '<<<Grab first name from recordset as variable value
    LastName = rst!Last_Name '<<<Same for last name
    'paste variables into word document
    doc.Bookmarks("FirstName").Range.Text = FirstName
    doc.Bookmarks("LastName").Range.Text = LastName
    'save and close document
    doc.SaveAs (strFolder & "Name You Want Document To Have.doc")
    doc.Close wdDoNotSaveChanges
    rst.MoveNext
    Loop
    MsgBox "All letters have been created."
    'quit word and release connections
    MyWord.Quit
    Set MyWord = Nothing
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing
    End Sub[/vba]

    HTH
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  6. #6
    Thanks Cregan, I'm not very good at codes, Can you modify this VBA for a record via loop instead of query. Also if filepath to word doc is not found a find file option and save. Thanks in advance

  7. #7
    an example would be helpful or location of one?

  8. #8
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    Can you modify this VBA for a record via loop instead of query.
    If you don't use a query, then where are you getting your records from?

    Also if filepath to word doc is not found a find file option and save.
    The above code is setup so that if the folder filepath is not found, it will automatically create it. If you do not want to have a hardcoded filepath, but want the user to select it, then there are a few articles in the KB and plenty of posts about using the File Dialog window.
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  9. #9
    The database only has a table right now. First Name, Last Name, City, State, Zip. It has 14 records of different individuals. I am trying to get a command button on the database that once activated would populate a word document of the current record showing in the database. Hopes this makes sense.

  10. #10
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    current record showing in the database.
    Do you mean the current record showing on the form?

    If so, you can control this with VBA in a number of ways. The easiest would be to pull the value of the form's objects by referring to them using the 'Me' keyword and the object's name. Example:
    [VBA]Dim FirstName As String
    FirstName = Me.txtFirstName[/VBA]
    where txtFirstName is the name of the textbox object on the form that holds the first name value from your table.
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  11. #11
    Found a How to on TechRepublic "Fill Word form fields with Access data?" did exactly as they instructions indicated and getting "Compile error: user-defined type not defined". I checked the reference and it has a check mark in the DAO 3.X object library. I've been searching for an example to learn from. Any examples would be greatly appreaciated.

  12. #12
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    Does the code require a reference to the Word object model?
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  13. #13

  14. #14
    Hello! so i know i am kind of asking 10 years later but is this code instead of this one FirstName = rst!First_Name '<<<Grab first name from recordset as variable value
    LastName = rst!Last_Name '<<<Same for last name
    'paste variables into word document
    doc.Bookmarks("FirstName").Range.Text = FirstName
    doc.Bookmarks("LastName").Range.Text = LastName ?

  15. #15
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I am not sure what your question is asking.
    That code places the data from the firstname and lastname fields in the bookmarks with the same names as the fields.

  16. #16
    My Question is i am pulling data from a form not a query. so i need to change the code from

    Set db = CurrentDb
    Set rst = db.OpenRecordset("QueryName")

    to something else correct ?
    in other words i want to modify the whole code so i can adapt it on my form! is it possible ?

  17. #17
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, now I understand.
    When using a form instead of a Recordset you use
    me.Fieldname
    where fieldname is the actual name of the field on the form.

    doc.Bookmarks("FirstName").Range.Text = me.FirstName

    Although I use
    doc.Bookmarks("FirstName").Text = me.FirstName

  18. #18
    great Thanks! i also need to change
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Eignerbestand")

    To something like

    Set frm = CurrentForm
    Set frm = DoCmd.OpenForm("Eignerbestand")

    or i should keep it like this since its the record source

  19. #19
    Private Sub Befehl120_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim doc As Word.Document
    Dim MyWord As Word.Application
    Dim txtVorname As String
    Dim txtName As String
    Dim Anrede As String


    Dim fso As Object
    Dim strFolder As String


    strFolder = "C:\Users\ve023g\Desktop\File"
    Set fso = CreateObject("Scripting.FileSystemObject")


    If Not fso.FolderExists(strFolder) Then
    fso.CreateFolder (strFolder)
    End If
    Set frm = CurrentForm
    Set frm = DoCmd.OpenForm("Eignerbestand")




    Set MyWord = New Word.Application
    rst.MoveFirst
    Do Until rst.EOF
    Set doc = MyWord.Documents.Open("C:\Users\ve023g\Desktop\File\zeid.docx")
    MyWord.Visible = False
    Vorname = rst!Vorname
    Name = rst!Name


    doc.Bookmarks("VorName").Range.Text = Me.txtV_Vorname
    doc.Bookmarks("Name").Range.Text = Me.txtName
    doc.Bookmarks("Anrede").Range.Text = Me.txtAnrede


    doc.SaveAs (strFolder & "\NewBrief13.doc")
    doc.Close wdDoNotSaveChanges
    rst.MoveNext
    Loop
    MsgBox "All letters have been created."


    MyWord.Quit
    Set MyWord = Nothing
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing


    End Sub


    This is my code

  20. #20
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    You do not need to set anything to the form if it is already open, so those 2 lines of code and any lines closing or setting to nothing are also not needed.
    It will only be open at 1 record so if you want to transfer more than one record to Word you either have loop through the Form records or Create a RecordsetClone and loop through that.
    So the question is are you trying to transfer one record or all the records?

Posting Permissions

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