Linking data in MS Word into Excel

[JohnJ]

At my work place we have many operational Word documents. In each document key information is stored normally in a Word table.

I would like to collect the data from selected word documents and link it into a single spreadsheet. This way users would not have to refer to several documents. The key data used on a daily bases will be displayed and updated on the spreadsheet. This spreadsheet would update all the links when it opens. I tried copying the data and doing a paste special link into the spreadsheet. I selected Word object. That worked but did not enter it into any spreadsheet cells...it was just a movable object. I tried selecting text instead of a Word object and got #name for each cell of the table in excel.

Is there a way to link Word data (tables) into excel to specified cells. Remember I will be doing this from several Word documents and I need to data to remain synchronized. I am using MS Office 2013. Or do I have to use VBA...and where, in the Word document or Excel document?

Thanks,

 

[Becky]

If I'm understanding correctly, don't you want to do it the other way around? ie establish a core spreadsheet with all the data, then link to the specific parts in the Word documents?

 

[JohnJ]

Actually I mead a table from word to excel. The issue is these are formal documents that must contain all the information for each document. However some information from each document is used all the time. So, we want to consolidate these information's in one place, and excel spreadsheet. So, I need to know if a small amount of data in each document can be linked INTO and excel spreadsheet. Sorry for any confusion.

Thanks,

 

[macropod]

Copying from Excel to Word with the Paste Link option only replicates Excel data in Word, not the other way around; to replicate Word data in Excel, you need to copy it from Word and paste it into Excel with the Paste Link option.

 

[JohnJ]

I agree but....I have tried and when I paste Special, Past Link I must select one of the "As:" selections. So I selected Microsoft Word Document Object and this placed the table into excel as a word object. The data did not go into any cell...Not good. So I did the paste Special, Paste Link again selectin "As:" Text or Unicode text. this time I get data in the cells but as error #VALUE!. See attachment. The top table is an object and not data in cells.

The bottom data is the paste special link as text or Unicode text. One of these cells is selected so youu can see the value/link at the top.

Does anyone have any answers for this after reading this string of messages? Remember, I am willing to use VBA but will need so help.

 

[macropod]

If you want the data to go into a cell, you should not chose the 'Microsoft Word Document Object' option, as all that does is embed a copy of the document into the workbook. Instead, you should chose, for example, the 'Text' option. You could have found what works for you by experimenting with the different options...