Can VBA copy a table from Word and be pasted entirely into one single cell in Excel?

[pk247]

Hi All,

I hope the title describes what I'm trying to find out. I hope someone somewhere can advise or provide code that can copy a small table (5 columns, 2 rows, with header) from a Word document and paste the contents of the table into one single cell in excel.

I have tried recording a macro and seeing if I can edit the code but it really doesn't look right at all. Has anyone ever tried to do this successfully? Or maybe you could point me in the right direction? I know I can do it manually by copying the table, double-clicking into a cell and then pasting and all formatting etc is removed but this is ok - I just require the text because I'm trying to do a search for specific words (word docs are in different folders).

Reason for this question is that I am trying to loop through many Word docs and copy a specific table then paste into single cells in Excel. If I need to explain further please let me know.

Many Thanks and fingers crossed this can be done!

[snb]

Sub M_snb()
Cells(1,1) = GetObject(, "word.application").activedocument.Tables(1).Range.Text
End Sub

'formatted' :


Sub M_snb()
Cells(1) = Replace(Replace(GetObject(, "word.application").activedocument.Tables(1).Range.Text, Chr(13) & Chr(7) & Chr(13) & Chr(7), Chr(10)), Chr(13) & Chr(7), Space(4))
End Sub

[pk247]

Thank you so much snb - that is exactly what I needed to help me on my way :yes I love this resource!! Hope I can help others out some day like you guys!