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 
    
    
    Formatting tags added by mark007

  2. #2
    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
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.

  4. #4
    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
    3,147
    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
    [MS MVP - Word]

  6. #6
    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 
    
    
    Formatting tags added by mark007
    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
    3,147
    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
    [MS MVP - Word]

  9. #9
    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
    3,147
    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
    [MS MVP - Word]

  11. #11
    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
    3,147
    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
    [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
  •