CODEWITHSUNDEEP
vbaexcel
JG7
JG7

Reputation: 371

Set Word Document Range to Excel Range

I have a larger project which helps generate letters in Excel based on multiple reports (on other sheets) and feeds each letter into a common Word document with page breaks inserted in between each letter. I have attempted to resolve an issue where an error was being randomly thrown stating that the clipboard was not valid. The following code produces this error on occasion:

Error-prone code:

Sub ExportToWordDoc(ws As Worksheet, wordDoc As Word.Document, classCount As Long)
    Application.CutCopyMode = False
    ws.Range("A1:J" & classCount + 8).Copy
    DoEvents    'added in attempt to resolve random error
    Application.Wait (Now + TimeValue("0:00:01"))    'also added in attempt to resolve error
    wordDoc.Range(wordDoc.Content.End - 1).Paste    'line causes intermittent error
    wordDoc.Range(wordDoc.Content.End - 1).InsertBreak Type:=7
End Sub

I believe that the ultimate solution will be to avoid using the clipboard for migrating the data over. Is there a way to do the following? Currently, the code below produces a type mismatch error.

Sub ExportToWordDoc(ws As Worksheet, wordDoc As Word.Document, classCount As Long)
    wordDoc.Range(wordDoc.Content.End - 1).Text = ws.Range("A1:J" & classCount + 8).value
    wordDoc.Range(wordDoc.Content.End - 1).InsertBreak Type:=7
End Sub

Any help would be much appreciated.

FYI: The number of letters generated can be between 10 and 100.

Edit: Some more information. Example image of ws

Upvotes: 0

Views: 1018

Answers (1)

sunsetsurf
sunsetsurf

Reputation: 592

Perhaps you can find a better way in this code. This example takes the range A1:A10 on Sheet 1 and exports it to the first table in an existing Word document named "Table Report". Note: It doesn't use copy.

    Sub Export_Table_Data_Word()

        'Name of the existing Word document
        Const stWordDocument As String = "Table Report.docx"

        'Word objects.
        Dim wdApp As Word.Application
        Dim wdDoc As Word.Document
        Dim wdCell As Word.Cell

        'Excel objects
        Dim wbBook As Workbook
        Dim wsSheet As Worksheet

        'Count used in a FOR loop to fill the Word table.
        Dim lnCountItems As Long

        'Variant to hold the data to be exported.
        Dim vaData As Variant

        'Initialize the Excel objects
        Set wbBook = ThisWorkbook
        Set wsSheet = wbBook.Worksheets("Sheet1")
        vaData = wsSheet.Range("A1:A10").Value

        'Instantiate Word and open the "Table Reports" document.
        Set wdApp = New Word.Application
        Set wdDoc = wdApp.Documents.Open(wbBook.Path &; "\" &; stWordDocument)

        lnCountItems = 1

        'Place the data from the variant into the table in the Word doc.
        For Each wdCell In wdDoc.Tables(1).Columns(1).Cells
            wdCell.Range.Text = vaData(lnCountItems, 1)
            lnCountItems = lnCountItems + 1
        Next wdCell

        'Save and close the Word doc.
        With wdDoc
            .Save
            .Close
        End With

        wdApp.Quit

        'Null out the variables.
        Set wdCell = Nothing
        Set wdDoc = Nothing
        Set wdApp = Nothing

        MsgBox "The " &; stWordDocument &; "'s table has succcessfully " &; vbNewLine &; _
               "been updated!", vbInformation

    End Sub

Upvotes: 1

Related Questions