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

Thread: export access query to word vba

  1. #1
    New Member
    Join Date
    Sep 2010
    Posts
    47
    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,234
    Post Thanks / Like
    Mentioned
    0 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
    47
    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,234
    Post Thanks / Like
    Mentioned
    0 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 10:26 PM.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Member
    Join Date
    Sep 2010
    Posts
    47
    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,366
    Post Thanks / Like
    Mentioned
    0 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
    47
    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,234
    Post Thanks / Like
    Mentioned
    0 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,366
    Post Thanks / Like
    Mentioned
    0 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 08: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

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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com