How to export access data to word

[isladogs]

Try this slightly modified code



One line of code removed (in GREEN)
Added code in RED

The idea is that it will use an existing instance of Word if it is open
If not error handling code is used to open a new instance of Word

Option Compare Database

Public Sub ExportNamesToWord()
Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim rs As DAO.Recordset

'Set wApp = New Word.Application 'REMOVED
On Error GoTo CreateWordApp
Set wApp = GetObject(, "Word.Application") 

Set wDoc = wApp.Documents.Open("C:\Users\WolphePC\Desktop\ARC \TYLetter\NewLetter.docx")
Set rs = CurrentDb.OpenRecordset("TblNames")

If Not rs.EOF Then rs.MoveFirst

Do Until rs.EOF
wDoc.Bookmarks("FullName").Range.Text = Nz(rs!FullName, "")
wDoc.Bookmarks("Address").Range.Text = Nz(rs!Address, "")
wDoc.Bookmarks("City").Range.Text = Nz(rs!City, "")
wDoc.Bookmarks("Zipcode").Range.Text = Nz(rs!Zipcode, "")
wDoc.Bookmarks("Amount").Range.Text = Nz(rs!Amount, "")
wDoc.SaveAs2 "C:\Users\WolphePC\Desktop\ARC\TYLetter" & rs!ID & "_NewLetter.docx"

wDoc.Bookmarks("FullName").Range.Delete wdCharacter, Len(Nz(rs!FullName, ""))
wDoc.Bookmarks("Address").Range.Delete wdCharacter, Len(Nz(rs!Address, ""))
wDoc.Bookmarks("City").Range.Delete wdCharacter, Len(Nz(rs!City, ""))
wDoc.Bookmarks("Zipcode").Range.Delete wdCharacter, Len(Nz(rs!Zipcode, ""))
wDoc.Bookmarks("Amount").Range.Delete wdCharacter, Len(Nz(rs!Amount, ""))

rs.MoveNext
Loop

wDoc.Close False
wApp.Quit

Set wDoc = Nothing
Set wApp = Nothing
Set rs = Nothing

CreateWordApp:
   ' If getobject fails, then ms-word was NOT running.
   ' The below will then launch word
   Set wApp = CreateObject("Word.Application")
   Resume Next

End Sub

[orange]

I don't know what the exact problem was/is. I don't use word very often, and never with vba. When I had the "running" issue and coud not get Access to respond, I checked taskmanager. At one point there were 4 or 5 instances of word. I cancelled Access and all the word instances. Then found when I went to the word document, it was being used/held (by Access I think). In order to continue, I made a new Word doc( copy of the other and renamed) and adjusted the vba to reflect the new word file name. I ran the program and it worked. As per the copies of 2 output files I sent earlier.
I also tried another test -- I took the word doc with the bookmarks and put $ right before the Amount bookmark, and deleted all of the output files, then ran the vba proc again. This time it ran fine and there was a $500 etc in the output. I also modified one of the amounts to 700.17, After deleting all the outputs again I ran the vba and $700.17 was written.
I wanted to confirm that the $ could be done and see if dollars and cents were handled--since originally only the dollar amounts were being output (500 rather than 500.00). It did work when the "cents" values were non-zero.

My best guess is that when Access was "running" and had to be cancelled via TM, the word file was locked/held/still used by Access.
My attempts to get control of the file failed, so I built another word file.

I suggest you check TM before running the program and make sure there are no word instances in TaskManager--cancel any that are there, since the vba is creating a new instance and that may be the source of the problem.

Link to Compact and Repair

Good luck.

PS: I think ridders change will handle the word is already available or not (Get vs Create). Try it and let us know.

[Wolphe]

Will give that a go, thank you!

I was playing with word more and discovered another option other than bookmarks. It's called "Mail (something)" and it works. I'd have to go back to my laptop later if you'd like to know what I did.

[orange]

Probably mail merge or word merge -- I did use that many years ago.

Good luck with your project.

[isladogs]

Albert Kallal provides a very easy mail merge to Word utility.
The code is very complex but using it is simple - just one line of code

MergeSingleWord

Its available from various places (not all active)
This one definitely works: http://hitechcoach.com/downloads-mai...asy-word-merge