Word Relative Field Paths Discussion (2)

Topic

New Reply

Hi Macropod,

I apologize if this is not the proper way or place to ask my question – the previous thread about the above mentioned topic was to old to reply to.

I have read through the entire previous thread and could not solve my problem on my own.

I have developed a Word report template for my firm witch uses links with excel. Therefor the folder containing the word to excel links must be relative allowing to folder to be moved.

I developed the template and everything was working – the links were relative on their own without the Macro. It was not until I saw that the very last LINK field in the Word doc was broken that I came across the threads and discussions that Link fields in Word are fixed.

I have attached 2 files (The one has the latest version of your Macro enabled, the other does not) to this thread.

The only reason I tried your Macro is because the very last link field in my Word doc was fixed and therefor the link broke when the folder was moved. If I understand the previous discussions correctly non of the link fields should be relative?

Thus my questions / problem is as follows:

a) Why are the links to excel in the Word doc (the one without a Macro to update the paths) relative and why is it only the last link field that stays fixed? If I add any new Link fields they are also fixed.

b) After I could not get the last link field to be relative I tried your Macro (please see second attached file). But again all the paths except for the very last link field are updated with the correct path. And I get the following error when opening the Word doc: “Run time error 6083: Objects in this document contain links to files that cannot be found. The linked information will not be updated.”

Your help on this will really be appreciated.

Regards,

Reply | Quote

Replies

In Word, all link paths to external files, regardless of the link type, are absolute. For all except the LINK field (typically used to link to Excel workbooks), the other linkage fields can be made to perform relative linking by the addition of the FILENAME field, as described here: http://windowssecrets.com/forums/showthread//154379-Word-Fields-and-Relative-Paths-to-External-Files. Relative linking with LINK, fields, however, requires a macro.

I am unable to open either of your attachments: I get a ‘does not appear to be a valid archive’ message when trying to do so.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Again,

Thank you for the quick response,.

I apologize for wasting you time, I recompressed the folders and tested them on another computer. This time they will open.

I have attached the new files to this reply.

Thank you again, you will save me allot of time if you can help me with this.

Regards,

Reply | Quote

The macro works fine with your document for me. Do note that, since you have two source workbooks, both must be placed in the same folder as the Word document; otherwise the update process will fail and the macro will generate the message:
“Run time error 6083: Objects in this document contain links to files that cannot be found. The linked information will not be updated.”

Quite apart from that, I seriously question why you’re using so many links. IMHO you’d do far better to use DATABASE fields for much of the data. Properly coded, they can automatically omit all your ‘**** DELETE THIS LINE ****’ lines too. For an implementation, see: http://www.msofficeforums.com/mail-merge/21847-mail-merge-into-different-coloumns.html#post67110

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi there,

Than you again for the quick response.

I agree with you, a database approach will better and faster. I just first need to learn myself how to do it and then make the required amendments as suggested.

But for the moment I will still have to use the template as is.

I still receive the same error with your macro. I tested it on three other PC’s and it still did the same thing. It looks to me that whenever the word document links to a single cell in excel the macro works but as soon as it reaches a field where the word doc links with a table or chart it changes the field format which leads to the error.

Here is the code after the macro did its job with a chart link:

{ LINK Excel.SheetMacroEnabled.12 “C:\Users\lbadenhorst\Desktop\Toetse\Template V2.20\Nuwe Template V1.35 (Safe)- edited LB.xlsm!Grafieke![Nuwe Template V1.35 (Safe)- edited LB.xlsm]Grafieke Chart 5” “” a p }

It should look like this:

{ LINK Excel.SheetMacroEnabled.12 “C:\Users\lbadenhorst\Desktop\Toetse\Template V2.20\Nuwe Template V1.35 (Safe)- edited LB.xlsm” “Grafieke![Nuwe Template V1.35 (Safe)- edited LB.xlsm]Grafieke Chart 5” a p }

Are you sure all the fields updated at your side?

And like I said before, even without the macro as soon as I update the links when the word doc opens almost all the paths update. Do you know how this is possible?

Thank you for all the help thus far. Obviously I am doing something wrong at my side I just need to find out what that is.

Regard

Reply | Quote

As I already said, the macro worked fine for me with your document and worksheets when all are in the same folder. And yes, the fields updated. Obviously, if the LINK paths are to a folder (any folder) that already contains the workbooks, Word will update the links to those workbooks – without the need for a macro.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I downloaded your Template with the macro and got the same error as above (cannot find file).
I have a problem of my own. I get a runtime error (5) “Invalid procedure call or argument” in the GetPath function
“StrPath = Left(StrPath, Len(StrPath) – 1)”
I am just linking to pictures in the same folder as the word document. I am running Word 2007.
What am I missing here?
Thanks
Frank

Reply | Quote

Hi Frank,

Aside from not needing the macro when working with pictures (which use an INCLUDEPICTURE field), the macro error indicates that the picture concerned is not, in fact, in the same folder as the document.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thank you for replying so quick Paul.
It would be great if I didn’t need the macro. My images are in the same folder as the document.
If I insert “{FILENAME p}\..\Image.png”} for file location and then move the document with the image.png, without the macro, the document launches with a place holder for the image. To retrieve the image, I need to Edit Links and there update.
This is not a problem for a document with one link, but with a document with 10 links, it’s a nuisance.
Am I still doing something wrong?
Frank

Reply | Quote

All you should need to do is check Word’s options to:
• Update fields before printing
• Update linked data before printing
• Update automatic links at open
Indeed, you probably only need the last of these.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I found those settings.
The “• Update automatic links at open” was already set.
The other 2 were not, but now set them.
There is no change to the behaviour. After being moved to another folder and document launched, a frame for the image is shown. When an update is performed via Edit Links the image shows.
This behaviour is the same if I have just image.png entered after the INCLUDEPICTURE or “{FILENAME p}\..\Image.png”}.
There must be something else at play here.
Frank

Reply | Quote

What is your full INCLUDEPICTURE field code?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

It is either:
{ INCLUDEPICTURE “{FILENAME p}\..\Penguins.jpg”} d * MERGEFORMAT }
or:
{ INCLUDEPICTURE Penguins.jpg d * MERGEFORMAT }
with the same behaviour.
Frank

Reply | Quote

Your first field contains an excess ‘}’ brace before the d.
Your second field lacks the path, so Word will quite possibly resort to the last (e.g. original) path stored in the document metadata.
Both fields contain an unnecessary * MERGEFORMAT switch, which may compromise their behaviour.

It take it the field braces for {FILENAME p} are created via Ctrl-F9 and are not simply typed/pasted.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I have removed the * MERGEFORMAT from the line.
I removed the } after the p.
This } came from your sticky post on the forum.
Still behaves the same.
Frank

Reply | Quote

I removed the } after the p.
This } came from your sticky post on the forum.

That is only partly true. The field there is represented as:
{INCLUDEPICTURE “{FILENAME p}\..\Image.png”}
That is the entire field. Your field, though, has additional switches – which can only occur before the terminating }. Since you have that, the other does not belong there.

I also note you’re using the d switch, which the field code I posted does not. I suggest trying the field without that switch, too.

If you still have no joy, attach the document and its image file to a post (delete anything sensitive) and I’ll take a look at it. You do this via the paperclip symbol on the ‘Go Advanced’ tab at the bottom of this screen.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

You are right.
I removed everything except your original text.
Now it looks like:
{ INCLUDEPICTURE “{FILENAME p}\..\Penguins.jpg” }
I am tickled pink! It works.
The d * MERGEFORMAT comes from Word. When I insert a picture through the menus, these switches are there. I assumed they were required.
Sorry to have been so difficult.
Thank you so much.

Reply | Quote

Macropod,

I’ve read your threads, and I’m still utterly lost. Can you take me step by step from the beginning? I have no background in macros what so ever and am using Microsoft Word 2010.

I’ve been handed a project to make 11 flashdrive copies of a master document and it’s links. I’ve linked all the documents to the master document, but when another computer uses the flashdrive, if it isn’t in their F:// drive, the links don’t work. I’ve tried editing hyperlinks and hyperbase, and what not but no luck.

All I want is for the supporting documents in the “Linked Documents” folder to appear when a link is clicked on in the master document.

The drive looks like this:

F://

Folder: EasternWVCTC

Subfolder: Linked Documents
Live Word – Master File
PDF – Master File

All of the hyperlinks are within the Subfolder: Linked Documents…

Thank you so much for your help…

Reply | Quote

In light of the additional editing required by inserting this field for every picture, to make it work as relative addressing, is almost too tedious.
I think, that since the standard insertion of an image, by using the menu system for each link, is much simpler, if there was an automatic macro was inserted into the document that run at startup, would update each field, would be an easier solution.
Just a thought.
Frank

Reply | Quote

In light of the additional editing required by inserting this field for every picture, to make it work as relative addressing, is almost too tedious.

It would, of course, be possible to do this using Find/Replace. Simply:
• press Alt-F9 to expose the document’s field codes
• create the FILENAME field and its trailers (i.e. {FILENAME p}\..\)
• copy the FILENAME field and its trailers to the clipboard
• open the Find/Replace dialogue and input the part of the path to be replaced as the Find text, with ^c as the Replace expression, then click ‘Replace All’.

I think, that since the standard insertion of an image, by using the menu system for each link, is much simpler, if there was an automatic macro was inserted into the document that run at startup, would update each field, would be an easier solution.

Anything that entails macros raises macro-security and user-response issues, aside from which one might also argue that static images should be embedded in the document rather than being linked to it.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Yes, editing can be made easier, but, needing the relative addressing is not often. In between those times needed, I am sure I would forget how to do it, so I would need to search out the method used last time.
This is why I thought that a macro would fix this by entering it into my normal template, it would be there automatically when I create a new document, and it would just work.
I take your comment about the security issue.
I am so happy though, that I now have a method of applying relative addressing for links.
Thank you very much.
Frank

Reply | Quote

Hey everyone. First post here and google searching brought me to the forums.

I’m using Office 2007, and have a Word file that has Links to various Excel tables all in the same/single Excel file. About 60 of them actually. The excel file is also a macro enabled. xlsm file. Both the Word and Excel file are basically a template that my staff will pull down from our network to their local PC. They will then rename both files for their current assignment. And both should typically always be in the same working folder as well. I believe macropod’s macro is designed to edit the links to a relative path in that same folder?

But when I run the macro, I get a an error that takes to me these lines

Code:

 ' Replace the link to the external file if they differ.
   If OldPath  NewPath Then .LinkFormat.SourceFullName = _
    Replace(.LinkFormat.SourceFullName, OldPath, NewPath)

I’m not certain, but should the macro be prompting for the new file location?

I think for my purposes, all I really need is a macro that would prompt the user for the new/correct folder and file location and then update all of the linked tables.

Any help would be greatly appreciated.

Thanks
PT

Reply | Quote

Do the LINK fields contain the path details? For example, when the error occurs, what do you see if you choose Debug and hover over OldPath & NewPath?

And no, the macro isn’t supposed to give any prompts – it can work out for itself what the current folder is.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hey Macrpod.

Thanks for getting back to me and trying to help.

I’m not sure how to copy and past that info here, but Yes when I hover over each one, I see what the current value is but not sure if they are all correct or not

Example
.SourceFullName shows the correct path and filename
.OldPath shows the correct path, the file name, and the cells being linked ; ie C:pathtofilefilename.xlsm!Cover Charts!R28C29

and .NewPath only shows the new file path, but neither shows the file name or the cell info.

And if I stop the macro and rerun it a 2nd time without exiting word, I get an error the excel file is already open, and .OldPath then returns just the file path, and no file name, etc.

You probably know it, but its all on line 100 of the macro.

FYI….. Reason I was curious about if the macro prompted for a the new/correct file name as in my case the user will likely be renaming both the Word and Excel docs at the start of every project. Is the macro looking to replace the path for a file with the same file name?

And thanks again for the help !

Reply | Quote

The content of .OldPath (i.e. C:pathtofilefilename.xlsm!Cover Charts!R28C29) shows you have a mal-formed LINK field. The path includes neither the filename nor the link address. All you should see there is ‘C:pathtofile’.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi all,

when I execute macropod’s macro (Rev. 2014-09-24 at 00:27) to update {LINK } fields to an Excel 2007 file in a Microsoft Word 2007 document, I get the following error message:

Window title: Microsoft Office Excel
Message: A document with the name ‘DATA.XLSX’ is already open. You cannot open two documents with the same name, even if the documents are in different folders. To open the second document, either close the document that is already open, or rename one of the documents.

In detail:

I have two files:
– FOLDER1DOCUMENT.DOCM and
– FOLDER1DATA.XSLX.

FOLDER1DOCUMENT.DOCM contains several link fields to specific cells in FOLDER1DATA.XSLX and the macro to update the folder names.

The macro works fine when I rename FOLDER1 or move it to a different location!
The error message only appears when I make a copy of FOLDER1, e.g. FOLDER2 and then open FOLDER2DOCUMENT.DOCM.

The following seems to happen:
When FOLDER2DOCUMENT.DOCM is opened Word calls Excel which opens/locks FOLDER1DATA.XSLX in the background (because the absolute paths have not yet changed), although I cannot see an Excel process in the task manager.
When macropod’s macro is then executed, the above-mentioned Excel error message appears. The debugger marks the following line:

Code:

.SourceFullName = Replace(.SourceFullName, OldPath, NewPath)

So I guess when .SourceFullName is changed by the macro, Word calls Excel again which tries to open the file FOLDER2/DATA.XSLX now (maybe to check if the file exists or to update the field content) in the background (an Excel process is now present in the task manager) but fails, because FOLDER1DATA.XSLX is already open or locked by Excel and has the same file name.

If I rename FOLDER1 to FOLDER3 before opening FOLDER2DOCUMENT.DOCM, Word cannot find FOLDER1DATA.XLSX and the macro works fine.

Does anybody have an idea how to prevent that opening FOLDER2DOCUMENT.DOCM and/or changing the variable .SourceFullName lets Excel open/lock the DATA.XSLX file(s)?

Thank you!
Jens

Reply | Quote

Try changing the document’s link updating to manual (via the Edit Links dialog) and/or unchecking Word’s ‘update automatic links at open’ option.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thank you for your quick response.

Unfortunately, changing all links to manual and unchecking the ‘update automatic links at open’ option doesn’t solve the issue.

Jens

Reply | Quote

Unfortunately, changing all links to manual and unchecking the ‘update automatic links at open’ option doesn’t solve the issue.

The only thing I can think of that would account for that is a LINK field in a page header/footer. Do you have any of those?

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I don’t know why you’re messing around with what you describe as ‘editing hyperbase’, by which I assume you mean the hyperlink base. The thread (http://windowssecrets.com/forums/showthread//154379-Word-Fields-and-Relative-Paths-to-External-Files) already has detailed instructions on what you need to do – everything except for the macro discussion late in the thread applies – simply read ‘HYPERLINK’ wherever the instructions refer to ‘INCLUDEPICTURE’.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote