Two clicks to generate word document from access form, with double rich text copied using vba

Question

I've been working in exporting a rtf (rich text) form a memo field in access 2010 to a word file with a bookmark. The problem is that It is necessary two clicks to open the word document, and then, the text is inserted twice. I'm still not able to find the problem.

Here is the code:

Option Compare Database

Private Sub Comando72_Click()
'Saves the current record -->
Me.Dirty = False
Dim appWord As Word.Application
Dim doc As Word.Document
Dim objWord As Object  '' Word.Application
Dim fso As Object  '' FileSystemObject
Dim f As Object  '' TextStream
Dim myHtml As String
Dim tempFileSpec As String

' grab some formatted text from a Memo field
myHtml = DLookup("DescripActivAEjecutarse", "PlanificacionServiciosInstitucionales", "IdPSI = Form!IdPSI")
Set fso = CreateObject("Scripting.FileSystemObject")  '' New FileSystemObject
tempFileSpec = fso.GetSpecialFolder(2) & "\" & fso.GetTempName & ".htm"
'' write to temporary .htm file
Set f = fso.CreateTextFile(tempFileSpec, True)
f.Write "<html>" & myHtml & "</html>"
f.Close
Set f = Nothing
Set fso = Nothing
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
    'If Word isn't open, create a new instance of Word.
    Set appWord = New Word.Application
End If
'set the doc for future use.
Set doc = appWord.Documents.Open("C:\Users\earias\Documents\SOLICITUD-Yachay-automatica2.docx", , True) 'True default (just reading).
'locates bookmark and inserts file
appWord.Selection.GoTo what:=wdGoToBookmark, Name:="bookmark_1"
appWord.Selection.InsertFile tempFileSpec

Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
    MsgBox Err.Number & ": " & Err.Description
End Sub

Mark Horner Mark Horner · Accepted Answer · 2015-04-07T19:37:34

If you are pressing the button twice it will run the procedure twice?

In terms of your current code, after this line Set doc = appWord.Documents.Open add the following;

doc.visible = true

This should enable you to view the document that's open when you press the button once. To prevent the window from popping up you could also instead of setting it to visible do;

doc.saveas "path here"

then set all to nothing and close off as you would and the file will be saved where you want it saved without needing to manually save as each time.

You could look at setting up a simple mail merge with a template and then saving-as the template to whichever format you choose and break the mailmerge link (my preferred method).

Let me know how you get on!

Two clicks to generate word document from access form, with double rich text copied using vba

1 Answers