Word Reference Information into Excel Sheet

Question

Hello,

I currently am working with Microsoft Word 2010 and Microsoft Excel 2010. I have a word document that will be responsible for tracking project information, and I have an Excel spreadsheet Calendar that will show due dates. I would like to know if it is possible to take the date entered in my Word document, and somehow link it to the Excel calendar. I do not want a link inside of my Word document of the calendar rather once a date is entered into Word, I would like this information to populate to the correct date on the Calendar in Excel. I am not sure if this would be an object, or a reference link, or a macro, but I would like to know how this could be done.

Thank you,

Amanda

Answer 1

You could certainly use VBA to insert the date entered into the Word document into Excel.

See the article "Control Excel from Word” at:

http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm

However, I suspect that your whole application should be reconsidered from scratch to obtain the best outcome.

Answer 2

It is possible to link to a bookmarked region of a Word file from Excel (just as you can link to a range of cells in an Excel sheet from Word), but It may not be a very practical way to go about doing things. Links can often spell trouble in Office and are probably better avoided. I also suspect that linking in this direction is not commonly done, so any problems associated with it may not be well known.

But to do it,

a. In Word, make sure you have saved the Word file. Mine is called c:\a\refintoexcel.docx

a. In Word, select the piece of text you want to appear in Excel.

b. Insert->Bookmark and type a name for the bookmark, e.g. "event1"

c. Edit->Copy

d. In Excel, click in the cell where you want the text to appear

e. click Home tab, the down-arrow below the Paste button, Paste Special...

f. Select Paste Link, then either Text or Unicode text, and click OK

The text should appear in the cell. The cell formula will look something like this:

=Word.Document.12|'c:\a\refintoexcel.docx'!'!event1'

You may find that an additional bookmark called something like "OLE_LINK1" is inserted into the Word file during the process and may be used when you Paste Special. In that case, you can modify the formula to use event1 instead of OLE_LINK1

If you now modify the text of the bookmark in Word, the cell text in Excel should also change.

A problem with bookmarks is that the text in them is very easily damaged or deleted. One way you can get around that in this case would be to put every text you want to reflect in Excel in a Content Control in Word (via Word's Developer tab), set the control's properties so that the control cannot be deleted, then follow much the same process as above. That appears to work, but again I suspect it is little-used.

The other thing to bear in mind with links is that if you copy these documents to other people, the Word file may have a different path+name and the links in Excel will probably break.