Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Copy data from excel to word

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy data from excel to word

    Please help in copying data from excel 2007 to word 2007 with a macro button. Urgent

    I have a range of data from A1:I34 in the excel workbook "Sheet1" data in that range need to be copied to existing Word document "November".

    And if macro is ran for second time it should copy the data in the same rang A1:A34 of Sheet1 to page 2 of existing word document "November".

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

    Default Re: Copy data from excel to word

    Try something along the lines of:
    Code:
    Sub SendRangeToDoc()
    'Note: A reference to the Word library must be set, via Tools|References
    Dim wdApp As New Word.Application, wdDoc As Word.document, StrNm As String
    StrNm = "C:\Users\" & Environ("UserName") & "\Documents\November.docx"
    If Dir(StrNm) <> "" Then
      wdApp.Visible = True
      With wdApp
        'open the Word Document
        Set wdDoc = Documents.Open(Filename:=StrNm)
        With wdDoc
            Sheets("Sheet1").Range("A1:I34").Copy
            .Characters.Last.InsertBefore Chr(12)
            .Characters.Last.Paste
            If .Characters.First = Chr(12) Then .Characters.First.Delete
            .Close True
        End With
        .Quit
      End With
    Else
      MsgBox "File: " & StrNm & " not found."
    End If
    Set wdDoc = Nothing: Set wdApp = Nothing
    End Sub
    Adjust the filepath to suit.
    Last edited by Macropod; Nov 23rd, 2017 at 05:44 PM.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Member
    Join Date
    Nov 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data from excel to word

    I have tried to replicate the above code with required modification and it says Compile error: User defined type not allowed. Following is the code that I have used. Please help me to debug if there is any error with the code/reference.

    Sub SendRangeToDoc()
    Dim wdApp As New Word.Application, wdDoc As Word.document, StrNm As String
    StrNm = "C:\Users\keerthiM\Documents\NOVEMBER2017"
    If Dir(StrNm) <> "" Then
    wdApp.Visible = True
    With wdApp
    'open the Word Document
    Set wdDoc = Documents.Open(Filename:=StrNm)
    With wdDoc
    Sheets("Sheet1").Range("A1:I34").COPY
    .Characters.Last.InsertBefore Chr(12)
    .Characters.Last.Paste
    If .Characters.First = Chr(12) Then .Characters.First.Delete
    .Close True
    End With
    .Quit
    End With
    Else
    MsgBox "File: " & StrNm & " not found."
    End If
    Set wdDoc = Nothing: Set wdApp = Nothing
    End Sub

  4. #4
    New Member
    Join Date
    Nov 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data from excel to word

    I think I have failed to add proper reference. Please guide to do so. I have opened Visual basic>Tools>Reference>Microsoft object 12.0 Objectt library is checked in. Am I supposed to check any other reference other than the above.

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

    Default Re: Copy data from excel to word

    Quote Originally Posted by Mkeerthi View Post
    I think I have failed to add proper reference. Please guide to do so. I have opened Visual basic>Tools>Reference>Microsoft object 12.0 Objectt library is checked in. Am I supposed to check any other reference other than the above.
    The code I posted includes a note (which you deleted from your code) telling you what reference to need to set.

    PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  6. #6
    New Member
    Join Date
    Nov 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data from excel to word

    There is an improvement. I am able to fix the reference problem. I have checked Microsoft Word 12.0 Object library. Now that Compile error: User defined type not allowed is no more. But I am getting file not found error though I have enabled proper path reference. Please help me if I am missing some where.

  7. #7
    New Member
    Join Date
    Nov 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data from excel to word

    Code:
    This is the code I have exactly pasted in my visual basic. Now the error is remote server machine does not exist or is unavailable
    
    Sub SendRangeToDoc()
    Dim wdApp As New Word.Application, wdDoc As Word.document, StrNm As String
    StrNm = "C:\Users\keerthiM\Documents\NOVEMBER2017.docx"
    If Dir(StrNm) <> "" Then
      wdApp.Visible = True
      With wdApp
        'open the Word Document
        Set wdDoc = Documents.Open(Filename:=StrNm)
        With wdDoc
            Sheets("Sheet1").Range("A1:I34").COPY
            .Characters.Last.InsertBefore Chr(12)
            .Characters.Last.Paste
            If .Characters.First = Chr(12) Then .Characters.First.Delete
            .Close True
        End With
        .Quit
      End With
    Else
      MsgBox "File: " & StrNm & " not found."
    End If
    Set wdDoc = Nothing: Set wdApp = Nothing
    End Sub

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

    Default Re: Copy data from excel to word

    Please clarify the nature of the problem. First you say it's 'file not found', then you say it's 'remote server machine does not exist or is unavailable'.

    '.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  9. #9
    New Member
    Join Date
    Nov 2017
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy data from excel to word

    file not found got rectified after I added .docx behind NOVEMBER2017 in the code. Now 'remote server machine does not exist' is the problem. Many thanks for your patience.

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

    Default Re: Copy data from excel to word

    Your previous mention of 'Microsoft Word 12.0 Object library' suggests you're using Office 2007, so there shouldn't be any trouble accessing a docx file. Did you disable any other references when you added the Word one? If you did, you should re-enable them.
    Last edited by Macropod; Nov 27th, 2017 at 02:13 AM.
    Cheers
    Paul Edstein
    [MS MVP - Word]

Some videos you may like

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
  •