Linking excel data to Word document so links survive file namechange

[BRC]

If I am in the wrong forum to post this question please let me know.
My problem is related to linking data in an excel spreadsheet to a
Word docment in the same folder. Here is situation: each job has a
folder [job1 job2 job3 etc.], each job folder has at least 2 files a
spreadsheet and 1 report document. so in folder job1 there will be
job1.doc and job1.xls. I am trying to develope templates for these 2
files that will maintain the link. So when we start job4 we can create
a new folder (job4) and start new files (job4.xls and job4.doc) that
are linked. I have searched a lot and found some references to
relative paths vs absolute paths but i couldn't really understand
them. If anyone has had any experience or can direct me to where i
might get some more info it would be greatly appreciated Thanks in
advance.

 

[macropod]

Hi BRC,

For what you want, a macro will be needed. That's because links from Word to Excel workbooks use LINK fields and you can't code
those in a way that allows for relative paths.

For a macro to implement relative paths in Word, check out the solution I've posted at:
http://lounge.windowssecrets.com/index.php?showtopic=670027

 

[BRC]

Hi BRC,

For what you want, a macro will be needed. That's because links from Wordto Excel workbooks use LINK fields and you can't code
those in a way that allows for relative paths.

For a macro to implement relative paths in Word, check out the solution I've posted at:http://lounge.windowssecrets.com/index.php?showtopic=670027

--
Cheers
macropod
[Microsoft MVP - Word]

If I am in the wrong forum to post this question please let me know.
My problem is related to linking data in an excel spreadsheet to a
Word docment in the same folder. Here is situation: each job has a
folder [job1 job2 job3 etc.], each job folder has at least 2 files a
spreadsheet and 1 report document. so in folder job1 there will be
job1.doc and job1.xls. I am trying to develope templates for these 2
files that will maintain the link. So when we start job4 we can create
a new folder (job4) and start new files (job4.xls and job4.doc) that
are linked.  I have searched a lot and found some references to
relative paths vs absolute paths but i couldn't really understand
them. If anyone has had any experience or can direct me to where i
might get some more info it would be greatly appreciated Thanks in
advance.- Hide quoted text -

- Show quoted text -

Thank you very much for your quick response and the useful
information. I was able to download the your macrot. I am having a
little trouble getting to run properly. As usual I left out some info
in my original post that may be important. I am using Word 2007, and
Excel 2007, windows 7 os. When I run the maco (when file opens) I get
an error (line 92 col 1) in the line that says
OldPath = Replace(.LinkFormat.SourcePath, "\", "\\"). When I hold the
mouse over “LinkFormat.SourcePath,” the balloon says”
linkFormat.sourcepath=<object varible or with block varibale not set>”
Do you happen to
Will I have to somewhere enter the name of the new excel file before
the actual replacements are done? Say through an input box? Thanks
again for the help

 

[macropod]

Hi BRC,

That error may be because your original field code only had the filename, not both the filename and path.

--
Cheers
macropod
[Microsoft MVP - Word]

Hi BRC,

For what you want, a macro will be needed. That's because links from Word to Excel workbooks use LINK fields and you can't code
those in a way that allows for relative paths.

For a macro to implement relative paths in Word, check out the solution I've posted
at:http://lounge.windowssecrets.com/index.php?showtopic=670027

--
Cheers
macropod
[Microsoft MVP - Word]

If I am in the wrong forum to post this question please let me know.
My problem is related to linking data in an excel spreadsheet to a
Word docment in the same folder. Here is situation: each job has a
folder [job1 job2 job3 etc.], each job folder has at least 2 files a
spreadsheet and 1 report document. so in folder job1 there will be
job1.doc and job1.xls. I am trying to develope templates for these 2
files that will maintain the link. So when we start job4 we can create
a new folder (job4) and start new files (job4.xls and job4.doc) that
are linked. I have searched a lot and found some references to
relative paths vs absolute paths but i couldn't really understand
them. If anyone has had any experience or can direct me to where i
might get some more info it would be greatly appreciated Thanks in
advance.- Hide quoted text -

- Show quoted text -

Thank you very much for your quick response and the useful
information. I was able to download the your macrot. I am having a
little trouble getting to run properly. As usual I left out some info
in my original post that may be important. I am using Word 2007, and
Excel 2007, windows 7 os. When I run the maco (when file opens) I get
an error (line 92 col 1) in the line that says
OldPath = Replace(.LinkFormat.SourcePath, "\", "\\"). When I hold the
mouse over “LinkFormat.SourcePath,” the balloon says”
linkFormat.sourcepath=<object varible or with block varibale not set>”
Do you happen to
Will I have to somewhere enter the name of the new excel file before
the actual replacements are done? Say through an input box? Thanks
again for the help

 

[BRC]

Hi BRC,

That error may be because your original field code only had the filename,not both the filename and path.

--
Cheers
macropod
[Microsoft MVP - Word]

Hi BRC,

For what you want, a macro will be needed. That's because links from Word to Excel workbooks use LINK fields and you can't code
those in a way that allows for relative paths.

For a macro to implement relative paths in Word, check out the solutionI've posted
at:http://lounge.windowssecrets.com/index.php?showtopic=670027

If I am in the wrong forum to post this question please let me know.
My problem is related to linking data in an excel spreadsheet to a
Word docment in the same folder. Here is situation: each job has a
folder [job1 job2 job3 etc.], each job folder has at least 2 files a
spreadsheet and 1 report document. so in folder job1 there will be
job1.doc and job1.xls. I am trying to develope templates for these 2
files that will maintain the link. So when we start job4 we can create
a new folder (job4) and start new files (job4.xls and job4.doc) that
are linked. I have searched a lot and found some references to
relative paths vs absolute paths but i couldn't really understand
them. If anyone has had any experience or can direct me to where i
might get some more info it would be greatly appreciated Thanks in
advance.- Hide quoted text -

- Show quoted text -

Thank you very much for your quick response and the useful
information.  I was able to download the your macrot.   I am having a
little trouble getting to run properly.  As usual I left out some info
in my original post that may be important.  I am using Word 2007, and
Excel 2007, windows 7 os.  When I run the maco (when file opens) I get
an error (line 92 col 1)  in the line that says
OldPath = Replace(.LinkFormat.SourcePath, "\", "\\"). When I hold the
mouse over “LinkFormat.SourcePath,” the balloon says”
linkFormat.sourcepath=<object varible or with block varibale not set>”
Do you happen to
Will I have to somewhere enter the name of the new excel file before
the actual replacements are done? Say through an input box?  Thanks
again for the help- Hide quoted text -

- Show quoted text -

 

[BRC]

Hi BRC,

That error may be because your original field code only had the filename, not both the filename and path.

"BRC" <[email protected]> wrote in messagenews:27c00946-edf2-4644-b368-0129dd77189b@u25g2000prh.googlegroups.com...

Hi BRC,
For what you want, a macro will be needed. That's because links from Word to Excel workbooks use LINK fields and you can't code
those in a way that allows for relative paths.
For a macro to implement relative paths in Word, check out the solution I've posted
at:http://lounge.windowssecrets.com/index.php?showtopic=670027
--
Cheers
macropod
[Microsoft MVP - Word]
If I am in the wrong forum to post this question please let me know..
My problem is related to linking data in an excel spreadsheet to a
Word docment in the same folder. Here is situation: each job has a
folder [job1 job2 job3 etc.], each job folder has at least 2 files a
spreadsheet and 1 report document. so in folder job1 there will be
job1.doc and job1.xls. I am trying to develope templates for these 2
files that will maintain the link. So when we start job4 we can create
a new folder (job4) and start new files (job4.xls and job4.doc) that
are linked. I have searched a lot and found some references to
relative paths vs absolute paths but i couldn't really understand
them. If anyone has had any experience or can direct me to where i
might get some more info it would be greatly appreciated Thanks in
advance.- Hide quoted text -
- Show quoted text -

Thank you very much for your quick response and the useful
information.  I was able to download the your macrot.   I am havinga
little trouble getting to run properly.  As usual I left out some info
in my original post that may be important.  I am using Word 2007, and
Excel 2007, windows 7 os.  When I run the maco (when file opens) I get
an error (line 92 col 1)  in the line that says
OldPath = Replace(.LinkFormat.SourcePath, "\", "\\"). When I hold the
mouse over “LinkFormat.SourcePath,” the balloon says”
linkFormat.sourcepath=<object varible or with block varibale not set>”
Do you happen to
Will I have to somewhere enter the name of the new excel file before
the actual replacements are done? Say through an input box?  Thanks
again for the help- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Macropod,
Thanks again for the reference. I looked though all of the posts
discussing your macro on that site. I still have a couple questions
about the macro. While working with your macro I found that it
changed the path to the linked file but not the file name. In the
scheme I am trying to create I will need to change the file name
( *.xlsm) as well as the path. The second ? I have is …can the field
code changes be saved [to the *.docm file] so that even if the
autoOpen macro doesn’t run the document field codes will still point
to the correct file. I hope these questions make sense. I am not a
programmer by trade so I don’t read and understand code like the
morning paper. Again, much thanks for your efforts. BRC

 

[macropod]

Hi BRC,

The problem with changing both the filename and path is one of determining what the filename should be. The code could be modified
to allow the filename to be determined by some parameter (eg the folder name) or from user input, but then it would lose its
portability. You can't use wildcards for filenames in field codes either.

As coded, the macro maintains the links regardless of changes in the path *provided the source filename remains the same* - that's
it's "raison d'etre". If one made the mods you're suggesting, you'd be obliged to rename the Excel file whenever the set gets moved
to another folder.

As for changing the docm file's field coding without the macro running, the answer is no. Changing the filepath is what the macro
does. If you don't run the macro, there's no way to automate changing the path.



--
Cheers
macropod
[Microsoft MVP - Word]

Hi BRC,

That error may be because your original field code only had the filename, not both the filename and path.

"BRC" <[email protected]> wrote in messagenews:27c00946-edf2-4644-b368-0129dd77189b@u25g2000prh.googlegroups.com...

Hi BRC,
For what you want, a macro will be needed. That's because links from Word to Excel workbooks use LINK fields and you can't
code
those in a way that allows for relative paths.
For a macro to implement relative paths in Word, check out the solution I've posted
at:http://lounge.windowssecrets.com/index.php?showtopic=670027
--
Cheers
macropod
[Microsoft MVP - Word]
If I am in the wrong forum to post this question please let me know.
My problem is related to linking data in an excel spreadsheet to a
Word docment in the same folder. Here is situation: each job has a
folder [job1 job2 job3 etc.], each job folder has at least 2 files a
spreadsheet and 1 report document. so in folder job1 there will be
job1.doc and job1.xls. I am trying to develope templates for these 2
files that will maintain the link. So when we start job4 we can create
a new folder (job4) and start new files (job4.xls and job4.doc) that
are linked. I have searched a lot and found some references to
relative paths vs absolute paths but i couldn't really understand
them. If anyone has had any experience or can direct me to where i
might get some more info it would be greatly appreciated Thanks in
advance.- Hide quoted text -
- Show quoted text -

Thank you very much for your quick response and the useful
information. I was able to download the your macrot. I am having a
little trouble getting to run properly. As usual I left out some info
in my original post that may be important. I am using Word 2007, and
Excel 2007, windows 7 os. When I run the maco (when file opens) I get
an error (line 92 col 1) in the line that says
OldPath = Replace(.LinkFormat.SourcePath, "\", "\\"). When I hold the
mouse over “LinkFormat.SourcePath,” the balloon says”
linkFormat.sourcepath=<object varible or with block varibale not set>”
Do you happen to
Will I have to somewhere enter the name of the new excel file before
the actual replacements are done? Say through an input box? Thanks
again for the help- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Macropod,
Thanks again for the reference. I looked though all of the posts
discussing your macro on that site. I still have a couple questions
about the macro. While working with your macro I found that it
changed the path to the linked file but not the file name. In the
scheme I am trying to create I will need to change the file name
( *.xlsm) as well as the path. The second ? I have is …can the field
code changes be saved [to the *.docm file] so that even if the
autoOpen macro doesn’t run the document field codes will still point
to the correct file. I hope these questions make sense. I am not a
programmer by trade so I don’t read and understand code like the
morning paper. Again, much thanks for your efforts. BRC

 

[BRC]

Hi BRC,
That error may be because your original field code only had the filename, not both the filename and path.
--
Cheers
macropod
[Microsoft MVP - Word]
Hi BRC,
For what you want, a macro will be needed. That's because links from Word to Excel workbooks use LINK fields and you can't code
those in a way that allows for relative paths.
For a macro to implement relative paths in Word, check out the solution I've posted
at:http://lounge.windowssecrets.com/index.php?showtopic=670027
--
Cheers
macropod
[Microsoft MVP - Word]
If I am in the wrong forum to post this question please let me know.
My problem is related to linking data in an excel spreadsheet to a
Word docment in the same folder. Here is situation: each job has a
folder [job1 job2 job3 etc.], each job folder has at least 2 files a
spreadsheet and 1 report document. so in folder job1 there will be
job1.doc and job1.xls. I am trying to develope templates for these 2
files that will maintain the link. So when we start job4 we can create
a new folder (job4) and start new files (job4.xls and job4.doc) that
are linked. I have searched a lot and found some references to
relative paths vs absolute paths but i couldn't really understand
them. If anyone has had any experience or can direct me to where i
might get some more info it would be greatly appreciated Thanks in
advance.- Hide quoted text -
- Show quoted text -
Thank you very much for your quick response and the useful
information.  I was able to download the your macrot.   I am having a
little trouble getting to run properly.  As usual I left out some info
in my original post that may be important.  I am using Word 2007, and
Excel 2007, windows 7 os.  When I run the maco (when file opens) I get
an error (line 92 col 1)  in the line that says
OldPath = Replace(.LinkFormat.SourcePath, "\", "\\"). When I hold the
mouse over “LinkFormat.SourcePath,” the balloon says”
linkFormat.sourcepath=<object varible or with block varibale not set>”
Do you happen to
Will I have to somewhere enter the name of the new excel file before
the actual replacements are done? Say through an input box?  Thanks
again for the help- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

Macropod,
Thanks again for the reference.  I looked though all of the posts
discussing your macro on that site.   I still have a couple questions
about the macro.  While working with your macro I found that it
changed the path to the linked file but not the file name.   In the
scheme I am trying to create I will need to change the file name
( *.xlsm) as well as the path.   The second ? I have is …can the field
code changes be saved [to the  *.docm file] so that even  if  the
autoOpen  macro doesn’t run the document field codes will still point
to the correct file.  I hope these questions make sense.  I am not a
programmer by trade so I don’t read and understand code like  the
morning paper.  Again, much thanks for your efforts. BRC- Hide quoted text -

- Show quoted text -

Macropod
I think what you mentioned in the 1st para of your last response is
what I am trying to do. I’ll elaborate; when we start a new job we
create a new xlsm file with the facility address as the name
(job1addr.xlsm) based on a template job.xltm, and a new report
document ( job1addr.docm) based on the template jobrept.dotm. Both
job1addr files will reside in folder “job1addr.” What I am trying to
figure out is how to retain the linked relationship from the template
state to the file state. Each new job gets its own files. Thanks
again for your help.

 

[macropod]

Hi BRC,

I think you'll be able to achive what you're after by modifying the macro as follows:

1. Create two new variables:
Dim OldFile As String
Dim NewFile As String
before:
Dim i As Integer

2. Insert:
' Set the new filename
NewFile = Split(ActiveDocument.Name, ".")(0)
after:
NewPath = Replace$(Parent & Child, "\", "\\")

2. Insert:
' Replace the source filename with the same name as this document
OldFile = Split(.LinkFormat.SourceName, ".")(0)
..Code.Text = Replace(.Code.Text, OldFile, NewFile)
after:
..Code.Text = Replace(.Code.Text, OldPath, NewPath)

As indicated in the comment line, this should change the name of the file the link points to, to a file with the same name as the
document (eg if you're running the code from job4.doc and the link is to an Excel file, the link will be updated to point to
job4.xls).

--
Cheers
macropod
[Microsoft MVP - Word]

Hi BRC,
That error may be because your original field code only had the filename, not both the filename and path.
--
Cheers
macropod
[Microsoft MVP - Word]
Hi BRC,
For what you want, a macro will be needed. That's because links from Word to Excel workbooks use LINK fields and you can't
code
those in a way that allows for relative paths.
For a macro to implement relative paths in Word, check out the solution I've posted
at:http://lounge.windowssecrets.com/index.php?showtopic=670027
--
Cheers
macropod
[Microsoft MVP - Word]
If I am in the wrong forum to post this question please let me know.
My problem is related to linking data in an excel spreadsheet to a
Word docment in the same folder. Here is situation: each job has a
folder [job1 job2 job3 etc.], each job folder has at least 2 files a
spreadsheet and 1 report document. so in folder job1 there will be
job1.doc and job1.xls. I am trying to develope templates for these 2
files that will maintain the link. So when we start job4 we can create
a new folder (job4) and start new files (job4.xls and job4.doc) that
are linked. I have searched a lot and found some references to
relative paths vs absolute paths but i couldn't really understand
them. If anyone has had any experience or can direct me to where i
might get some more info it would be greatly appreciated Thanks in
advance.- Hide quoted text -
- Show quoted text -
Thank you very much for your quick response and the useful
information. I was able to download the your macrot. I am having a
little trouble getting to run properly. As usual I left out some info
in my original post that may be important. I am using Word 2007, and
Excel 2007, windows 7 os. When I run the maco (when file opens) I get
an error (line 92 col 1) in the line that says
OldPath = Replace(.LinkFormat.SourcePath, "\", "\\"). When I hold the
mouse over “LinkFormat.SourcePath,” the balloon says”
linkFormat.sourcepath=<object varible or with block varibale not set>”
Do you happen to
Will I have to somewhere enter the name of the new excel file before
the actual replacements are done? Say through an input box? Thanks
again for the help- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

Macropod,
Thanks again for the reference. I looked though all of the posts
discussing your macro on that site. I still have a couple questions
about the macro. While working with your macro I found that it
changed the path to the linked file but not the file name. In the
scheme I am trying to create I will need to change the file name
( *.xlsm) as well as the path. The second ? I have is …can the field
code changes be saved [to the *.docm file] so that even if the
autoOpen macro doesn’t run the document field codes will still point
to the correct file. I hope these questions make sense. I am not a
programmer by trade so I don’t read and understand code like the
morning paper. Again, much thanks for your efforts. BRC- Hide quoted text -

- Show quoted text -

Macropod
I think what you mentioned in the 1st para of your last response is
what I am trying to do. I’ll elaborate; when we start a new job we
create a new xlsm file with the facility address as the name
(job1addr.xlsm) based on a template job.xltm, and a new report
document ( job1addr.docm) based on the template jobrept.dotm. Both
job1addr files will reside in folder “job1addr.” What I am trying to
figure out is how to retain the linked relationship from the template
state to the file state. Each new job gets its own files. Thanks
again for your help.

 

[BRC]

Hi BRC,

I think you'll be able to achive what you're after by modifying the macroas follows:

1. Create two new variables:
Dim OldFile As String
Dim NewFile As String
before:
Dim i As Integer

2. Insert:
' Set the new filename
NewFile = Split(ActiveDocument.Name, ".")(0)
after:
NewPath = Replace$(Parent & Child, "\", "\\")

 2. Insert:
' Replace the source filename with the same name as this document
OldFile = Split(.LinkFormat.SourceName, ".")(0)
.Code.Text = Replace(.Code.Text, OldFile, NewFile)
after:
.Code.Text = Replace(.Code.Text, OldPath, NewPath)

As indicated in the comment line, this should change the name of the filethe link points to, to a file with the same name as the
document (eg if you're running the code from job4.doc and the link is to an Excel file, the link will be updated to point to
job4.xls).

--
Cheers
macropod
[Microsoft MVP - Word]

Hi BRC,
That error may be because your original field code only had the filename, not both the filename and path.
--
Cheers
macropod
[Microsoft MVP - Word]
Hi BRC,
For what you want, a macro will be needed. That's because links from Word to Excel workbooks use LINK fields and you can't
code
those in a way that allows for relative paths.
For a macro to implement relative paths in Word, check out the solution I've posted
at:http://lounge.windowssecrets.com/index.php?showtopic=670027
--
Cheers
macropod
[Microsoft MVP - Word]
If I am in the wrong forum to post this question please let me know.
My problem is related to linking data in an excel spreadsheet to a
Word docment in the same folder. Here is situation: each job has a
folder [job1 job2 job3 etc.], each job folder has at least 2 files a
spreadsheet and 1 report document. so in folder job1 there willbe
job1.doc and job1.xls. I am trying to develope templates for these 2
files that will maintain the link. So when we start job4 we cancreate
a new folder (job4) and start new files (job4.xls and job4.doc)that
are linked. I have searched a lot and found some references to
relative paths vs absolute paths but i couldn't really understand
them. If anyone has had any experience or can direct me to where i
might get some more info it would be greatly appreciated Thanksin
advance.- Hide quoted text -
- Show quoted text -
Thank you very much for your quick response and the useful
information. I was able to download the your macrot. I am having a
little trouble getting to run properly. As usual I left out some info
in my original post that may be important. I am using Word 2007, and
Excel 2007, windows 7 os. When I run the maco (when file opens) I get
an error (line 92 col 1) in the line that says
OldPath = Replace(.LinkFormat.SourcePath, "\", "\\"). When I holdthe
mouse over “LinkFormat.SourcePath,” the balloon says”
linkFormat.sourcepath=<object varible or with block varibale not set>”
Do you happen to
Will I have to somewhere enter the name of the new excel file before
the actual replacements are done? Say through an input box? Thanks
again for the help- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -

Macropod,
Thanks again for the reference. I looked though all of the posts
discussing your macro on that site. I still have a couple questions
about the macro. While working with your macro I found that it
changed the path to the linked file but not the file name. In the
scheme I am trying to create I will need to change the file name
( *.xlsm) as well as the path. The second ? I have is …can the field
code changes be saved [to the *.docm file] so that even if the
autoOpen macro doesn’t run the document field codes will still point
to the correct file. I hope these questions make sense. I am not a
programmer by trade so I don’t read and understand code like the
morning paper. Again, much thanks for your efforts. BRC- Hide quoted text -

- Show quoted text -

Macropod
I think what you mentioned in the 1st para of your last response is
what I am trying to do.  I’ll elaborate; when we start a new job we
create a new xlsm file with the facility address as the name
(job1addr.xlsm) based on a template job.xltm,  and a new report
document ( job1addr.docm) based on  the template jobrept.dotm. Both
job1addr files will reside in folder “job1addr.”  What I am trying to
figure out is how to retain the linked relationship from the template
state to the file state.  Each new job gets its own files.  Thanks
again for your help.- Hide quoted text -

- Show quoted text -

Macropod,
Hope your still out there. I made some progress over a very
frustrating weekend. But I have reached a point where the link now
looks exactly correct. After the auto open runs the link points to
the correct file in the correct directory. For the benefit of anyone
following this, I did have a problem with the code for changing the
file name from old file to new file. I had to move that code to occur
before the path change. I set message boxes to display the variables
as the code executed and if I stopped the .Code.Text = lines from
executing all of the variables had correct values. So when I changed
the order of the .Code.Text = lines it writes the new link correctly.
My problem now is the link initially still points at the old file.
Even though the link has the new file info when I double click on the
object it opens in the old file. When I right click and select update
link I get an error and message box says “Word is unable to create a
link to the object you specified. Please insert the object directly in
your file without creating a link.” The new file is an exact copy of
the original that generated the link. Do you know if word is using
some hidden parameter within the file the linked source file? Again
many thanks for all the help. BRC

 

[macropod]

Hi BRC,

The only reason I can see for the new link not working correctly for you is that the Excel file doesn't exist (ie hasn't been
created yet or its name isn't quite the same as that of the document).

To improve the link updating, try changing the lines between:
OldPath = Replace(.LinkFormat.SourcePath, "\", "\\")
and:
..Update
to:
' Get the old filename
OldFile = Split(.LinkFormat.SourceName, ".")(0)
' Replace the link to the external file
..Code.Text = Replace(.Code.Text, OldPath & "\\" & OldFile, NewPath & "\\" & NewFile)

That approach works fine for me, updating the link in one operation and making it work correctly in the updated document.


--
Cheers
macropod
[Microsoft MVP - Word]



Macropod,
Hope your still out there. I made some progress over a very
frustrating weekend. But I have reached a point where the link now
looks exactly correct. After the auto open runs the link points to
the correct file in the correct directory. For the benefit of anyone
following this, I did have a problem with the code for changing the
file name from old file to new file. I had to move that code to occur
before the path change. I set message boxes to display the variables
as the code executed and if I stopped the .Code.Text = lines from
executing all of the variables had correct values. So when I changed
the order of the .Code.Text = lines it writes the new link correctly.
My problem now is the link initially still points at the old file.
Even though the link has the new file info when I double click on the
object it opens in the old file. When I right click and select update
link I get an error and message box says “Word is unable to create a
link to the object you specified. Please insert the object directly in
your file without creating a link.” The new file is an exact copy of
the original that generated the link. Do you know if word is using
some hidden parameter within the file the linked source file? Again
many thanks for all the help. BRC

 

[BRC]

HiBRC,

The only reason I can see for the new link not working correctly for you is that the Excel file doesn't exist (ie hasn't been
created yet or its name isn't quite the same as that of the document).

To improve the link updating, try changing the lines between:
OldPath = Replace(.LinkFormat.SourcePath, "\", "\\")
and:
.Update
to:
' Get the old filename
OldFile = Split(.LinkFormat.SourceName, ".")(0)
' Replace the link to the external file
.Code.Text = Replace(.Code.Text, OldPath & "\\" & OldFile, NewPath & "\\" & NewFile)

That approach works fine for me, updating the link in one operation and making it work correctly in the updated document.

--
Cheers
macropod
[Microsoft MVP - Word]



Macropod,
Hope your still out there.  I made some progress over a very
frustrating weekend. But I have reached a point where the link now
looks exactly correct.  After the auto open runs the link points to
the correct file in the correct directory.  For the benefit of anyone
following this, I did have a problem with the code for changing the
file name from old file to new file.  I had to move that code to occur
before the path change.  I set message boxes to display the variables
as the code executed and if I stopped the .Code.Text = lines from
executing all of the variables had correct values. So when I changed
the order of the .Code.Text = lines it writes the new link correctly.
My problem now is the link initially still points at the old file.
Even though the link has the new file info when I double click on the
object it opens in the old file.  When I right click and select update
link  I get an error and message box says “Word is unable to create a
link to the object you specified. Please insert the object directly in
your file without creating a link.” The new file is an exact copy of
the original that generated the link. Do you know if word is using
some hidden parameter within the file the linked source file? Again
many thanks for all the help.BRC

Macropod, This is working perfectly. That last bit of code you
sent did the trick. For the benefit of anyone following, I did
download your macro from the link you posted in your first response.
The file download as "AutoFldUpdt.zip" size 1779 bytes. In your last
post you told me to place new code between OldPath = Replace
(.LinkFormat.SourcePath, "\", "\\") and: .Update. In the original
zip file the “.update” command was not there. I don’t know how
critical it is but thought I would mention this. My next part of the
project will be to modify the updatefields() macro to update the links
to a specified locations so when I install this on other machines I
can re establish the links between the doc template and the excel
template. Again, thanks for all the help. You are a Wizard.