Exporting from Access to Word

[dkkirk2000]

Hello,
I need some help with this code. I am trying to export several word documents from a query. I can get the 3 documents out but the information keeps adding to the previous doc.

The letters in orange are commented out because if I use them the code doesn't work at all, but I believe is suppose to remove the one country and replace it with the next and so on... (but that doesn't work).

thanks for the help!!!

Here is the code I have:

Public Sub ExportAnnextoWord()
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\kirkwdk\Documents\ECPExample.docx")
Set rs = CurrentDb.OpenRecordset("Annexes Query")

If Not rs.EOF Then rs.MoveFirst

Do Until rs.EOF
wDoc.Bookmarks("Country").Range.Text = Nz(rs!Country, "")
wDoc.Bookmarks("CRNumber").Range.Text = Nz(rs!CRNumber, "")
wDoc.Bookmarks("ECPNumber").Range.Text = Nz(rs!ECPNumber, "")
wDoc.Bookmarks("CRTitle").Range.Text = Nz(rs![CR Title], "")
wDoc.SaveAs2 "C:\Users\kirkwdk\Documents" & rs!ID & "_ECPExample.docx"

' wDoc.Bookmarks("Country").Range.Delete wdCharacter, Len(Nz(rs!Country, ""))
' wDoc.Bookmarks("CRNumber").Range.Delete wdCharacter, Len(Nz(rs!CRNumber, ""))
' wDoc.Bookmarks("ECPNumber").Range.Delete wdCharacter, Len(Nz(rs!ECPNumber, ""))
' wDoc.Bookmarks("CRTitle").Range.Delete wdCharacter, Len(Nz(rs![CR Title], ""))

rs.MoveNext
Loop

wDoc.Close False
wApp.Quit
Set wDoc = Nothing
Set wApp = Nothing
Set rs = Nothing
End Sub

Results:

KoreaJapanColombia ECP 123451234512345 for CR-033349,-CR-033349,-CR-033349,- ​

“INITIAL RELEASEINITIAL RELEASELINITIAL RELEASE” ​

 

[The_Doc_Man]

but the information keeps adding to the previous doc.

If you are rebuilding the document from scratch each time, delete the previous incarnation. The VBA "KILL" verb will delete an extant file.

If you are using an "add-on template" (sometimes called "boilerplate") then give it a different name and open it. But when you close the document through the Word App, use the SaveAs option.

If neither of these is what you meant, we might need a better explanation of the problem and/or symptoms, as well as desired results.

As to the items you had commented out showing in orange, I'm not surprised things don't work because normally, a range has to be SET before you can operate on it. So I'm not surprised that the .Delete didn't appear to work.

In case you don't have it, here is a link to an overview for Word and its object structure. You don't actually care about the article directly referenced in the link. You care about the tree-like index list to the left of that article, which lets you browse the various objects exposed by Word App objects.

https://docs.microsoft.com/en-us/office/vba/api/overview/word

 

[Gasman]

Wouldn't each subsequent bookmark setting override the previous.?

 

[Cronk]

Wouldn't each subsequent bookmark setting override the previous.?

No, it inserts text without overwriting what is currently existing.

I do as suggested by Doc, use a template so create a new document each time through the loop, inserting required text, then saving that version of the document.

I use the bookmark(s) as a place marker, not defining a range. A bookmark can be used to define a range but when I tried it that way, the bookmark was removed in the first pass - something I did not expect.

Another odd thing I found was that the intellisense on the document object, did not show 'SaveAs' but rather 'SaveAs2' as used in the OP's code. Microsoft's object model includes SaveAs2 in document methods, but not SaveAs.

 

[The_Doc_Man]

To be honest, Gasman, I have used Word objects so long ago (and not for this kind of editing) that I am not sure. However, labeled bookmarks DO persist because when I used them, it was a "help" document and the HELP button triggered a .GoTo Bookmark operation. So as long as they are named (and the syntax seems to say they are named), they should stick around. Or did you mean something else by that?

 

[Gasman]

Hi The_Doc_Man,

No, I was just enquiring whether the bookmark acted like an access control. Cronk has advised that it does not.

To be honest, Gasman, I have used Word objects so long ago (and not for this kind of editing) that I am not sure. However, labeled bookmarks DO persist because when I used them, it was a "help" document and the HELP button triggered a .GoTo Bookmark operation. So as long as they are named (and the syntax seems to say they are named), they should stick around. Or did you mean something else by that?

 

[Galaxiom]

The block of code before the orange part inserts the data from the recordset. The orange part takes it out again.

Hence the code would appear to do nothing when working perfectly.

What are you actually trying to do?

 

[dkkirk2000]

In case you don't have it, here is a link to an overview for Word and its object structure. You don't actually care about the article directly referenced in the link. You care about the tree-like index list to the left of that article, which lets you browse the various objects exposed by Word App objects.

https://docs.microsoft.com/en-us/office/vba/api/overview/word[/QUOTE]

This link doesn't take me to an article and there is nothing on the left.

 

[dkkirk2000]

this is the query I'm using

ID COUNTRY ECPNumber CRTitle
1 Colombia CR-033349,- INITIAL RELEASE
2 Japan CR-033349,- INITIAL RELEASE
3 Korea CR-033349,- INITIAL RELEASE

The results I want is:
3 Word documents, one for each of the countries with the rest of the information in the query.

 

[Gasman]

Perhaps here?

https://docs.microsoft.com/en-us/office/vba/api/word.bookmark

In case you don't have it, here is a link to an overview for Word and its object structure. You don't actually care about the article directly referenced in the link. You care about the tree-like index list to the left of that article, which lets you browse the various objects exposed by Word App objects.

https://docs.microsoft.com/en-us/office/vba/api/overview/word

This link doesn't take me to an article and there is nothing on the left.[/QUOTE]

 

[The_Doc_Man]

The original link worked for me when I made it and I can click on it today to get to the same place I had in mind. The link you copied that appears in in post #8 ALSO works for me and takes me to the intended place.

I have no idea why the link works for me and not for you, but there are only a few possibilities that would block it. (A) You have a firewall that for some reason blocks that site, and you should get a pop-up telling you that. (B) You are getting another error of some sort, in which case tell us what it is. (C) Your ISP for some unknown reason has a bogus or spoofed version of microsoft.com in its name server cache.

Checking further, Gasman's link ALSO takes you to a member of the documentation group to which I referred. The index is on the left and you can find the top of that list from his reference as well as mine.

FYI, I am using Firefox as my primary browser.

 

[Gasman]

To be fair The_Doc_Man,

The link did not work for me when I tried it, hence looking further. I use Chrome.

Now it works.?

 

[dkkirk2000]

None of this is very helpful. I still don't know how to modify my code to make it create 3 documents with different countries.

 

[Gasman]

You have already been given some advice on how to do that.
If you wish to stick with your code

Create a new document within the recordset loop, write the bookmarks, save the document and close it.

Repeat for each record.?

 

[The_Doc_Man]

Further, note that if you have "boilerplate" stuff, keep that in a separate document. Inside your loop, open the template file, fill it in, and use a SaveAs with the name you want. Using the links, you should be able to find how to use SaveAs or SaveAs2 (as has been suggested for your case).