VBA - Populate Word Document

QandAdam

New Member
Joined
Jan 12, 2019
Messages
30
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 FieldAccess Field
txtCompanyNameCompanyName
txtAddressLine1AddressLine1
txtAddressLine2AddressLine2
txtPostCodePostCode

<tbody>
</tbody>

Does anybody have the right code to use?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
"Run-time error '94':
Invalid use of Null"

When I click 'Debug', it highlights this row:
Code:
[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?
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top