export access query to word vba

[claven123]

I'm trying to make a letter from data from access. I have about 40 or so records to print out on a work document. I've set up the document with bookmarks. I'm getting the data from a query.

So far this sorta runs.... I click the cmd button.... nothing happens. When I click on the actual file name, it says it's read only, then if I click thru it open and then the it runs.... creating saving the 40 or so files in the documents folder. Then when I open one of the word files it is formatted correctly.

I would like it to print these out, don't really need to save them. But, for testing purposes I'm just saving them, I can change that function later.

How do I get this to run from the cmd click? What am I missing?

Thanks,

[COLOR=#465584][FONT=Courier]Private Sub ExportToWord_Click()[/FONT][/COLOR]

[COLOR=#465584][FONT=Courier]    Dim wApp As Word.Application[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Dim wDoc As Word.Document[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Dim rs As DAO.Recordset[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        Set wApp = New Word.Application[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        Set wDoc = wApp.Documents.Open(CurrentProject.Path & "\ExportLetter.docx")[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        Set rs = CurrentDb.OpenRecordset("AddressLabelCurrent", dbOpenDynaset)[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    If Not rs.EOF Then rs.MoveFirst[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    Do Until rs.EOF[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        wDoc.Bookmarks("FirstName").Range.Text = Nz(rs!Name, "")[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]               [/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        wDoc.SaveAs2 "CurrentccProject.Path" & rs!StreetAddress & "_documentname.docx"[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        [/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]        wDoc.Bookmarks("FirstName").Range.Delete wdCharacter, Len(Nz(rs!Name, ""))[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]      [/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]    rs.MoveNext[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]   Loop[/FONT][/COLOR]

[COLOR=#465584][FONT=Courier]   wDoc.Close False[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]   [/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]   wApp.Quit[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]   Set wDoc = Nothing[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]   Set wApp = Nothing[/FONT][/COLOR]
[COLOR=#465584][FONT=Courier]   Set rs = Nothing[/FONT][/COLOR]

 

[Macropod]

Cross-posted at: Export query Access to Word bookmark VBA
Please read Mr Excel's policy on Cross-Posting in rule 13: Forum Rules

 

[claven123]

I'm sorry I cross posted this question. I visited the link and the sub link in the rules. I've posted this question to that forum, this one and I think and a couple more in a full disclosure format. My intent was not to be malicious and or deceitful in anyway. I've noticed over the years that some forums will produce results more than others and sometimes I get a reply that is very far off base, then never will get a reply to the original question, so I've noticed that if I post to other boards I end up with good responses to my questions. i realize why this is discouraged.

I will refrain from doing this in the future.

D

 

[Macropod]

I've posted this question to that forum, this one and I think and a couple more in a full disclosure format. My intent was not to be malicious and or deceitful in anyway

We don't have a rule against cross-posting, only about what you're expected to do when you do so. The main concern is that people don't waste their time going over ground that's been covered elsewhere.

Please provide full cross-post links on all of the forums concerned.

 

[claven123]

vba - Access To Word With Bookmarks From Query - Stack Overflow
Access To Word With Bookmarks From Query - UtterAccess Forums

D

 

[xenou]

This is unclear:

When I click on the actual file name, it says it's read only, then if I click thru it open and then the it runs.... creating saving the 40 or so files in the documents folder.

What actual file name are you clicking on? Are you saying you are doing this in the middle of running your code? When you say nothing happens do you mean literally nothing? How can nothing be happening if at the end you get your 40 or so saved files! Clearly something is happening.

 

[claven123]

I click on the cmd button, nothing happens, well something happens sort of.

I then navigate in the documents folder for the target word file, the one access will export the data to. I double click this and it opens with an error, that the file is open read only. I then say yes and the word doc opens. THEN it starts to run the code, ie placing a name in each instance of word and after this is done, I have 40 word files saved in the documents folder. It does nothing until until I open the word doc.

However, the issue has been solved.

Instead of:
wDoc.SaveAs2 "CurrentccProject.Path" & rs!StreetAddress & "_documentname.docx"
use:
wDoc.SaveAs2 CurrentccProject.Path & "" & rs!StreetAddress & "_documentname.docx"

Presumably you're using something more meaningful than 'documentname', too...​

Cheers
Paul Edstein
[MS MVP - Word]
​

 

[Macropod]

I then navigate in the documents folder for the target word file, the one access will export the data to. I double click this and it opens with an error, that the file is open read only. I then say yes and the word doc opens.

You could change:
Set wDoc = wApp.Documents.Open(CurrentProject.Path & "\ExportLetter.docx")
to either:
Set wDoc = wApp.Documents.Open(CurrentProject.Path & "\ExportLetter.docx", ReadOnly:=True)
or:
Set wDoc = wApp.Documents.Add(CurrentProject.Path & "\ExportLetter.docx")
with the second approach, you could omit:
wDoc.Close False
because your document is being treated as a template.

 

[xenou]

It's possible that the code is waiting for a dialog box to be clicked (somewhere, perhaps invisibly). I would try the open read only approach (and other suggestions) above. On the other hand, your code might be the thing that has the file open already ... which is very strange why it wouldn't be able to continue.

As a general debugging method, I'd add something to the code so you know if the file is opened successfully or not:

Set wDoc = wApp.Documents.Open(CurrentProject.Path & "\ExportLetter.docx")
If not wDoc is nothing then
    msgbox "file opened!"
else
    msgbox "file not opened!"
end if

Or ... actually ... I'd just step through the code line by line.

 

[Micron]

I double click this and it opens with an error, that the file is open read only.

Because Access still has a lock on it (not you)? Try closing the newly saved document in code before you navigate to the folder, or for testing, close then reopen in code to save yourself the trouble of navigating. You should then be able to review the fields. I don't think it's running any code when you open it - I think it's creating a copy based on the locked copy, which takes a moment to recreate the data in the bookmarked fields.