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?
 

QandAdam

New Member
Joined
Jan 12, 2019
Messages
30
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.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
679
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.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
679
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:

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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

New Member
Joined
Jan 12, 2019
Messages
30
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
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
679
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:

QandAdam

New Member
Joined
Jan 12, 2019
Messages
30
"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:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
679
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
 

Forum statistics

Threads
1,078,461
Messages
5,340,440
Members
399,375
Latest member
alwayssunny

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top