Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / August 2007

Tip: Looking for answers? Try searching our database.

Updating Excel Links

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
pdberger - 27 Aug 2007 21:44 GMT
Greetings --
This forum was kind enough to help me with a previous version of this
problem, but I'm still having trouble.  Since I'm the most computer-literate
person among the intended users, I thought I'd try to create a fix that's
even easier for others.

My Word document consists of almost nothing more than a couple hundred Excel
links.  I created them simply by copying the Excel cell and pasting into the
right place in Word.  Now that both files are done, and I want users to be
able to download them to their own computers, and fill in data in the Excel
workbook.  The Word 'client' document pulls data from the new Excel file in
the new location.

WHAT I'D LIKE -- the user stores both files in the same folder.  On startup,
the Word file automatically updates all links to the copy of the Excel file
in the same folder.  This would happen every time the Word file opens.

Does someone have a snippet I can use?

Thanks in advance.
Jean-Guy Marcil - 28 Aug 2007 02:31 GMT
pdberger was telling us:
pdberger nous racontait que :

> Greetings --
> This forum was kind enough to help me with a previous version of this
[quoted text clipped - 17 lines]
>
> Thanks in advance.

From what I have seen before, you do not need code for this.

Just make sure that when you create the links in Word (Paste Special with
links) that the Excel workbook sis in the same folder as the Word document.

By default, when opening the Word document, and as long as the Excel
Workbook is kept in the same folder, all links will automatically update.

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

pdberger - 28 Aug 2007 03:34 GMT
merci beaucoups...  c'est tous de ma francaise...

> pdberger was telling us:
> pdberger nous racontait que :
[quoted text clipped - 28 lines]
> By default, when opening the Word document, and as long as the Excel
> Workbook is kept in the same folder, all links will automatically update.
macropod - 28 Aug 2007 10:17 GMT
> From what I have seen before, you do not need code for this.

Actually, you do. If you don't change the links, Word will still try to find the source data according to the original path. That'll
only work if the users copy the files to the exact same folder path/location on their own systems.

Anyway, here's the code you need:

Option Explicit
Dim TrkStatus As Boolean      ' Track Changes flag

Private Sub AutoOpen()
' This routine runs whenever the document is opened.
' It calls on the others to do the real work.
' Prepare the environment.
Call MacroEntry
' Most of the work is done by this routine.
Call UpdateFields
' Set the saved status of the document to true, so that changes via
' this code are ignored. Since the same changes will be made the
' next time the document is opened, saving them doesn't matter.
ActiveDocument.Saved = True
' Go to the start of the document
Selection.HomeKey Unit:=wdStory
' Clean up and exit.
Call MacroExit
End Sub

Private Sub MacroEntry()
' Store current Track Changes status, then switch off temporarily.
With ActiveDocument
   TrkStatus = .TrackRevisions
   .TrackRevisions = False
End With
' Turn Off Screen Updating temporarily.
Application.ScreenUpdating = False
End Sub

Private Sub MacroExit()
' Restore original Track Changes status
ActiveDocument.TrackRevisions = TrkStatus
' Restore Screen Updating
Application.ScreenUpdating = True
End Sub

Private Sub UpdateFields()
' This routine sets the new path for external links, pointing them to the current folder.
Dim oRange As Word.Range
Dim oField As Word.Field
Dim OldPath As String
Dim NewPath As String
' Set the new path
NewPath = Replace$(ActiveDocument.Path, "\", "\\")
' Go through all story ranges in the document, including shapes,
' headers & footers.
For Each oRange In ActiveDocument.StoryRanges
' Go through the fields in the story range.
   For Each oField In oRange.Fields
       With oField
           ' Skip over fields that don't have links to external files
           If Not .LinkFormat Is Nothing Then
               ' Get the old path
               OldPath = Replace(.LinkFormat.SourcePath, "\", "\\")
               ' Replace the link to the external file
               .Code.Text = Replace(.Code.Text, OldPath, NewPath)
           End If
       End With
   Next oField
Next oRange
End Sub

Cheers
Signature

macropod
[MVP - Microsoft Word]
-------------------------

Jean-Guy Marcil - 28 Aug 2007 23:37 GMT
macropod was telling us:
macropod nous racontait que :

>> From what I have seen before, you do not need code for this.
>
> Actually, you do. If you don't change the links, Word will still try
> to find the source data according to the original path. That'll only
> work if the users copy the files to the exact same folder
> path/location on their own systems.

This is true only if the source (the Excel workbook in this case) is in a
folder that is totally unrelated to the Word document folder. If you move
them around, Word will not find the source.

If you place the source in a children folder of the Word document folder,
and always keep the same children path, Word will find it if you move them
around, although it can lose the connection at times.

But if the Excel and Word files are always kept in the same folder, Word
will always find the Excel workbook and rebuild the path (Create the files,
link them, do ALT-F9 in Word to see the paths. Close both files, move them
to a totally unrelated folder, open the Word document and do ALT-F9  and see
the paths have been changed to match the new location.

I have seen this, I think, from at least Word 2000, or it could be 2002, I
am not sure at this point.

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

macropod - 29 Aug 2007 00:29 GMT
> But if the Excel and Word files are always kept in the same folder, Word
> will always find the Excel workbook and rebuild the path

Not in my experience. That's why I wrote the macro.

Cheers
Signature

macropod
[MVP - Microsoft Word]
-------------------------

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.