• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Export from Excel to Word and display Run-time error '5491'

marreco

Member
Hi i try export data from excel to word, but display
Run-time error '5491'
the requested member of the collection does not exist
My file is in \Download and name is correct, way error?
Code:
Sub Direct_Mail()

Dim ws As Worksheet
Dim n As Long
Dim r As Long, rLast As Long
Dim c As Long, cLast As Long
Dim appWord As Word.Application
Dim doc As Word.Document

Set ws = ActiveSheet
Set appWord = CreateObject("Word.Application")
appWord.Visible = True
With ws
    rLast = .Cells(.Rows.Count, "A").End(xlUp).Row
    cLast = .Cells(1, .Columns.Count).End(xlToLeft).Column
    For r = 2 To rLast
        Set doc = appWord.Documents.Open(Filename:="C:\Users\marreco\Downloads\test.docx") 'this line yellow
        For c = 1 To cLast
            doc.Bookmarks(.Cells(1, c)).Range.Text = .Cells(r, c)
        Next c
        n = n + 1
        doc.SaveAs2 Filename:="C:\Users\marreco\Downloads\" & Format(n, "000"), FileFormat:=wdFormatXMLDocument
        doc.Close
    Next r
End With
End Sub

Thank you!!
 
Try the following slightly adjusted code:

Code:
Sub Direct_Mail()

Dim ws As Worksheet
Dim n As Long
Dim r As Long, rLast As Long
Dim c As Long, cLast As Long
Dim appWord As Object

Set appWord = GetObject(wdInputName, "Word.document")
appWord.Application.Visible = True

Set ws = ActiveSheet
With ws
  rLast = .Cells(.Rows.Count, "A").End(xlUp).Row
  cLast = .Cells(1, .Columns.Count).End(xlToLeft).Column
  For r = 2 To rLast
  Set doc = appWord.Documents.Open(Filename:="C:\Users\marreco\Downloads\test.docx") 'this line yellow
  For c = 1 To cLast
  doc.Bookmarks(.Cells(1, c)).Range.Text = .Cells(r, c)
  Next c
  n = n + 1
  doc.SaveAs2 Filename:="C:\Users\marreco\Downloads\" & Format(n, "000"), FileFormat:=wdFormatXMLDocument
  doc.Close
  Next r
End With
  
End Sub

If this doesn't help can you please post a sample of the activesheet as real data would be nice
 
Hi Hui, sorry feedback late, i like it, but i need more time to finish tests.

Anyway, I want to thank you for your help, thank you !!
 
Hui

-- Trying to understand and apply this code to a project I'm working on... I'm confused by the following line:

Code:
Set appWord = GetObject(wdInputName, "Word.document")

Can you clarify what the variable <wdInputName> is doing? I don't see it defined elsewhere in your code.

Thanks.
 
Last edited:
Hui

-- Please disregard above. I found an alternate solution that is working fine.

All best...
 
Back
Top