Taking data from Excel and inserting into a pre existing Word table?

Question

I know this has been asked previously, but I can only find sites which create the table in Word as you transfer the data.

I have a Word document with 3 tables and want to extract data from an Excel file sent by a client into certain cells within the Word tables. E.G I would like to take the content of cell D3 in Excel and put it in cell 2,2 in the second table in Word? Is there a way to reference which table to insert the data?

Can anyone help with this or just push me in the right direction as I am relatively new to Macros etc. As we use the Word document and have the Excel file sent to us, would making the Macro in Word be more beneficial or not?

Thanks

You should be able to identify the tables by their .Name property. Then you can refer to the table's .Cell(2,2).Shape.Text = "some value" (or something like this -- I'm not in a position to test so I'm writing from memory. If this is all you need to do, whether you choose to bind Excel to Word or Word to Excel is a matter of preference, really. — David Zemens
– David Zemens, Commented May 22, 2014 at 14:24
Cheers David, but forgive me for being a bit stupid - how do I assign a .Name property to a table that already exists? — Jaz
– Jaz, Commented May 23, 2014 at 9:21
you can iterate through each shape in the InlineShapes collection using For each shp in ActiveDocument.InlineShapes then check: If shp.HasTable Then Debug.Print shp.Name, etc. — David Zemens
– David Zemens, Commented May 23, 2014 at 12:55
Thanks for you help David, you gave me a push in the right direction. It wasn't in InlineShapes but under, believe it or not, Tables. I can't believe I missed it the first time round of doing it. Thanks again — Jaz
– Jaz, Commented May 23, 2014 at 14:11
Glad I could help, even if I was mistaken (Tables are a member of the Shapes collection in PowerPoint which is where I do most of my VBA these days, but you're right they are a separate Tables collection in Word). — David Zemens
– David Zemens, Commented May 23, 2014 at 14:24

Jaz · Accepted Answer · 2014-05-23 14:08:29Z

4

I have managed to fix it and typically it is relatively simple, but thanks to @David Zemens for the push in the right direction. I don't know how I missed the "Tables" bit out when searching through "ThisDocument" previously.

 Sub GetData()

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Dim ExcelFileName As String

ExcelFileName = "{Put the directory of Excel file here}"
Set exWb = objExcel.Workbooks.Open(ExcelFileName)

'Set the text of the cell from Excel to the cell in the specified table in Word (the second table in this instance)
ActiveDocument.Tables(2).Cell(2, 2).Range.Text = exWb.Sheets("Test").Cells(2, 1)

' Close Excel bits
exWb.Close
Set exWb = Nothing

End Sub

answered May 23, 2014 at 14:08

Jaz

671 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Taking data from Excel and inserting into a pre existing Word table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related