[Jose Valdes]

Hi MacroPod,

Thank you very much for the VBA script, and I hope to put it to good use
soon.

Can you help with a follow up question: Can I use the INCLUDETEXT field to
display an MS-Excel file in Word 2003? I have tried often, but cannot make
it work.

The Insert File dialog box (Insert=File.) displays a button called "Range".
Click this button, and the help text says, "Type the bookmark name or range
of Microsoft Excel cells you want to insert." But it won't work for me. When
I enter a cell range and link to an Excel file, Word displays a dialog box
called Convert File and asks me to identify the type of source file such as
Plain Text, RTF, or WordPerfect. Excel is noticeably absent from these
options. Of course, if you chose one of these options, you get junk inserted
into your Word file.

The online help for IncludeText says that you can use a \c switch to specify
the "ClassName" of the source file. I have not, however, been able to find a
ClassName for Excel. I have tried "Excel.Sheet.8" without success.

Is using IncludeText with Excel another MS dead-end?

Thanks! José



"macropod" wrote in message
...
Hi Jose,

The short answer is no. For whatever reason, LINK fields behave different
to INCLUDEPICTURE and INCLUDETEXT fields, swallowing any other embedded
fields every time the LINK field is updated. The only way around this at
present is to use vba to recode/recreate the field whenever the document
is opened, updating the paths as you go.

Here's a macro to automatically reset all INCLUDEPICTURE, INCLUDETEXT,
LINK, RD & HYPERLINK fields to point to the current folder. You might like
to modify it to point to a particular child/parent/sibling folder, perhaps
using your DOCPROPERTY variable.

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.
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

Amongst other things, the macro gives feedback on its progress.

To make the macro update only LINK fields, you'd change the line:
If Not .LinkFormat Is Nothing Then
to
If .Type = wdFieldLink Then

Cheers

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

"Jose Valdes" wrote in message
...
I am writing a long manual in MS-Word 2003 that uses OLE links to MS-Excel
files. Here's an example of one of my links:

{ LINK Excel.Sheet.8 "{ DOCPROPERTY home_folder}RMS 2400\\Book
files\\Linked_files\\2400\\Section_2.doc\\RMS_2400 _Slip_Die.xls" "Body"
\a \f 0 \p }

In this example, please note that I use a custom document property,
"home_folder", to store the directory path to the Excel file,
"RMS_2400_Slip_Die.xls". The following example shows the link with the
value of the DOCPROPERTY:

{ LINK Excel.Sheet.8 "G:\\TRS_Engineering\\Operations manuals\\Work in
progress\\RMS 2400\\Book
files\\Linked_files\\2400\\Section_2.doc\\RMS_2400 _Slip_Die.xls" "Body"
\a \f 0 \p }

I thought I was pretty smart when I developed this plan, but Word has won
another round against me. ;-) Every time I update the previous link, Word
helpfully renames the directory path to the following:

{ LINK Excel.Sheet.8
"\\\\spencerroad1\\groups\\TRS_Engineering\\Operat ions manuals\\Work in
progress\\RMS 2400\\Book
files\\Linked_files\\2400\\Section_2.doc\\RMS_2400 _Slip_Die.xls" Body \a
\f 0 \p }

The new directory path is correct and displays the Excel file as it
should. It does not, however, use the DOCPROPERTY. When it was time to
move the word file to another computer, I was hoping to change the
DOCPROPERTY and re-define all of these links. This plan has so far worked
for the INCLUDEPICTURE and INCLUDETEXT fields. Can I make it work with
the LINK field as well?