VBA - Populate Word Document

[QandAdam]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Word Form Field[/TD]
[TD]Access Field[/TD]
[/TR]
[TR]
[TD]txtCompanyName[/TD]
[TD]CompanyName[/TD]
[/TR]
[TR]
[TD]txtAddressLine1[/TD]
[TD]AddressLine1[/TD]
[/TR]
[TR]
[TD]txtAddressLine2[/TD]
[TD]AddressLine2[/TD]
[/TR]
[TR]
[TD]txtPostCode[/TD]
[TD]PostCode[/TD]
[/TR]
</tbody>[/TABLE]

Does anybody have the right code to use?

 

[welshgasman]

This should get you started.

https://www.google.com/search?q=wor.....69i57j33.13423j0j7&sourceid=chrome&ie=UTF-8

One of those links https://access-programmers.co.uk/forums/showthread.php?t=224511 should take you right there.

 

[QandAdam]

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?

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.

 

[welshgasman]

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

"\\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.

 

[welshgasman]

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.

 

[stumac]

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.

 

[QandAdam]

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:

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

 

[welshgasman]

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

On Error Resume Next
Err.Clear

Also walk through the code with F8 in the debugger instead of hoping for the best.:D

 

[QandAdam]

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

When I click 'Debug', it highlights this row:

[COLOR=#333333].FormFields("txtAddressLine2").Result = Me!AddressLine2[/COLOR]

This can't be this bit of code which is wrong because it works when Word is already open?

 

[welshgasman]

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