Using DOCPROPERTY in LINK field

[Jose Valdes]

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\\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 }

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?

 

[macropod]

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

 

[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é

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

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\\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 }

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?

 

[macropod]

Hi Jose,

If you edit the LINK field by changing 'LINK' and the Excel object reference to 'INCLUDETEXT', you'll get something that looks like
a Word table. It will lose much of the Excel formatting and it may be more trouble than its worth to create the necessary styles etc
in Excel to restore the formatting for Word - if you make the changes directly in Word, they'll be lost as soon as you update the
field.

Cheers

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

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é

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

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\\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 }

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?

 

[Jose Valdes]

Hi MacroPod,

Thanks! I won't experiment anymore with using IncludeText to link to Excel
because the conversion to MS-Word sounds less than desirable under the best
of circumstances. I'm going to focus on using your VBA script to keep the
LINK fields pointed to the right files. Thanks to you, I'll spend more time
writing my manual rather than experimenting with MS Word.

Thanks! José

Hi Jose,

If you edit the LINK field by changing 'LINK' and the Excel object
reference to 'INCLUDETEXT', you'll get something that looks like a Word
table. It will lose much of the Excel formatting and it may be more
trouble than its worth to create the necessary styles etc in Excel to
restore the formatting for Word - if you make the changes directly in
Word, they'll be lost as soon as you update the field.

Cheers

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

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é

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

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\\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 }

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?

 

[Cindy M.]

Hi Macropod,

If you edit the LINK field by changing 'LINK' and the Excel object reference to

'INCLUDETEXT', you'll get something that looks like

a Word table.

Caveat: only as long as the spreadsheet converter is installed on the system. And
this is no longer being distributed with Office (last time was 2002, I think).

-- Cindy Meister

 

[Jose Valdes]

Thanks Cindy! I'll search http://office.microsoft.com for the spreadsheet
converter!

 

[Cindy M.]

Hi Jose,

I'll search http://office.microsoft.com for the spreadsheet
converter!

You won't find it.

With the more strenuous security measures, Word now requires
that file converters be signed. A few years ago, Microsoft had
to decide which converters they'd rewrite (most were bought
from third parties) and sign, and which they'd drop. The
spreadsheet converter fell into the latter category.

If you still have an older version of Office and install it
before you install the newer version, including the converter,
then the converter will still be there and be recognized by
the newer version. An older converter can also be copied from
an older machine.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun
17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[macropod]

Hi Cindy,

What's with the converter reference? From my reading of the OP's issues, Excel is already installed - it's just a matter of creating
dynamic links to allow for path changes.

Cheers

 

[Cindy M.]

Hi Macropod,

What's with the converter reference? From my reading of the OP's issues, Excel is already installed - it's just a matter of creating
dynamic links to allow for path changes.

It's because the OP want's to use IncludeText (rather than Link). IncludeText means you have to have the converter.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail