Consulting

Results 1 to 14 of 14

Thread: Export query Access to Word bookmark VBA

  1. #1

    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

    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

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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?

  3. #3

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

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  5. #5
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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
    [Fmr MS MVP - Word]

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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

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

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  10. #10
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by OBP View Post
    why are you not just printing out an Access Report
    Read the vert first sentence in the thread:
    Quote Originally Posted by claven123 View Post
    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.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  11. #11
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Quote Originally Posted by macropod View Post
    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.

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

  13. #13
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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

    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  14. #14
    Quote Originally Posted by macropod View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •