Export query Access to Word bookmark 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,


d



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

[OBP]

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?

[claven123]

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

[OBP]

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.

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

[OBP]

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


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

[claven123]

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

[macropod]

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/general-excel-discussion-other-questions/713478-word-2007-2010-mail-merge-save-individual-pdf-files-post4796480.html#post4796480
https://www.mrexcel.com/forum/general-excel-discussion-other-questions/1015081-automating-word-mail-merge-excel-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/office/forum/office_2010-word/many-to-one-email-merge-using-tables/8bce1798-fbe8-41f9-a121-1996c14dca5d
and:
http://www.msofficeforums.com/mail-merge/21847-mail-merge-into-different-coloumns.html#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/showthread.php?40408-Solved-from-excel-to-word-(fill-bookmarks)&p=257609&viewfull=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.

[OBP]

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.

[macropod]

why are you not just printing out an Access Report
Read the vert first sentence in the thread:

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.

[OBP]

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.

[claven123]

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

[macropod]

Cross-posted at: https://www.mrexcel.com/forum/microsoft-access/1018703-export-access-query-word-vba.html
Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

[claven123]

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