export access query to word vba

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: export access query to word vba

  1. #1
    New Member
    Join Date
    Sep 2010
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default export access query to word 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,




    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

  2. #2
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,443
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: export access query to word vba

    Cross-posted at: Export query Access to Word bookmark VBA
    Please read Mr Excel's policy on Cross-Posting in rule 13: Forum Rules
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Member
    Join Date
    Sep 2010
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: export access query to word vba

    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

  4. #4
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,443
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: export access query to word vba

    Quote Originally Posted by claven123 View Post
    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.
    Last edited by Macropod; Aug 13th, 2017 at 11:26 PM.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Member
    Join Date
    Sep 2010
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: export access query to word vba

    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.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    New Member
    Join Date
    Sep 2010
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: export access query to word vba

    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]

  8. #8
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,443
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: export access query to word vba

    Quote Originally Posted by claven123 View Post
    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.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: export access query to word vba

    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:

    Code:
    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.
    Last edited by xenou; Aug 15th, 2017 at 09:48 AM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #10
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,282
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: export access query to word vba

      
    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.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com