Consulting

Results 1 to 3 of 3

Thread: Access Information to Word Table

  1. #1
    VBAX Newbie
    Joined
    Aug 2019
    Posts
    2
    Location

    Access Information to Word Table

    Hi everyone. New here and trying to get information from Access into a two column Word table. Here is the snippet of code. The problem is that I have four names in Access, when it runs through this code I end up with two of the names duplicated. So name 1 is in table row 1 column 1 AND row 1 column2, name 2 is in table row 2 column 1 and row 2 column 2. The last two names are dropped.


    This is what i get:

    John Smith John Smith
    Bill Jones Bill Jones

    This is what I want:

    John Smith Bill Jones
    Ron Brown Amy Smith

    My Code:
    Dim appWord2 As Object 'Word.Application
    Dim WordDoc2 As Object 'Word.Document
    Dim db2 As Object
    Dim rst2 As Object
    Dim strPath2 As String
    Dim strFileName2 As String
    Dim intholder2 As Integer
    Dim intholder3 as integer


    strPath2 = "C:\#Masters"


    Set db2 = CurrentDb
    'Set rst = db.OpenRecordset("qrySelectClientInfo")
    Set rst2 = db.OpenRecordset("SELECT * FROM qryCreateMasterFile_COS WHERE Fileno=" & Forms!frmFileInformation!FileNo)
    strFileName2 = rst2!FileNo & "_COS"
    'Opens word with the appropriate template
    Set appWord2 = CreateObject("Word.Application")
    appWord2.Visible = True
    Set WordDoc2 = appWord2.Documents.Add(strPath & "COSMasterFile.docx")



    intholder2 = 2
    intholder3 = 2
    rst.MoveFirst


    'Populate the table
    Do Until rst.EOF
    appWord2.activedocument.tables(1).cell(intholder2, 1).Range.Text = Nz(rst!FileNo, "") 'change the names of the fields in database
    appWord2.activedocument.tables(1).cell(intholder2, 1).Range.Text = Nz(rst!COSName, "")
    appWord2.activedocument.tables(1).cell(intholder3, 2).Range.Text = Nz(rst!COSName, "")


    appWord2.activedocument.tables(1).Rows.Add
    intholder2 = intholder2 + 1
    rst.MoveNext
    appWord2.activedocument.tables(1).Columns(2).Select
    intholder3 = intholder3 + 1
    rst.MoveNext
    Loop

    Any help would be greatly appreciated. I have tried multiple things and I just never get the results I need.

    Thanks!

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I have never used Access VBA with a word table, only bookmarks.
    However I have noticed in your code that your first 2 lines of code both assign an item to the same cell ie cell(intholder2, 1) which is a bit odd, as the second line should overwrite the first line.
    The other point I would like to make is that being old fashioned I do not like EOF as it is not always reliable, I prefer to establish the number of records in the recordset and then use a For/Next loop to move through the records.

  3. #3
    VBAX Newbie
    Joined
    Aug 2019
    Posts
    2
    Location
    Quote Originally Posted by OBP View Post
    I have never used Access VBA with a word table, only bookmarks.
    However I have noticed in your code that your first 2 lines of code both assign an item to the same cell ie cell(intholder2, 1) which is a bit odd, as the second line should overwrite the first line.
    The other point I would like to make is that being old fashioned I do not like EOF as it is not always reliable, I prefer to establish the number of records in the recordset and then use a For/Next loop to move through the records.
    Thanks for the reply. I have not used the process you mentioned. I will look into that as well.

Posting Permissions

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