Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: VBA - Populate Word Document

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Populate Word Document

    I've been looking at different code to use for this but can't find one that works.

    I'm wanting to create a button which populates a word document with the information currently displayed on the access form.

    Word Form Field Access Field
    txtCompanyName CompanyName
    txtAddressLine1 AddressLine1
    txtAddressLine2 AddressLine2
    txtPostCode PostCode

    Does anybody have the right code to use?

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    666
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Populate Word Document

    This should get you started.

    https://www.google.com/search?q=word...hrome&ie=UTF-8

    One of those links https://access-programmers.co.uk/for...d.php?t=224511 should take you right there.
    Last edited by welshgasman; Jun 30th, 2019 at 04:13 AM.
    Office 2007
    Access novice. Sometimes trying to give something back

  3. #3
    New Member
    Join Date
    Jan 2019
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Populate Word Document

    I've managed to get the code below to be triggered when a button is clicked but when it opens Word, it doesn't open the document or display any page. Any ideas?

    Code:
    Function GenerateAgreement()Dim appWord As Word.Application
    Dim doc As Word.Document
    Dim Path As String
    
    On Error Resume Next
    Error.Clear
    Path = "\\Example\File\Path\Document Name.docx"
    Set appWord = GetObject(, "word.application")
    If Err.Number <> 0 Then
    Set appWord = New Word.Application
    appWord.Visible = True
    End If
    Set doc = appWord.Documents.Open(Path, , True)
    With doc
        .FormFields("txtCompanyName").Result = Me.BusinessName
        .FormFields("txtAddressLine1").Result = Me.AddressLIne1
        .FormFields("txtAddressLine2").Result = Me.AddressLine2
        .FormFields("txtPostCode").Result = Me.PostCode
    
    
    appWord.Visible = True
    appWord.Activate
    End With
    
    Set doc = Nothing
    Set appWord = Nothing
    
    End Function
    I've changed the File Path in this example to make it easier.

  4. #4
    Board Regular
    Join Date
    May 2013
    Posts
    666
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Populate Word Document

    I can't believe you have a document with a path of this?

    Code:
    "\\Example\File\Path\Document Name.docx"
    Try a word document that exists.

    I have just tried your code with a file that exists and it works fine, even though I do not have any formfields in it.
    Office 2007
    Access novice. Sometimes trying to give something back

  5. #5
    Board Regular
    Join Date
    May 2013
    Posts
    666
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Populate Word Document

    Just noticed that you said you changed the path name. Never a good idea, as that could be the problem.?

    Regardless, that code works if a file by that name and path exists.
    Last edited by welshgasman; Jul 24th, 2019 at 02:58 AM.
    Office 2007
    Access novice. Sometimes trying to give something back

  6. #6
    Board Regular
    Join Date
    Jul 2010
    Posts
    459
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Populate Word Document

    Why are you running it as a function and not as subroutine. if the code isn't on the form itself then me wont work, if it is and will only be called from a single button then incorporate into the onlclick sub.

    Take out the on error resume next and run it, that way you should get a better understanding as to why it isn't working.

  7. #7
    New Member
    Join Date
    Jan 2019
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Populate Word Document

    I've finally got round to looking at this again and it works almost perfectly! The only problem I have now is that it only works if I have Word open before clicking on the button. Is there a way to get this to open word?

    The code i'm using now is:

    Code:
    Private Sub Command271_Click()
    
    Dim appWord As Word.Application
    Dim doc As Word.Document
    
    On Error Resume Next
    Err.Clear
    
    Set appWord = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
    
    Set appWord = New Word.Application
    End If
    Set doc = appWord.Documents.Open("\\scc01\Data\Backup\Database\Documents\Agreement XDS213.docx", , True)
    With doc
    .FormFields("txtCompanyName").Result = Me!BusinessName
    .FormFields("txtAddressLine1").Result = Me!AddressLIne1
    .FormFields("txtAddressLine2").Result = Me!AddressLine2
    .FormFields("txtPostCode").Result = Me!PostCode
    .FormFields("txtOurRef").Result = Me!txtOurRef
    .Visible = True
    .Activate
    
    End With
    Set doc = Nothing
    Set appWord = Nothing
    Exit Sub
    errHandler:
    MsgBox Err.Number & ": " & Err.Description
    
    
    End Sub

  8. #8
    Board Regular
    Join Date
    May 2013
    Posts
    666
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Populate Word Document

    Comment these out, so you can at least see any errors.

    Code:
    On Error Resume Next
    Err.Clear
    Also walk through the code with F8 in the debugger instead of hoping for the best.:D
    Last edited by welshgasman; Sep 24th, 2019 at 04:07 PM.
    Office 2007
    Access novice. Sometimes trying to give something back

  9. #9
    New Member
    Join Date
    Jan 2019
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Populate Word Document

    "Run-time error '94':
    Invalid use of Null"

    When I click 'Debug', it highlights this row:
    Code:
    .FormFields("txtAddressLine2").Result = Me!AddressLine2
    This can't be this bit of code which is wrong because it works when Word is already open?
    Last edited by QandAdam; Sep 24th, 2019 at 04:49 PM.

  10. #10
    Board Regular
    Join Date
    May 2013
    Posts
    666
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Populate Word Document

    Well it is going to be flaky if you just simply ignore errors.?

    Surround your form controls with the NZ() function.

    Fix the simple errors first before looking for bigger ones. Access sometimes works in mysterious ways. :D
    Office 2007
    Access novice. Sometimes trying to give something back

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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