dcsimg
Results 1 to 6 of 6

Thread: Run-time error '5941' in transferring data from Access to Word Template

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    3

    Run-time error '5941' in transferring data from Access to Word Template

    Hi all,

    I am trying to extract data from a column in the Access table "summary" and put it into Word templates I've made ("AccesstoWorddemo.docx")

    I've run the following code:
    Code:
    Public Sub ExportPathtoWord()
        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("C:\Users\donnenfiej\Documents\AccesstoWorddemo.docx")
        Set rs = CurrentDb.OpenRecordset("summary")
        
        If Not rs.EOF Then rs.MoveFirst
        
        Do Until rs.EOF
            wDoc.Bookmarks("Architecture").Range.Text = Nz(rs!Architecture, "")
            wDoc.SaveAs2 "C:\Users\donnenfiej\Documents\DemoFolder" & rs!Link & "_AccesstoWorddemo.docx"
            
            wDoc.Bookmarks("Architecture").Range.DELETE wdCharacter, Len(Nz(rs!Architecture, ""))
            rs.MoveNext
            
        Loop
        
        wDoc.close False
        wApp.quit
        
        Set wDoc = Nothing
        Set wApp = Nothing
        Set rs = Nothing
        
    End Sub
    This generates only the first document, but then the program crashes and throws an error: "The requested member of the collection does not exist."

    Any idea of how to fix this?
    Last edited by Shaggy Hiker; Aug 2nd, 2018 at 09:08 AM. Reason: Added CODE tags.

  2. #2
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,677

    Re: Run-time error '5941' in transferring data from Access to Word Template

    donjo352,

    Welcome to VBForums.

    Now, you should be able to execute this in the IDE. If you do that, you should be able to isolate the specific line of code that's throwing the error. Once you have that, the answer will probably become obvious.

    And just as an FYI, I use the DAO along with Word automation and Word templates all the time. I love it. It's my primary way of report generation.

    Good Luck,
    Elroy
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  3. #3
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    4,677

    Re: Run-time error '5941' in transferring data from Access to Word Template

    Also, just casually staring at your code, if it were me, I'd use that If Not rs.EOF Then line of code to make a multi-line IF statement, and surround the whole loop with it. That might actually be your entire problem. You shouldn't allow your code to fall into that Do...Loop unless you know there are records.

    Actually, I'm not sure what you're trying to do, but you may want to use the .RecordCount property.

    Good Luck,
    Elroy
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  4. #4

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    3

    Re: Run-time error '5941' in transferring data from Access to Word Template

    Hi Elroy,

    Thanks for the quick reply! The highlighted line is the third line of the loop.

    I think the issue is that some lines in the "Architecture" column are blank, but I thought the Nz() function would take care of this...

    Could you further explain that IF statement recommendation?

    -donjo352

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2018
    Posts
    3

    Re: Run-time error '5941' in transferring data from Access to Word Template

    I'll add that it does produce the first Word document, but that's all it does. It breaks after making the first one.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,818

    Re: Run-time error '5941' in transferring data from Access to Word Template

    "The requested member of the collection does not exist."
    i am guessing this would be the collection of bookmarks, maybe the bookmark is being deleted with the content

    it would probably be better practice to add a new document for each iteration of the loop, based on a specific template (save the document you are using as a template), then save the new document and close, that way you need not worry about retaining the bookmark
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width