Pasting from excel to word on specific line or table

[klutch]

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:

 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:

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:

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?

 

[Macropod]

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?

 

[klutch]

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.

 

[Macropod]

You could do that with just:

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

 

[klutch]

You could do that with just:

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?

 

[klutch]

Okay so I assigned a bookmark to the table, naming it "PRTable"
This is how I coded it to appear in Cell (2,4).

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?

 

[Macropod]

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

 

[Macropod]

Okay so I assigned a bookmark to the table, naming it "PRTable"
This is how I coded it to appear in Cell (2,4).

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).