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.

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]


"BRC" <sgos4v-googrp@yahoo.com> wrote in message news:1e3fc5e5-4215-437c-bd12-9c0f3c810d8b@x5g2000prf.googlegroups.com...
> 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. 

On Dec 17, 12:23=A0am, "macropod" <macro...@invalid.invalid> wrote:
> 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=3D670027
>
> --
> Cheers
> macropod
> [Microsoft MVP - Word]
>
>
>
> "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:1e3fc5e5-4215-437c-b=
d12-9c0f3c810d8b@x5g2000prf.googlegroups.com...
> > 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. =A0I 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 =3D Replace(.LinkFormat.SourcePath, "\", "\\"). When I hold the
mouse over =93LinkFormat.SourcePath,=94 the balloon says=94
linkFormat.sourcepath=3D<object varible or with block varibale not set>=94
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

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]


"BRC" <sgos4v-googrp@yahoo.com> wrote in message news:27c00946-edf2-4644-b368-0129dd77189b@u25g2000prh.googlegroups.com...
On Dec 17, 12:23 am, "macropod" <macro...@invalid.invalid> wrote:
> 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]
>
>
>
> "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:1e3fc5e5-4215-437c-bd12-9c0f3c810d8b@x5g2000prf.googlegroups.com...
> > 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 

On Dec 17, 1:46=A0pm, "macropod" <macro...@invalid.invalid> wrote:
> 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]
>
> "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:27c00946-edf2-4644-b=
368-0129dd77189b@u25g2000prh.googlegroups.com...
>
> On Dec 17, 12:23 am, "macropod" <macro...@invalid.invalid> wrote:
>
>
>
>
>
> > Hi BRC,
>
> > For what you want, a macro will be needed. That's because links from Wo=
rd 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=3D670027
>
> > --
> > Cheers
> > macropod
> > [Microsoft MVP - Word]
>
> > "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:1e3fc5e5-4215-437c=
-bd12-9c0f3c810d8b@x5g2000prf.googlegroups.com...
> > > 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 creat=
e
> > > 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. =A0I was able to download the your macrot. =A0 I am having a
> little trouble getting to run properly. =A0As usual I left out some info
> in my original post that may be important. =A0I am using Word 2007, and
> Excel 2007, windows 7 os. =A0When I run the maco (when file opens) I get
> an error (line 92 col 1) =A0in the line that says
> OldPath =3D Replace(.LinkFormat.SourcePath, "\", "\\"). When I hold the
> mouse over =93LinkFormat.SourcePath,=94 the balloon says=94
> linkFormat.sourcepath=3D<object varible or with block varibale not set>=
=94
> 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? =A0Thanks
> again for the help- Hide quoted text -
>
> - Show quoted text -

On Dec 17, 10:07=A0pm, BRC <brc1051-goog...@yahoo.com> wrote:
> On Dec 17, 1:46=A0pm, "macropod" <macro...@invalid.invalid> wrote:
>
>
>
> > Hi BRC,
>
> > That error may be because your original field code only had the filenam=
e, not both the filename and path.
>
> > --
> > Cheers
> > macropod
> > [Microsoft MVP - Word]
>
> > "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:27c00946-edf2-4644=
-b368-0129dd77189b@u25g2000prh.googlegroups.com...
>
> > On Dec 17, 12:23 am, "macropod" <macro...@invalid.invalid> wrote:
>
> > > 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 soluti=
on I've posted
> > > at:http://lounge.windowssecrets.com/index.php?showtopic=3D670027
>
> > > --
> > > Cheers
> > > macropod
> > > [Microsoft MVP - Word]
>
> > > "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:1e3fc5e5-4215-43=
7c-bd12-9c0f3c810d8b@x5g2000prf.googlegroups.com...
> > > > 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 cre=
ate
> > > > a new folder (job4) and start new files (job4.xls and job4.doc) tha=
t
> > > > 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. =A0I was able to download the your macrot. =A0 I am having=
 a
> > little trouble getting to run properly. =A0As usual I left out some inf=
o
> > in my original post that may be important. =A0I am using Word 2007, and
> > Excel 2007, windows 7 os. =A0When I run the maco (when file opens) I ge=
t
> > an error (line 92 col 1) =A0in the line that says
> > OldPath =3D Replace(.LinkFormat.SourcePath, "\", "\\"). When I hold the
> > mouse over =93LinkFormat.SourcePath,=94 the balloon says=94
> > linkFormat.sourcepath=3D<object varible or with block varibale not set>=
=94
> > 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? =A0Thanks
> > 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 =85can the field
code changes be saved [to the  *.docm file] so that even  if  the
autoOpen  macro doesn=92t 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=92t read and understand code like  the
morning paper.  Again, much thanks for your efforts. BRC

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]


"BRC" <brc1051-googrps@yahoo.com> wrote in message news:53058e0e-95e5-4ff3-a7cd-b28d1d8c3442@b36g2000prf.googlegroups.com...
On Dec 17, 10:07 pm, BRC <brc1051-goog...@yahoo.com> wrote:
> On Dec 17, 1:46 pm, "macropod" <macro...@invalid.invalid> wrote:
>
>
>
> > 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]
>
> > "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:27c00946-edf2-4644-b368-0129dd77189b@u25g2000prh.googlegroups.com...
>
> > On Dec 17, 12:23 am, "macropod" <macro...@invalid.invalid> wrote:
>
> > > 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]
>
> > > "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:1e3fc5e5-4215-437c-bd12-9c0f3c810d8b@x5g2000prf.googlegroups.com...
> > > > 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 

On Dec 17, 10:12=A0pm, BRC <brc1051-goog...@yahoo.com> wrote:
> On Dec 17, 10:07=A0pm, BRC <brc1051-goog...@yahoo.com> wrote:
>
>
>
>
>
> > On Dec 17, 1:46=A0pm, "macropod" <macro...@invalid.invalid> wrote:
>
> > > Hi BRC,
>
> > > That error may be because your original field code only had the filen=
ame, not both the filename and path.
>
> > > --
> > > Cheers
> > > macropod
> > > [Microsoft MVP - Word]
>
> > > "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:27c00946-edf2-46=
44-b368-0129dd77189b@u25g2000prh.googlegroups.com...
>
> > > On Dec 17, 12:23 am, "macropod" <macro...@invalid.invalid> wrote:
>
> > > > Hi BRC,
>
> > > > For what you want, a macro will be needed. That's because links fro=
m 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 solu=
tion I've posted
> > > > at:http://lounge.windowssecrets.com/index.php?showtopic=3D670027
>
> > > > --
> > > > Cheers
> > > > macropod
> > > > [Microsoft MVP - Word]
>
> > > > "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:1e3fc5e5-4215-=
437c-bd12-9c0f3c810d8b@x5g2000prf.googlegroups.com...
> > > > > If I am in the wrong forum to post this question please let me kn=
ow.
> > > > > 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 file=
s a
> > > > > spreadsheet and 1 report document. so in folder job1 there will b=
e
> > > > > job1.doc and job1.xls. I am trying to develope templates for thes=
e 2
> > > > > files that will maintain the link. So when we start job4 we can c=
reate
> > > > > a new folder (job4) and start new files (job4.xls and job4.doc) t=
hat
> > > > > 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 i=
n
> > > > > advance.- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > Thank you very much for your quick response and the useful
> > > information. =A0I was able to download the your macrot. =A0 I am havi=
ng a
> > > little trouble getting to run properly. =A0As usual I left out some i=
nfo
> > > in my original post that may be important. =A0I am using Word 2007, a=
nd
> > > Excel 2007, windows 7 os. =A0When I run the maco (when file opens) I =
get
> > > an error (line 92 col 1) =A0in the line that says
> > > OldPath =3D Replace(.LinkFormat.SourcePath, "\", "\\"). When I hold t=
he
> > > mouse over =93LinkFormat.SourcePath,=94 the balloon says=94
> > > linkFormat.sourcepath=3D<object varible or with block varibale not se=
t>=94
> > > 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? =A0Thanks
> > > again for the help- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -
>
> Macropod,
> Thanks again for the reference. =A0I looked though all of the posts
> discussing your macro on that site. =A0 I still have a couple questions
> about the macro. =A0While working with your macro I found that it
> changed the path to the linked file but not the file name. =A0 In the
> scheme I am trying to create I will need to change the file name
> ( *.xlsm) as well as the path. =A0 The second ? I have is =85can the fiel=
d
> code changes be saved [to the =A0*.docm file] so that even =A0if =A0the
> autoOpen =A0macro doesn=92t run the document field codes will still point
> to the correct file. =A0I hope these questions make sense. =A0I am not a
> programmer by trade so I don=92t read and understand code like =A0the
> morning paper. =A0Again, much thanks for your efforts. BRC- Hide quoted t=
ext -
>
> - 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=92ll 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 =93job1addr.=94  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.

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]


"BRC" <brc1051-googrps@yahoo.com> wrote in message news:1f9d0e7b-ff45-4c1e-8d00-065f6ef4db43@u18g2000pro.googlegroups.com...
On Dec 17, 10:12 pm, BRC <brc1051-goog...@yahoo.com> wrote:
> On Dec 17, 10:07 pm, BRC <brc1051-goog...@yahoo.com> wrote:
>
>
>
>
>
> > On Dec 17, 1:46 pm, "macropod" <macro...@invalid.invalid> wrote:
>
> > > 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]
>
> > > "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:27c00946-edf2-4644-b368-0129dd77189b@u25g2000prh.googlegroups.com...
>
> > > On Dec 17, 12:23 am, "macropod" <macro...@invalid.invalid> wrote:
>
> > > > 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]
>
> > > > "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:1e3fc5e5-4215-437c-bd12-9c0f3c810d8b@x5g2000prf.googlegroups.com...
> > > > > 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. 

On Dec 18, 8:05=A0pm, "macropod" <macro...@invalid.invalid> wrote:
> 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 =3D Split(ActiveDocument.Name, ".")(0)
> after:
> NewPath =3D Replace$(Parent & Child, "\", "\\")
>
> =A02. Insert:
> ' Replace the source filename with the same name as this document
> OldFile =3D Split(.LinkFormat.SourceName, ".")(0)
> .Code.Text =3D Replace(.Code.Text, OldFile, NewFile)
> after:
> .Code.Text =3D 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]
>
> "BRC" <brc1051-goog...@yahoo.com> wrote in messagenews:1f9d0e7b-ff45-4c1e=
-8d00-065f6ef4db43@u18g2000pro.googlegroups.com...
>
> On Dec 17, 10:12 pm, BRC <brc1051-goog...@yahoo.com> wrote:
>
>
>
>
>
> > On Dec 17, 10:07 pm, BRC <brc1051-goog...@yahoo.com> wrote:
>
> > > On Dec 17, 1:46 pm, "macropod" <macro...@invalid.invalid> wrote:
>
> > > > Hi BRC,
>
> > > > That error may be because your original field code only had the fil=
ename, not both the filename and path.
>
> > > > --
> > > > Cheers
> > > > macropod
> > > > [Microsoft MVP - Word]
>
> > > > "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:27c00946-edf2-=
4644-b368-0129dd77189b@u25g2000prh.googlegroups.com...
>
> > > > On Dec 17, 12:23 am, "macropod" <macro...@invalid.invalid> wrote:
>
> > > > > Hi BRC,
>
> > > > > For what you want, a macro will be needed. That's because links f=
rom 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 so=
lution I've posted
> > > > > at:http://lounge.windowssecrets.com/index.php?showtopic=3D670027
>
> > > > > --
> > > > > Cheers
> > > > > macropod
> > > > > [Microsoft MVP - Word]
>
> > > > > "BRC" <sgos4v-goo...@yahoo.com> wrote in messagenews:1e3fc5e5-421=
5-437c-bd12-9c0f3c810d8b@x5g2000prf.googlegroups.com...
> > > > > > 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 t=
o a
> > > > > > Word docment in the same folder. Here is situation: each job ha=
s a
> > > > > > folder [job1 job2 job3 etc.], each job folder has at least 2 fi=
les 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 th=
ese 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 understa=
nd
> > > > > > them. If anyone has had any experience or can direct me to wher=
e 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 in=
fo
> > > > in my original post that may be important. I am using Word 2007, an=
d
> > > > Excel 2007, windows 7 os. When I run the maco (when file opens) I g=
et
> > > > an error (line 92 col 1) in the line that says
> > > > OldPath =3D Replace(.LinkFormat.SourcePath, "\", "\\"). When I hold=
 the
> > > > mouse over =93LinkFormat.SourcePath,=94 the balloon says=94
> > > > linkFormat.sourcepath=3D<object varible or with block varibale not =
set>=94
> > > > Do you happen to
> > > > Will I have to somewhere enter the name of the new excel file befor=
e
> > > > 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 =85can the field
> > code changes be saved [to the *.docm file] so that even if the
> > autoOpen macro doesn=92t 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=92t read and understand code like the
> > morning paper. Again, much thanks for your efforts. BRC- Hide quoted te=
xt -
>
> > - Show quoted text -
>
> Macropod
> I think what you mentioned in the 1st para of your last response is
> what I am trying to do. =A0I=92ll 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, =A0and a new report
> document ( job1addr.docm) based on =A0the template jobrept.dotm. Both
> job1addr files will reside in folder =93job1addr.=94 =A0What I am trying =
to
> figure out is how to retain the linked relationship from the template
> state to the file state. =A0Each new job gets its own files. =A0Thanks
> 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 =3D lines from
executing all of the variables had correct values. So when I changed
the order of the .Code.Text =3D 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 =93Word is unable to create a
link to the object you specified. Please insert the object directly in
your file without creating a link.=94 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

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]


"BRC" <brc1051-googrps@yahoo.com> wrote in message news:04ef4557-2c77-46e3-8cd5-f057abbce5e6@b36g2000prf.googlegroups.com...
On Dec 18, 8:05 pm, "macropod" <macro...@invalid.invalid> wrote:

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 

On Dec 21, 12:36=A0pm, "macropod" <macro...@invalid.invalid> wrote:
> 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 =3D Replace(.LinkFormat.SourcePath, "\", "\\")
> and:
> .Update
> to:
> ' Get the old filename
> OldFile =3D Split(.LinkFormat.SourceName, ".")(0)
> ' Replace the link to the external file
> .Code.Text =3D Replace(.Code.Text, OldPath & "\\" & OldFile, NewPath & "\=
\" & NewFile)
>
> That approach works fine for me, updating the link in one operation and m=
aking it work correctly in the updated document.
>
> --
> Cheers
> macropod
> [Microsoft MVP - Word]
>
> "BRC" <brc1051-goog...@yahoo.com> wrote in messagenews:04ef4557-2c77-46e3=
-8cd5-f057abbce5e6@b36g2000prf.googlegroups.com...
>
> On Dec 18, 8:05 pm, "macropod" <macro...@invalid.invalid> wrote:
>
> Macropod,
> Hope your still out there. =A0I made some progress over a very
> frustrating weekend. But I have reached a point where the link now
> looks exactly correct. =A0After the auto open runs the link points to
> the correct file in the correct directory. =A0For 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. =A0I had to move that code to occur
> before the path change. =A0I set message boxes to display the variables
> as the code executed and if I stopped the .Code.Text =3D lines from
> executing all of the variables had correct values. So when I changed
> the order of the .Code.Text =3D 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. =A0When I right click and select update
> link =A0I get an error and message box says =93Word is unable to create a
> link to the object you specified. Please insert the object directly in
> your file without creating a link.=94 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 =3D Replace
(.LinkFormat.SourcePath, "\", "\\")  and:  .Update.  In the original
zip file the =93.update=94 command was not there. I don=92t 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.