View Single Post
  #2  
Old April 12th, 2011, 04:13 PM
tarquinious tarquinious is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2011
Posts: 34
Default

Quote:
Originally Posted by JenniferJ View Post
I have an excel table with cells that the user will input. I need those cells to populate into a Word document that is a script. I found a way to link the cells to the appropriate positions in word, however you must save then close the word doc and reopen for the word doc to show the changes. My users need to see these changes real time. I have tried to just use excel for the script and use a second tab for the table but the formatting is impossible it seems....is there a better way?

Patiently awaiting a frustration reliever response. Thx
I can think of a couple of options here, but firstly it is important to understand how you managed to get the Word fields linked to the Excel data.

To do this myself, I simply copied the Excel field, and in Word I selected Paste Special/Paste Link.../Microsoft Excel 2003 Worksheet Object. In this way whenever I update that cell in Excel, the value comes across into the Word document.

This is all well and good when I'm changing Excel and viewing Word on the same machine. I take it your concern is when somebody else updates Excel, the only way to view that update in Word on another machine is to close and re-open the Word document?

There are a couple of things that can be done.
1. The user can periodically right-click the field in Word and select Update Link. Not very nice, but functional.
2. You can put a small macro in Word to update this field. The following seems to work OK, and you can adjust the timing of it (I have it set for every 5 seconds, but that may be too frequent). The issue which you may still find is that this will update from the saved version of the Excel file. In which case you could put a similar macro in Excel to save the file every x seconds.

Code:
Private Sub Document_Open()
    Call UpdateLinks
End Sub
Sub UpdateLinks()
    ' Update all the fields in the document
    ActiveDocument.Fields.Update
    ' Call this macro again after 5 seconds from now
    Application.OnTime When:=Now + TimeValue("00:00:05"), Name:="UpdateLinks"
End Sub
Private Sub Document_Close()
    ' Stop any updates after the document is closed
    Application.OnTime When:=Now, Name:=""
End Sub
Let me know if this helps you at all.

Last edited by tarquinious : April 12th, 2011 at 04:18 PM.