Copying from Word and pasting into Excel

[nims]

I'm trying to create code for excel that will allow me to open a series of word documents, copy certain rows from tables in those docs, and paste them into the excel worksheet.

I know how to open word from excel, but my knowledge ends there....

Can someone please help me, by explaining how to open a word doc, copy a table/row, and paste it into excel?

Thank you!

[RobDog888]

Welcome to the Forums.

Use the Tables collection to access the rows of a table for reading into a variable and then setting a cell in Excel to that var. etc.

[nims]

I think I need to use the copy and paste methods, because setting a cell's value equal to a row from a table in Word does not retain proper formatting of the cells.

Is there any way to copy specific rows (say from table(1) and table(3)) from word, and paste them onto specific rows in Excel?

[RobDog888]

In word you can use Selection and the Windows clipboard to copy and then set the cell value to the clipboard.gettext to "paste it in.

[nims]

Using the clipboard is giving me an error, while using range.copy and range.past is pasting the text with odd formatting.

Here is my code, using clipboard. what is wrong with this?

Dim oWrd As Object
Set oWrd = CreateObject("Word.Application")
Dim oDoc As Object
Set oDoc = CreateObject("Word.Document")
Set oDoc = oWrd.Documents.Open("c:\OMA\TIJ.doc")

oDoc.Tables(1).Rows(1).Range.Select
Selection.Copy
ActiveWorkbook.Worksheets(1).Cells(5, 5) = Clipboard.GetText

[nims]

If use this code to copy and paste...

oDoc.Tables(3).Rows(3).Range.Copy
ActiveSheet.Paste

....it pastes the data properly, but I cannot specify where in the worksheet to paste it.

[RobDog888]

This line is redundant and not needed.

Set oDoc = CreateObject("Word.Document")

You can set the ActiveCell to the position where you want to paste it.

[nims]

How can I specify where to paste the rows?

ActiveSheet.Paste seems to paste it properly, but not in the location I want.

[dannymking]

You can either specify the destination on the ActiveSheet.Paste or set the range object and pastespecial..

ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A1")

or

Range("A1").PasteSpecial xlPasteAll

[nims]

Thanks,

I did it like this:
ActiveWorkbook.Worksheets(1).Cells(aRow, iCol).Value = Application.Clean(.Cell(iRow, iCol).Range)