[lumpjaw]

Hello,

Here is a scenario. I have a folder which I am going to use various forms
from we will call this the template folder.
I have an excel file called information.xls
I have a word file called work.doc
I will use the two files over and over again and create new new directories
in which to put them.
I will change information in the spread sheet r1:c1

What I want to do is create a link, embedded object or hyperlink (not sure
how to do this) from the word document to the excel document (as source)
I want to reference r1:c1 in the spreadsheet. and put it into the word
document.

Like I said I want these two files to be templates of sort, so wherever the
two files are together it will know where to get the information.
I do not want to change the linking source information each time I copy.

Is there a way to link an item and reference it like this...

\\information.xls

and not like this...

c:\template\information.xls


Thanks.




--

Please post your responses to this newsgroup so that
we may all learn from your expertise.

-lumpjaw

[macropod]

Hi lumpjaw,

Word doesn't permit the use of a path like that. However, since the files
will be kept in the same folder, inserting the following macro into the Word
document will update the link paths to point to the current folder on
opening.

The code is extensively commentd, and reports its progress on the status
bar - useful if there are many links to update.

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

Cheers

--
macropod
[MVP - Microsoft Word]


"lumpjaw" wrote in message
...
Hello,

Here is a scenario. I have a folder which I am going to use various forms
from we will call this the template folder.
I have an excel file called information.xls
I have a word file called work.doc
I will use the two files over and over again and create new new
directories
in which to put them.
I will change information in the spread sheet r1:c1

What I want to do is create a link, embedded object or hyperlink (not sure
how to do this) from the word document to the excel document (as source)
I want to reference r1:c1 in the spreadsheet. and put it into the word
document.

Like I said I want these two files to be templates of sort, so wherever
the
two files are together it will know where to get the information.
I do not want to change the linking source information each time I copy.

Is there a way to link an item and reference it like this...

\\information.xls

and not like this...

c:\template\information.xls


Thanks.




--

Please post your responses to this newsgroup so that
we may all learn from your expertise.

-lumpjaw

[lumpjaw]

Thanks Marco,

When you say Word does not allow this thing I am trying to accomplish, is it
safe to say Excel does? I also have Excel documents that I want to treat in
the same fashion. Thanks for the reply.


-lumpjaw





7-"macropod" wrote in message
...
Hi lumpjaw,

Word doesn't permit the use of a path like that. However, since the files
will be kept in the same folder, inserting the following macro into the
Word
document will update the link paths to point to the current folder on
opening.

The code is extensively commentd, and reports its progress on the status
bar - useful if there are many links to update.

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

Cheers

--
macropod
[MVP - Microsoft Word]


"lumpjaw" wrote in message
...
Hello,

Here is a scenario. I have a folder which I am going to use various
forms
from we will call this the template folder.
I have an excel file called information.xls
I have a word file called work.doc
I will use the two files over and over again and create new new
directories
in which to put them.
I will change information in the spread sheet r1:c1

What I want to do is create a link, embedded object or hyperlink (not
sure
how to do this) from the word document to the excel document (as source)
I want to reference r1:c1 in the spreadsheet. and put it into the word
document.

Like I said I want these two files to be templates of sort, so wherever
the
two files are together it will know where to get the information.
I do not want to change the linking source information each time I copy.

Is there a way to link an item and reference it like this...

\\information.xls

and not like this...

c:\template\information.xls


Thanks.




--

Please post your responses to this newsgroup so that
we may all learn from your expertise.

-lumpjaw

[macropod]

Hi lumpjaw,

Unlike Word, Excel formulae referring to an external file in the same folder
can be copied (with the referenced file) to other folders and still work
without modification.

Cheers

--
macropod
[MVP - Microsoft Word]


"lumpjaw" wrote in message
...
Thanks Marco,

When you say Word does not allow this thing I am trying to accomplish, is
it
safe to say Excel does? I also have Excel documents that I want to treat
in
the same fashion. Thanks for the reply.


-lumpjaw





7-"macropod" wrote in message
...
Hi lumpjaw,

Word doesn't permit the use of a path like that. However, since the
files
will be kept in the same folder, inserting the following macro into the
Word
document will update the link paths to point to the current folder on
opening.

The code is extensively commentd, and reports its progress on the status
bar - useful if there are many links to update.

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

Cheers

--
macropod
[MVP - Microsoft Word]


"lumpjaw" wrote in message
...
Hello,

Here is a scenario. I have a folder which I am going to use various
forms
from we will call this the template folder.
I have an excel file called information.xls
I have a word file called work.doc
I will use the two files over and over again and create new new
directories
in which to put them.
I will change information in the spread sheet r1:c1

What I want to do is create a link, embedded object or hyperlink (not
sure
how to do this) from the word document to the excel document (as
source)
I want to reference r1:c1 in the spreadsheet. and put it into the word
document.

Like I said I want these two files to be templates of sort, so wherever
the
two files are together it will know where to get the information.
I do not want to change the linking source information each time I
copy.

Is there a way to link an item and reference it like this...

\\information.xls

and not like this...

c:\template\information.xls


Thanks.




--

Please post your responses to this newsgroup so that
we may all learn from your expertise.

-lumpjaw

[lumpjaw]

Thanks for the help.


-lumpjaw


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

Unlike Word, Excel formulae referring to an external file in the same
folder
can be copied (with the referenced file) to other folders and still work
without modification.

Cheers

--
macropod
[MVP - Microsoft Word]


"lumpjaw" wrote in message
...
Thanks Marco,

When you say Word does not allow this thing I am trying to accomplish, is
it
safe to say Excel does? I also have Excel documents that I want to treat
in
the same fashion. Thanks for the reply.


-lumpjaw





7-"macropod" wrote in message
...
Hi lumpjaw,

Word doesn't permit the use of a path like that. However, since the
files
will be kept in the same folder, inserting the following macro into the
Word
document will update the link paths to point to the current folder on
opening.

The code is extensively commentd, and reports its progress on the
status
bar - useful if there are many links to update.

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

Cheers

--
macropod
[MVP - Microsoft Word]


"lumpjaw" wrote in message
...
Hello,

Here is a scenario. I have a folder which I am going to use various
forms
from we will call this the template folder.
I have an excel file called information.xls
I have a word file called work.doc
I will use the two files over and over again and create new new
directories
in which to put them.
I will change information in the spread sheet r1:c1

What I want to do is create a link, embedded object or hyperlink (not
sure
how to do this) from the word document to the excel document (as
source)
I want to reference r1:c1 in the spreadsheet. and put it into the word
document.

Like I said I want these two files to be templates of sort, so
wherever
the
two files are together it will know where to get the information.
I do not want to change the linking source information each time I
copy.

Is there a way to link an item and reference it like this...

\\information.xls

and not like this...

c:\template\information.xls


Thanks.




--

Please post your responses to this newsgroup so that
we may all learn from your expertise.

-lumpjaw

[Bill Manville]

I don't think you want the link source to look like \\information.xls

I think you really want it to be referencing the directory containing
the document containing the link. \\ will not do that.

.\information.xls
would do what you want, if Word allows it.

As far as Excel is concerned, formula links to workbooks in the same
directory, or a sub-directory thereof, are held as relative links so
you would not need to do anything.

I bow to Macropod's knowledge of Word for how to handle such links in
Word.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

[lumpjaw]

You are ruight, that is how I would want to handle it. Thanks for the
pointer. While I have ya'lls ear... what would you recommend as a solid
manual for intermediate to advanced level Word 2003? Thanks in advance.


-Lumpjaw


"Bill Manville" wrote in message
...
I don't think you want the link source to look like \\information.xls

I think you really want it to be referencing the directory containing
the document containing the link. \\ will not do that.

.\information.xls
would do what you want, if Word allows it.

As far as Excel is concerned, formula links to workbooks in the same
directory, or a sub-directory thereof, are held as relative links so
you would not need to do anything.

I bow to Macropod's knowledge of Word for how to handle such links in
Word.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup