How to export access data to word

[Wolphe]

Hi all!

To best explain, this video is exactly what I want to do, but after setting it up with my specific names, i go to "immediate" I followed the step "exportNamesToWord" then press enter and it gets stuck in "running".

Is this the best method nowadays? Or is there a better way of doing it?



https://youtu.be/JilB511V3AU

[orange]

The sample youtube by Steve Bishop shows you how to move Access data to bookmarks within a Word document.

Please show us what you have tried.

[Wolphe]

The sample youtube by Steve Bishop shows you how to move Access data to bookmarks within a Word document.

Please show us what you have tried.

Here is a pic of the commands. I followed his method to the T, Only thing I changed were the names of the bookmarks and address to where my file is on my PC.

[orange]

Do you get an error?
Did you close the word document before trying to run the code?
Can you make a zip file of your word document with the bookmarks and your database and post it for readers to review?

[Wolphe]

I closed word doc then ran it. Then at top of vba it says "running". And it stays that way and can't do nothing on access nor vba.

I'd have to go to task manager and force close it.

[orange]

Make a zip file of your word document with the bookmarks and your database and post it for readers to review?

Since most of us don't have Acc 2016, you might have to save a copy in an earlier version and use it with the zip so readers can
review/test the database.

[Wolphe]

I'll do that when I get to my laptop. The docs I made are test samples. I'm trying to figure how it works so I can show my boss.

[orange]

No problem. Good luck.

When you show code, it is best to copy it directly into your post or attach it as a file. When you show us a jpg of code we can do knowing (other than read it).

[orange]

I went to the video, and downloaded the workfiles. Extracted the files and ran the code without issue.
I have a reference to Microsoft Word.

[Wolphe]

Make a zip file of your word document with the bookmarks and your database and post it for readers to review?

Since most of us don't have Acc 2016, you might have to save a copy in an earlier version and use it with the zip so readers can
review/test the database.

I just got Office 365 last night. How do I convert them to an older version?
Here is the code again (This time not in pic format)

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

End Sub

[orange]

I got the program to work, but I did have a continuing issue with "running". I had to cancel MSAccess via Taskmanager a number of times.

I found that the word document was locked.
I had to make a copy of the original word doc with the bookmarks, modified the file reference in the code and it worked.

2 examples of output:

Name: Mozart
Address: 66 London Dr
City: London Zipcode: 26898


Thank you for your continued service. Your check of 600 has been processed.

Sincerely,
The Team.


Name: Frank Sinatra
Address: 22 Elm St
City: New York Zipcode: 10128


Thank you for your continued service. Your check of 500 has been processed.

Sincerely,
The Team.

[Wolphe]

So the Word doc was the problem? What do I need to do there?

[orange]

I'm not a regular word user. When I hit the running problem and there was no error message, I tried different things. Stop Access via taskmanager. I found I had 4 instances of word running so I stopped all of them. I then copied the original word and adjusted code to reference the copy and it worked. I also did compact and repair on the database a few times.

[Wolphe]

Awesome. I'll give that a shot.

Thank you so much!

[Wolphe]

I'm not a regular word user. When I hit the running problem and there was no error message, I tried different things. Stop Access via taskmanager. I found I had 4 instances of word running so I stopped all of them. I then copied the original word and adjusted code to reference the copy and it worked. I also did compact and repair on the database a few times.

I've just tried what you had mentioned about copying the word doc and adjust the code in the ACCESS VBA to reference the copy and i'm still getting the same results.

When you said compact and repair on the database? what do you mean and how do you do that?

If I'm understanding correctly, the problem I am having is not in the code or ACCESS, but the Word Doc itself, am I correct?