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?
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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
822
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
822
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
822
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
822
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,665
Messages
5,488,179
Members
407,630
Latest member
Mehezabin

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top