Export query Access to Word bookmark VBA

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,

d

Code:

Private Sub ExportToWord_Click() 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(CurrentProject.Path & "\ExportLetter.docx") Set rs = CurrentDb.OpenRecordset("AddressLabelCurrent", dbOpenDynaset) If Not rs.EOF Then rs.MoveFirst Do Until rs.EOF wDoc.Bookmarks("FirstName").Range.Text = Nz(rs!Name, "") wDoc.SaveAs2 "CurrentccProject.Path" & rs!StreetAddress & "_documentname.docx" wDoc.Bookmarks("FirstName").Range.Delete wdCharacter, Len(Nz(rs!Name, "")) rs.MoveNext Loop wDoc.Close False wApp.Quit Set wDoc = Nothing Set wApp = Nothing Set rs = Nothing

Nothing obvious stands out, although the code you are using is different to mine, as mine selects the Bookmark prior to filling it, but as the word docs are Ok that doesn't appear to be a problem.
Can you post a copy of the word doc to test with your code?

1 Attachment(s)

OK

Here is the simple form letter I'm using.

My goal was to make the finish product once I get all the coding correct.

Do I need to have a template vs general doc file?

D

Well I use Template, but I am not sure that you have to.
I will take a look at your code with the word doc tomorrow, it is coming up to bedtime now.

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

I do not know if Macropod response has fixed your problem but this works for me

Code:

Dim rs As Object, m_objWord As Object, m_objDoc As Object, wname As String, SQL As String, nameaddress As String, newname As String Dim fromfile As String, tofile As String, formettedDate As String, count As Long FormattedDate = Replace(Date, "/", "") Set rs = CurrentDb.OpenRecordset("AddressLabelCurrent", dbOpenDynaset) If Not rs.EOF Then rs.MoveLast rs.MoveFirst End If wname = "C:\Users\A C\Downloads\Word\ExportLetter.docx" For count = 1 To rs.RecordCount Set m_objWord = New Word.Application Set m_objDoc = m_objWord.Documents.Add(wname) m_objDoc.Bookmarks("FirstName").Select m_objWord.Selection.Text = rs!FirstName newname = "C:\Users\A C\Downloads\Word\" & rs![FirstName] & " = " & FormattedDate & ".DOC" 'Save Word doc m_objDoc.SaveAs FileName:=newname m_objDoc.Save m_objDoc.Close m_objWord.Quit ' clean up Set m_objWord = New Word.Application ' not actually needed, it opens the new doc to ensure it is OK m_objWord.Visible = True 'the same Set m_objDoc = m_objWord.Documents.Add(newname) ' The same MsgBox "this New Document has been saved as - " & newname ' The same m_objDoc.Close ' The same m_objWord.Quit ' The same rs.MoveNext Next count 'MsgBox "this New Document has been saved" rs.Close Set rs = Nothing Set m_objWord = Nothing Exit_Command1_Click: Exit Sub Err_Command1_Click: MsgBox Err.Description Resume Exit_Command1_Click

You will of course have to change the Path & File names

Yes, that file name was just a placeholder :)

I really don't need to save the doc, I'm only doing that to see the output (so, I can problem solve). I really need to just print them.

Some issues... This will need to run on multiple computers, so I have to have the current project path to get the word doc file.

What is the basic syntax needed to export anything to word from access. And to close it all down at the end.

What is the syntax to print a word doc?

Then I need to determine the coding to get data from the query to the bookmarks.

Thanks, I'll have to look at the code you provided.

D

There are many ways of skinning this cat and the one you choose might depend on what you're trying to achieve.

For example, you might automate a mailmerge from Access - this would be instead of using bookmarks. For code you could adapt for that, see:
https://www.mrexcel.com/forum/genera...ml#post4796480
https://www.mrexcel.com/forum/genera...-2010-vba.html
If you require tabular output, you might even insert a DATABASE field into the Word template, using an SQL statement there to retrieve your data. You could either have your Access VBA code edit the DATABASE field's code or, more simply, create a Document Variable and have your Access code update that. Then, all you'd need to do as a one-off for the DATABASE field is to insert a DOCVARIABLE field into it at the appropriate point. For a demonstration of the DATABASE field's use, see:
http://answers.microsoft.com/en-us/o...1-1996c14dca5d
and:
http://www.msofficeforums.com/mail-m...post67097]Mail merge into different coloumns
The first one describes the set up for use with a mailmerge; the second functions as a stand-alone field.

To update a Word bookmark, see, for example: http://www.vbaexpress.com/forum/show...l=1#post257609

As for printing, that can be as simple as adding a .Printout line to the code or automating Word's print dialogue. Again, it all depends on whether you want the user to choose a printer or simply have the output sent to whatever printer is their current default.

I am not sure that I understand the logic of this exercise, if it is just to print out data for "problem solving" then why are you not just printing out an Access Report, why do you need Word at all for that?
I am happy to assist but totally bewildered.

Quote:

Originally Posted by OBP

why are you not just printing out an Access Report

Read the vert first sentence in the thread:

Quote:

Originally Posted by claven123

I'm trying to make a letter from data from access.

When it comes to writing letters, Access reports simply don't provide the formatting capabilities of Word.

Quote:

Originally Posted by macropod

Read the vert first sentence in the thread:

When it comes to writing letters, Access reports simply don't provide the formatting capabilities of Word.

I must admit that I missed the bit about a "letter" as I was thinking email attachment etc.
I know very well that Word is superior for formatting written output.

I need to print 40 letters. I have the data in an Access and I have a query. I have a letter that I am using, it's a letter head official type letter (template if you will). I will have only a few bookmarks, FullName, Address, City, State, Zip and Name.

I need it to be a simple cmd button, that does it all (for the end user). I, myself could do the export mailmerge function, but the end user could not.

I'll have to look over the coding you mention above.

The original code I have above does not work, see original post. I'm not sure if I have some syntax incorrect. I will of course redo the coding to only print the docs and re-post again.

Thanks,

Dennis

Cross-posted at: https://www.mrexcel.com/forum/micros...-word-vba.html
Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq...._new_faq_item3

Quote:

Originally Posted by macropod

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

For the record this did the trick, works great. It runs, the data is entered at the bookmark and the files are created in the current project path. I just need to adjust it to print vs create files. Will look into that next.

ActiveDocumentPrintout

See the other forum for cross posting comment, very sorry.

Thanks,

d