Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Pasting from excel to word on specific line or table

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    38
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Pasting from excel to word on specific line or table

    Hello, I am working on a project to copy/paste a single cell from various unique excel sheets to their correlating files.
    This is what I have so far:
    Code:
     Sub CopyandPaste()
    Dim myfile
    Range("j2").Select
    Selection.Copy
    myfile = Application.GetOpenFilename(, , "Browse for Document")
    Dim wdapp As Word.Application
    Set wdapp = CreateObject("Word.Application")
    wdapp.Visible = True
    Dim wddoc As Word.document
    Set wddoc = wdapp.Documents.Open(myfile)
    End Sub
    What I am looking for is code to not only paste it into word, but into the last table of the document.
    So far I have come up with using this:
    Code:
    Sub PasteToWord()
         Dim rangetocopy As Range
         Set rangetocopy = Range("j2").CurrentRegion
         Dim wordapp As Word.Application
         wordapp.Visible = True
         Dim worddoc As Word.document
         Set worddoc = wordapp.Documents.Add
         rangetocopy.Copy
         worddoc.Words(1).PasteExcelTable False, False, False
         
         
         
         
    End Sub
    or this:
    Code:
    selection.GoTo What:=wdGoToLine, Which:=wdGoToFirst, Count:= 39, Name:="" Selection.Find.ClearFormattingWithSelection.Find
    
    .text=""
    .Replacement.Text="" 
    .Forward=True
    .Wrap=wdfindcontinue
    .Format=false
    .MatchCase=False
    .MatchWholeWord=False 
    .MatchWildCards=False
    .MatchSoundslike=False
    .MatchAllWordForms=False
    End With
    I have had trouble implementing either of these to run effectively. Any solutions?

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

    Default Re: Pasting from excel to word on specific line or table

    Is there anything in the Excel workbook to tell Word what files to open? As for the Word table, which cell is to be updated, and is there any content there that needs to be preserved?
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    38
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting from excel to word on specific line or table

    The code I currently have allows me to select which file to open, and it copies the cell that I want. I am looking for code to paste the copied data into the word doc at the end. I don't know if I would be best off just running the excel macro to paste it in word, and then set up a separate word macro to put it where I want.

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

    Default Re: Pasting from excel to word on specific line or table

    You could do that with just:
    Code:
    Sub CopyAndPaste()
    Dim myfile, wdApp As New Word.Application, wdDoc As Word.document
    myfile = Application.GetOpenFilename(, , "Browse for Document")
    Range("J2").Copy
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    wdDoc.Range.Characters.Last.PasteExcelTable False, False, False
    End Sub
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Member
    Join Date
    Jun 2018
    Posts
    38
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting from excel to word on specific line or table

    Quote Originally Posted by Macropod View Post
    You could do that with just:
    Code:
    Sub CopyAndPaste()
    Dim myfile, wdApp As New Word.Application, wdDoc As Word.document
    myfile = Application.GetOpenFilename(, , "Browse for Document")
    Range("J2").Copy
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    wdDoc.Range.Characters.Last.PasteExcelTable False, False, False
    End Sub
    That is so close! Is there a way for me to match the paste to the format of the word doc?

  6. #6
    New Member
    Join Date
    Jun 2018
    Posts
    38
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pasting from excel to word on specific line or table

    Okay so I assigned a bookmark to the table, naming it "PRTable"
    This is how I coded it to appear in Cell (2,4).
    Code:
    Sub CopyAndPaste()
    Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
    Dim oTable As Table
    Dim WriteToDoc As Document
    Dim Sourcedoc As Document
    Set WriteToDoc = ActiveDocument
    myfile = Application.GetOpenFilename(, , "Browse for Document")
    Range("e4").Copy
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    Set oTable = Sourcedoc.bookmarks("PRTable").Range.Tables(1)
    WriteToDoc.Range.InsertAfter oTable.Cell(2, 4).Range.Text & vbCrLf
    Sourcedoc.Close
    Set oTable = Nothing
    Set Sourcedoc = Nothing
     
    End Sub
    I am getting a run-time error '94': object variable or With Block variable not set.

    Any solutions?

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

    Default Re: Pasting from excel to word on specific line or table

    Quote Originally Posted by klutch View Post
    That is so close! Is there a way for me to match the paste to the format of the word doc?
    Depending on what you want to achieve, you could change:
    wdDoc.Range.Characters.Last.PasteExcelTable False, False, False
    to:
    wdDoc.Range.Characters.Last.PasteExcelTable False, True, False
    or:
    wdDoc.Range.Characters.Last.PasteExcelTable False, True, True
    or even:
    wdDoc.Range.Characters.Last.Paste
    Cheers
    Paul Edstein
    [MS MVP - Word]

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

    Default Re: Pasting from excel to word on specific line or table

    Quote Originally Posted by klutch View Post
    Okay so I assigned a bookmark to the table, naming it "PRTable"
    This is how I coded it to appear in Cell (2,4).
    Code:
    Sub CopyAndPaste()
    Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
    Dim oTable As Table
    Dim WriteToDoc As Document
    Dim Sourcedoc As Document
    Set WriteToDoc = ActiveDocument
    myfile = Application.GetOpenFilename(, , "Browse for Document")
    Range("e4").Copy
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    Set oTable = Sourcedoc.bookmarks("PRTable").Range.Tables(1)
    WriteToDoc.Range.InsertAfter oTable.Cell(2, 4).Range.Text & vbCrLf
    Sourcedoc.Close
    Set oTable = Nothing
    Set Sourcedoc = Nothing
     
    End Sub
    I am getting a run-time error '94': object variable or With Block variable not set.

    Any solutions?
    The error is most likely related to your use of Sourcedoc - which you don't define properly and never assign to anything before trying to use it.

    Aside from that, I can't really tell what you're trying to do with the modified code - you copy cell J2, then do nothing with it and instead try to insert something from Sourcedoc into WriteToDoc (which you also don't define properly).
    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
  •