|
There are isolated problems with current patches, but they are well-known and documented on this site. |
| SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
Home » Forums » AskWoody support » Productivity software by function » MS Word and word processing help » Word Relative Field Paths Discussion
Allen Edwall.
WSHansVHi macropod,
That is a very interesting approach. It works fine for me with the INCLUDETEXT and HYPERLINK fields.
For INCLUDEPICTURE fields, however, it only works until the document is saved. At that point, the FILENAME field disappears, to be replaced by the relative path of the picture. I’m using Word 2002 SP3.
macropodWSHansV
WSsilverbackHello, Macropod.
I’m trying to implement your VB solution for relative links. I’ve imported the file but it’s not working. I’ve proved that the AutoOpen routine is not being entered (by putting a MsgBox at the start; this never gets executed)
Have you any ideas about why this should be? What have I done/not done?
Thanks
Silverback
macropodWSsilverbackMany thanks to Paul and Hans 
I changed the declaration of AutoOpen as Hans suggested and lowered the macro security level to medium (so Word prompts me) as Paul suggested and it’s all working!
This is going to save me literally hours of work and make the output more usable for the end users (because they will be able to update links without having to globally edit the links – obviously very error prone – which is all I was (cap)able to do for them this year.)
Thanks again
Silverback
WSHansV
WSgoawaymacropodHi Jen,
Are you referring to the macro? Or to the field coding? The macro was last updated in September this year, but I’m not aware of anything in the code that prevents it from working with Word 2007 files. Do note, though, that macros don’t work in files saved in the docx format – you must use either the old doc format or the new docm format if your file has macros.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSgoawaymacropodWSgoawaymacropodHi jen,[indent]
10-261488 FieldLinkUpdater
“Source filenames are expected to have a 3-letter extension (eg filename.ext). If they do not, the macro will not work correctly.”
[/indent]Ah, that quote is from a different thread – see post 261488. Whilst the subject is related, it isn’t quite the same. The Field Link Updater in post 261488 would indeed need some changes to work with files having 4-character extensions. Neither the field coding nor the macro attached to the originating post in this thread (post 670027) would need such a change.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSbrokenlinkHi Paul,
I recently downloaded the script from post#8 for fixing the broken link issue. Before going into the narrative, let me first thank you for this tool. It saves quite a bit of heartburn. Apparently your script has worked for me, but I had to tweak it a bit to make it work.
Now to my situation.
I had a bit of a corner case for the document that I was editing.
I am working with a MS Word 2003 compatible XML file (using a MS Word 2010 editor) which had links to cells from a .xlsx. I am actually supposed to share this file across, so that other people can also work with it. I had worked with linked powerpoint presentation a while back which were able to maintian relative links to other files, but I was not aware that for MS Word the links are absolute, and will break when someone else opens up the document.
Anyway, I imported the script in my document as expected, and the first thing that I faced was a syntax error on this line
line 85: If .Type = wdFieldHyperlink Or .Type = wdFieldImport _
I am not a windows programmer, but I tried to debug it by turning off the arguments to the condition. seems that a value of “wdFieldHyperlink” was not supported. I modified the line to look like
line 85: If .Type = wdFieldImport _
After this, I executed the script, but it went into an infinite loop.. I had to kill the processes using task manager to end them.
In the next iteration, I started to step through the code, and found that the loop for oFld was stuck around the very first LINK that it found. On closely debugging it, I found that the new path (which pointed to a mounted drive) was expanded after it was replaced
For e.g.
Old path = C:usersmedocument.xlsx
Expected
New path = Z:usersmedocument.xlsx
Actual
New path = \fileservershared_driveusersmedocument.xlsx where\fileservershared_drive = Z:
Because of the path expansion, the for loop became infinite on the very first link.
At present I have to ask people to not put the linked files on a network drive, but that is a workaround.
I am not familiar with programming in windows but have to use MS Office for documentation that can be used across the company. I cannot give you a proposal on the solution, but if you can let me know what I need to do to allow the script to work across drives, i would be glad to implement it.
best regards
Himanshu
WSburtinhHi, Paul.
First, thanks for posting the macro. I am trying to use it on a Word 2007 (.docm) file, and it’s not working quite as expected–I just want to make sure I have not missed something.
The Word 2007-source file and the referenced Excel file reside in the same subdirectory (as will all referenced files).
Here’s an example of what I see in the original (.docx without macro) and after (.docm with macro):
(.docx)
{LINK Excel.Sheet.8 “E:pdwDataWorkAVCINGPDARSAVCING_PDARS_Formats.xlsx!NamingConvention!R1C1:R11C4” “” a p }
(.docm)
{LINK Excel.Sheet.8 “C:Documents and SettingsbehDesktoptests:R63C6” “” a p }
The change omitting/changing the Excel object’s name/reference pointer has me stumped.
So, the macro is working–just not quite as expected, leaving the objects still inaccessible afterward. Any help you can be will be most appreciated.
Best regards,
Buddy
macropodHi Buddy,
Welcome to the Lounge.
I’me not sure what you mean by “leaving the objects still inaccessible afterward”. As written, the macro changes the link path to the folder the Word document is in. Naturally, therefore, your Excel workbook would need to be in the same folder for the link to work – if it isn’t you’d need to change the code to modify the new path to suit (eg by adding a child folder’s name).
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSburtinhSorry, Paul.
I didn’t realize that it changes the path, the file name, and the pointers, as it did for every instance in the .docm I used it to modify (re: the example I posted). I thought it was supposed to change only the path, leaving the file name and pointers intact. Because the .docx linked reference was in the format…
{LINK Excel.Sheet.8 “E:pdwDataWorkAVCINGPDARSAVCING_PDARS_Formats.xlsx!NamingConvention!R1C1:R11C4” “” a p }
…in the .docm (macro-modified version) I was expecting…
{LINK Excel.Sheet.8 “C:Documents and SettingsbehAVCING_PDARS_Formats.xlsx!NamingConvention!R1C1:R11C4” “” a p }
…(which would make sense because, as I stated in my original post, the Word file and Excel file DO reside in the same folder)…
…BUT, in the .docm (macro-modified version) what I got was…
{LINK Excel.Sheet.8 “C:Documents and SettingsbehDesktoptests:R63C6” “” a p }
…which has no file name at all, and the only pointer that is left is not even remotely related to the original…
Best-
Buddy
macropodHi Buddy,
What you’re reporting is really strange. There is nothing in the code to change the filename or pointers – only the path should be changed (to reflect the current path). I can’t see any way the code could do what you’re reporting.
Can you post a ‘sanitized’ copy of your file for me to look at?
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSgiffordpinchotHello,
I found this thread by searching on includetext and fieldname and I’m having an implementation problem. Thanks for the great solution but I could use a tad more help to get it working. I’m on Windows XP, MS Word 2007 and I’m driving a mail merge from VB in Access. If I hard code the file paths in the includetext fields in my mail merge everything works fine. I’m trying to get a relative path so I can move my mail merge templates to the same directory as my end Access db.
Here is my coding in Word:
{ IF { MERGEFIELD BillUnitTypeID } = 3 “{ INCLUDETEXT “{FILENAME p}..Invoices_Letters_Static.docx ” }” “” }
I then have two more If statements after this but all this embeddeding of field has me stumped. I wonder if it has something to do with the difference between and /. I don’t really understand that either. For instance, does the Fieldname return a path with or /?
thanks for your help!
macropodHi,
Welcome to the Lounge.
The field brace pairs (ie ‘{ }’) for the INCLUDETEXT field and the FILENAME field embedded within it are created via Ctrl-F9 – you can’t simply type them or copy & paste them from the original post.
Also, with your mailmerge, you can probably use a structure like:
{INCLUDETEXT “{FILENAME p}..{IF{MERGEFIELD BillUnitTypeID}= 3 “Invoices_Letters_Static.docx” {IF{MERGEFIELD BillUnitTypeID}= 2 “Invoices_Letters_Variable.docx” “Invoices_Letters_Default.docx”}}” }
This gives you the possibility of 3 output letters, and you can increase/decrease the number by changing the number of embedded IF tests. Again, note that the field brace pairs (ie ‘{ }’) for the above example are created via Ctrl-F9 – you can’t simply type them or copy & paste them from this post.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSgiffordpinchotThanks, Paul. It’s like you knew exactly what I was doing! Instead of embedding my if statements I just put them consecutively (e.g. {IF …} {IF …}{IF ….}). I got it working just fine without the embedded IFs and using {MERGEFIELD DBPath} instead of FILENAME. Now I’m trying to clean it up and use your more elegant solution but I have hiccup:
It returns this:
{ INCLUDETEXT “C:Documents and SettingsMy NameDesktopTemplatesInvoices_Letters_Static.docx” }
instead of the actual file. I can’t see any difference in my syntax between this version and the consecutive if statements I used before.
Any other thoughts?
Also, is there a way to copy and past my code from Word into this message without it rendering? The first time I had to manually type it but that defeats the puprose of having someone see if I made an error in my actual code.
Last one – sometimes I get a field that seems to get corrupted (???) and appears to be be able to update. It gets blue shading instead of the regular gray and has a red exclamation mark when I try to update. It’s happening on my very standard date field.
Thanks again, Paul.
WSHansVOur moderator jscher2000 has written a macro that will convert Word fields to text that you can paste into a post here, or convert such text back into a working field. See Field Codes-to-Text Converter (and vice versa)
WSgiffordpinchotmacropodHi,
You say that when you run your mailmerge,[indent]
It returns this:
{ INCLUDETEXT “C:Documents and SettingsMy NameDesktopTemplatesInvoices_Letters_Static.docx” }
[/indent] and you’re using:[indent]
{ INCLUDETEXT “{ MERGEFIELD DBPath }Templates{IF {MERGEFIELD BillUnitTypeID }= 1 “Invoices_Letters_Acres.docx” {IF {MERGEFIELD BillUnitTypeID }= 2 “Invoices_Letters_Tons.docx” “Invoices_Letters_Static.docx”}}” }
[/indent]Two observations:
1. The field code display after running the mailmerge suggests you’ve got Word set to show field codes instead of their values. Try pressing Alt-F9 to toggle the display.
2. The inclusion of { MERGEFIELD DBPath } indicates you’re not using relative paths at all – which is what the head post in this thread is all about and what you indicated you were trying to achieve.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSgiffordpinchotHUGE screenshot shrunk by HansV – please don’t post pictures larger than 640×480 pixels
Can anyone tell me what the blue highlighting and tab mean? This is the first time I’ve seen this come up. It seems to want me to update the field but even when I update it the blue stays. Also, this field never propagates when I do the merge.
I think this is my last stumbling block!
Thanks…
WSjscher2000macropodWSgiffordpinchot
WSgeckoHello. I’m trying to use your relative paths method for using LINK in Word 2003 SP3 to connect to a PowerPoint slide. I’ve tried your macro as you set out in your post and, while it runs, it seems to replace the brace field delimiter characters with some other unrecognisable character, rendering the field useless. I end up with the following:
{ LINK PowerPoint.Slide.8 ” FILENAME p ..AAT Course Notes Jan 09.ppt” “448” d }
You can see the delimiters have been removed from around the FILENAME p term and replaced with something else (it just shows up as a rectangle in the Word document).
Perhaps it’s some kind of character set issue?
Any thoughts would be much appreciated.
macropodHi gecko,
Welcome to the Lounge.
Judging by the field code included in your post, it seems you’re trying to combine the use of the FILENAME field and the macro to manage a LINK field. The FILENAME field can’t be used in a LINK field – you can only use the macro.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSgeckoMany thanks, Paul. Silly me – I was thinking the macro was in addition to your FILENAME procedure, not as a replacement for it. Does this mean that if I’ve got my PPT file in the same folder as the Word doc, that I should remove the pathname entirely from the link – as per the following?:
{ LINK PowerPoint.Slide.8 “AAT Course Notes Jan 09.ppt” “448” d }
The original slide link after I pasted it into Word, was as follows:
{ LINK PowerPoint.Slide.8 “C:DataAATAAT Course Notes Jan 09.ppt” “448” d }
In both cases, however, I get the same result with the macro – the moment I change the underlying slide, it disappears from the Word document and is replaced by the following message:
Error! Not a valid link.
I made sure to attach your macro file to the Word document and saved it, even before pasting any slide links into it. Also, just to point out I only need the PPT and DOC files to be in the same folder, but this folder is likely to be shunted around drives with different drive-letters.
Cheers,
Gecko
macropodHi Gecko,
Are you sure the original link path & filename was valid (eg did you update the field and view the embedded slide after editing the field)?
Are both files in a common folder? Before & after moving (especially after)?
I’ve just retested the process (using Word 2000) and it works fine for me when I move both files to a new folder.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSjayawanthWas always on the look out for relative path spec in Links – Thanks for the solution. A couple of issues.
1. If I use the inserted Field FILENAME method, after a save, exit and reload, INCLUDEPICTURE fields should still have the inserted FILENAME. Right? In .docx files they are being recomputed to full paths. Please clarify what is expected, Macro is not installed.
2. Links have the Alt Text which is usually the path, it would be useful to define this in a similar relative way. Currently the only way seems to be to use a macro similar to AutoFieldUpdate to change every link during a file open to have the current Alt Text.
Thanks – Jayawanth
macropodHi jayawanth,
Sorry about the delay in replying – I’ve been unable to review this matter until now.
When I insert an INCLUDEPICTURE field coded as: {INCLUDEPICTURE “{FILENAME p}..MyPic.jpg”} into a docx document, the field coding is preserved after saving, closing & re-opening the file.
Do note, though, that copying the field code from one document to another will result in the FILENAME field being resolved and the path in the INCLUDETEXT field converted to plain text (ie “{FILENAME p}..MyPic.jpg” becomes “C:UsersThisUserDocumentsMyPic.jpg”). Are you sure that isn’t what happened?
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSjayawanthThanks for the reply and clarifying why it was delayed.
I experimented and found out that I have to save the file with the Field Codes displayed. Then it preserves the field codes always on subsequent saves and loads. Some boolean is not set if save is executed with the fields evaluated.
– Thanks, Jayawanth
WSTheGoodOmenI have two issues. I am attempting to use INCLUDETEXT to reference in headers and footers for a large set of specifications. I have been successful in getting my word documents to link up to the headers and footers that I have set up in separate files. Here are my issues:
1. The files I am referencing (called HeaderData.doc and FooterData.doc) contain formatting to make certain elements bold. When I reference these into my target documents, the formatting is lost. While not a disaster, I’d like to be able to retain the formatting of the source file. It doesn’t seem to matter whether the MERGEFORMAT option is selected or not. Can the formatting be retained somehow?
2. My goal is to be able to change information in HeaderData.doc or FooterData.doc (the source files) and the print all the target files directly to the printer, without opening each file and printing. The routine at the beginning of this thread does not seem to update the INCLUDETEXT (and may even create havoc). Is there a way to make sure the files are updated when they are printed? At the very least, is there a way to make sure that those fields are updated upon opening the file?
Thanks for your assistance.
WSjscher2000
WSrussdwI moved a Word 2007 file and a bunch of imported graphics to a flash drive and promptly discovered that links to imported graphics break when you move the files from one location to another, even when the relative location of the document and the graphic files are the same. Wow.
This post has been very useful in coming up with a good workflow, and I’m planning to use the macro. My own personal preference, though, is to keep all my graphic files in a “child” folder at the same level as my Word documents — so all the graphic files are one folder level down. Could you please recommend a modification I could make to the macro that would make this work? I’ve looked at the code and even tried one idea, but the bottom line is I’m not proficient enough with Visual Basic to get it to fly.
Thanks for your help.
-Russ
macropodI moved a Word 2007 file and a bunch of imported graphics to a flash drive and promptly discovered that links to imported graphics break when you move the files from one location to another, even when the relative location of the document and the graphic files are the same. Wow.
This post has been very useful in coming up with a good workflow, and I’m planning to use the macro. My own personal preference, though, is to keep all my graphic files in a “child” folder at the same level as my Word documents — so all the graphic files are one folder level down. Could you please recommend a modification I could make to the macro that would make this work? I’ve looked at the code and even tried one idea, but the bottom line is I’m not proficient enough with Visual Basic to get it to fly.
Thanks for your help.
-Russ
Hi Russ,
For what you want to do, make the following changes to the ‘UpdateFields’ sub:
1. Add ‘Dim Child As String’ after ‘Dim NewPath As String’
2. Add ‘Child = “Your chile folder’s name”‘ after ‘Dim Child As String’
3. Change ‘NewPath = Replace$(ActiveDocument.Path, “”, “\”)’ to ‘NewPath = Replace$(ActiveDocument.Path & Child & “”, “”, “\”)’
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSrussdwMacropod, thanks a lot! In change #3, I did have to swap the order of the backslash and Child as follows:
[indent]NewPath = Replace$(ActiveDocument.Path & “” & Child, “”, “\”)[/indent]
The macro is working great in a small test document, but when I use the macro in a larger file, I get the following error message when I open the file:
[indent]Run-time error ‘5891’:
That property is not available on that object.[/indent]
The debugger highlights this line:
[indent]OldPath = Replace(.LinkFormat.SourcePath, “”, “\”)[/indent]
The Word document contains a number of additional fields: REF and PAGEREF for cross references, STYLEREF and SEQ for figure numbers, and DATE and PAGE as well. To find out if one of them might be causing a conflict, I removed each field type one-by-one and reopened the file; only REF appears to be the culprit, even though PAGEREF has identical arguments most of the time, like this example sentence:
[indent]For more information, see “{ REF _Ref231120866 h }” on page { PAGEREF _Ref231120866 h }.[/indent]
Could you possibly modify the code to eliminate this error message, although I am stumped why it would be occurring for REF alone and not PAGEREF too.
Thanks!
-Russ
macropodMacropod, thanks a lot! In change #3, I did have to swap the order of the backslash and Child as follows:
[indent]NewPath = Replace$(ActiveDocument.Path & “” & Child, “”, “\”)[/indent]
The macro is working great in a small test document, but when I use the macro in a larger file, I get the following error message when I open the file:[indent]Run-time error ‘5891’:
That property is not available on that object.[/indent]
The debugger highlights this line:[indent]OldPath = Replace(.LinkFormat.SourcePath, “”, “\”)[/indent]
The Word document contains a number of additional fields: REF and PAGEREF for cross references, STYLEREF and SEQ for figure numbers, and DATE and PAGE as well. To find out if one of them might be causing a conflict, I removed each field type one-by-one and reopened the file; only REF appears to be the culprit, even though PAGEREF has identical arguments most of the time, like this example sentence:[indent]For more information, see “{ REF _Ref231120866 h }” on page { PAGEREF _Ref231120866 h }.[/indent]
Could you possibly modify the code to eliminate this error message, although I am stumped why it would be occurring for REF alone and not PAGEREF too.Thanks!
-Russ
Hi Russ,
Thanks for the note about the NewPath line.
Evidently there’s something buggy in the way MS has implemented the Linfkormat Property – REF fields don’t have one and should therefore be excluded by the LinkFormat property test. For a workaround, replace:
If Not .LinkFormat Is Nothing Then
with:
‘ A ‘.Linfkormat’ property test should work, but is not reliable.
If .Type = wdFieldHyperlink Or .Type = wdFieldImport Or .Type = wdFieldInclude _
Or .Type = wdFieldIncludePicture Or .Type = wdFieldIncludeText Or .Type = wdFieldLink Or .Type = wdFieldRefDoc Then
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSrussdw
WServonealHello,
I am not very familiar with coding, so I apologize for the questions that clearly point this out. I have a Word file that has multiple links to an Excel file in the same folder. The files are out on a network folder that is shared by a few people. However, I also need to be able to send them to people outside of our company and have the links still work. I have spent a lot of time researching and testing how to make a relative link to a file that does not convert to an absolute file path. I am trying to use the method you described above. I have inserted a { FILENAME p} field in the document. I have a few questions for which I would really appreciate a response.
First, my original links look like this:
{ LINK Excel.Sheet.8 “\\Stlfile\finance\John Doe\Notes\Company One.xls” “Balance Sheet!BalanceSheet” a p * MERGEFORMAT }
This link is showing the path on the network, the file name, the tab name of Balance Sheet and the range name of Balance Sheet. I do need to maintain the formatting.
After applying your method, should it look like this:
{ LINK Excel.Sheet.8 “{ FILENAME p}/../Company One.xls” “Balance Sheet!BalanceSheet” a p * MERGEFORMAT }
Or like this:
{ LINK Excel.Sheet.8 “{ FILENAME p}\..\Company One.xls” “Balance Sheet!BalanceSheet” a p * MERGEFORMAT }
I’m not sure what the difference is between /../ and \..\ for this type of situation. Can you explain for me?
Also, I noticed that the coding that is in my Word file has a space between characters where your example does not have a space. Does this make any difference? An example would be the space between { and FILENAME.
Out of curiousity, what do the a and p represent?
Thanks for your asistance.
WServonealAs a follow-up to my post above, whenever I change the links as described above, they work even after I save the file. I have the option turned off to update the links upon closing the file. When I subsequently open the file, I get the message that the file has links to data in other files and do I want to update the data. If I choose NO, the file opens and the links are as they were when I saved them. However, if I choose YES, the links are converted back to their original absoute file paths. The same thing happens if I chhose NO but then later update the links manually. I am using Word 2007 and Excel 2007 but both files are saved as 2003 file extensions if this makes a difference.
macropodHi Ervin,
Welcome to the Lounge.
As mentioned in my original post, the fieldcode modifications do not work with LINK fields. That’s because LINK fields have the nasty habit of converting any embedded fields to their plain text equivalents immediately the fields are updated. Accordingly, you need to employ the macro-based solution.
The a and p are switches – the a tells the field to automatically update to reflect changes in the data source. The p tells Word to represent the link as an image. As an aside, you can delete the ‘* MERGEFORMAT’ – it does nothing useful in this context.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WServonealThank Macropod.
I have imported the macro file, saved my file as a .docm, and enabled macros for this file. I have saved everything, closed the file and reopened it. I can hit F11 and see the file still out there in a module. I can view macros and see the AutoOpen macro. However, nothing seems to be changing in my document. I reviewed the code (not that I am an expert) for any other clues. My file is not password protected. Any ideas where I should look next?
The original version of this file was created in Word 2007 but saved as a Word 2003 document. As part of this process, I have converted it to a .docm. The links I noted above are referring to an Excel file that was also created in 2007 but saved as a 2003 compatible file. I assume that this has nothing to do with the macro not updating the links.
I keep thinking of the saying “There are no stupid questions, but the world is full of inquisitive idiots”
macropodWServonealMacropod,
I do really appreciate your timely responses and your patience with the macro challenged.
I moved the files from my network folder to my desktop. When I opened the Word document, I could tell it was trying to do something with Excel as my Excel session was blinking rapidly. However, once the Word file opened, I noticed the Links had only changed slightly by dropping the “Excel.Sheet.8” from the address. All the rest of it looked the same. I copied the before and after below. As a test, I opened the Excel file on my desktop and changed a number. I clicked on “Update Link” in the Word file and got an error that the linked file cound not be located. Any suggestions?
Before
{ LINK Excel.Sheet.8 “\\Stlfile\Finance\Ervin\Notes to the Financial Statements\Company Inc.xls” “Balance Sheet!BalanceSheet” a p * MERGEFORMAT }
After
{ LINK “\\Stlfile\Finance\Ervin O’Neal\Notes to the Financial Statements\Company Inc.xls” “Balance Sheet!BalanceSheet” a p * MERGEFORMAT }
Also, if I close the Word file and reopen it, the Link formulas keep building upon themselves as seen below
{ LINK Excel.Sheet.8 “\\Stlfile\Finance\Ervin O’Neal\Notes to the Financial Statements\Company Inc.xls” Income!Income FinanceErvin O’NealNotes to the Financial StatementsCompany Inc.xls “\\Stlfile\Finance\Ervin O’Neal\Notes to the Financial Statements\Company Inc.xls!Income!Income” “” a p * MERGEFORMAT }
Thanks,
macropodHi Ervin,
Running the macro as posted, I am unable to replicate the behaviour you’re reporting using either Word 2000 or Word 2007.
In my testing, ‘LINK Excel.Sheet.8’ remains unchanged, as does the filename and the field switches – all that changes is the filepath (as intended) and I get no duplication of the filepath on subsequent re-openings of the document.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WServonealMacropod,
Thanks for all of your assistance and fast responses. I created a new test .docm file that had a link to a new .xlsx file. After importing the macro file, I saved everything. I then moved the files from my Desktop to our network server. When I opened the Word document, the links were updated as your macro is designed. Now, I just have to figure out what is going on in my old documents that are interfering. But at least I know now that it is not your macro or a system setting on my PC.
Thanks again and have a great holiday!
WServonealMacropod,
Hopefully my last question. I having all kinds of trouble getting the file paths to update once I cut and past the Word and Excel file into a different folder. One of the things I am noticing can hopefully be seen by you in the attached files. When I open the Word file after it was previously saved with the file paths viewable, Word asks me if I want to update the data with the linked files. When I say yes, I can witness the link updating to the new file path and then it changes back to the old path. Once the update is complete, if I hit Undo, the link changes back to the correct updated path. Can you look at the files attached and see if you can determine what is causing this extra step?
Thanks,
macropodWServonealHi Macropod,
Not sure why, but I couldn’t get the links to stop changing back to the old file path. If there were 10 links, when I opened the file and chose “Yes” to update the data linked from other files, I can see the macro change the file paths to the new folders and then I can immediately see them change back. If I click on Undo, one of the links goes back to the updated file path. If I click Undo 9 more times, all of the links are updated one at a time. After reading that this macro works with both the .doc and .docm file extensions, I saved my .docm to a .doc file to see if it made any difference. The .doc file works without anything unusual happening. I can cut and past the Word and Excel files to any directory and the file paths are updated correctly. Any idea what system setting, etc. I might have that is causing the .docm file type to not work properly?
macropod
WSababenchristHi macropod
I have a question regarding Relative Paths in Word Fields. If my source file is an Excel 2007 and has target links to different docm’s in another file folder that are in the same path. Will this still keep links intact if downloaded to Vista or Windows 7 operating systems? I’m using XP myself so I’m unable to check
macropodWSababenchrist
WSNocheHi,
Thanks for developing this utility! When I load the macro to a file with a very basic hyperlink to another file in the same folder, save it as .docm, and re-open it, I get this error message:
Run-time error ’91’:
Object variable or With block variable not set
and the code stops at this line:
OldPath = Replace(.LinkFormat.SourcePath, “”, “\”)
I’m using Word 2007, set to allow Macros, on Windows XP. The hyperlink field is:
{HYPERLINK “doMaster%20Consulting%20Analysis.do”}
I’m assuming that I’m doing something stupid, but I can’t think of what. Any suggestions?
WSjscher2000I get this error message:
Run-time error ’91’:
Object variable or With block variable not set
and the code stops at this line:
OldPath = Replace(.LinkFormat.SourcePath, “”, “\”)
I’m using Word 2007, set to allow Macros, on Windows XP.
I’m not sure that hyperlink fields follow all the same rules as other kinds of link fields. Anyway, to prevent the error, add this on the line above that line:
If Not .LinkFormat Is Nothing Then _
(The trailing underscore is the continuation character: it runs the two lines together as one.)
WSNocheI’m not sure that hyperlink fields follow all the same rules as other kinds of link fields.
Thanks for the quick response! It looks like you’re right about hyperlinks being different. With the line you gave, the macro doesn’t change anything upon moving the files to a new location.
In another attempt, I had manually added the {FILENAME p} to all of the hyperlink paths, but after opening and closing the document a few times, Word took the liberty of changing them all to absolute paths 
I am wondering why the original post says that the macro will work with hyperlinks?
In a final attempt, when I try to use
{ LINK Excel.OpenDocumentSpreadsheet.12 C:\data\Consulting\Analysis\income.xml }
After I open and close, it replaces as
{ EMBED Package }
And doesn’t update the path upon moving.
macropodThanks for the quick response! It looks like you’re right about hyperlinks being different. With the line you gave, the macro doesn’t change anything upon moving the files to a new location.
In another attempt, I had manually added the {FILENAME p} to all of the hyperlink paths, but after opening and closing the document a few times, Word took the liberty of changing them all to absolute paths
I am wondering why the original post says that the macro will work with hyperlinks?
In a final attempt, when I try to use
{ LINK Excel.OpenDocumentSpreadsheet.12 C:\data\Consulting\Analysis\income.xml }After I open and close, it replaces as
{ EMBED Package }And doesn’t update the path upon moving.
Hi Ryan,
The field code does work with hyperlinks, but do note that it doesn’t affect the displayed link text.
As for link fields, only the macro works, as my initial post made clear. You can also use the macro with hyperlink fields but, again, it doesn’t affect the displayed link text.
The only way I can see of getting the { EMBED Package } result is if there’s a fault with your office installation.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSjedelThanks for all your work, this solves so many problems for me.
One problem I am having now though is with the updating of the data. When I open my .docm file, your macro runs perfectly and converts all of my links. However, before the macro runs, Word asks if I want to “update this document with the data from the linked files”. The problem is, the macro has not converted the links yet, so once the macro has run, the links already missed their chance to automatically update.
Is there a way for the macro to run the update AFTER it has converted the links so that it reflects the current data in the Excel doc?
Thanks again!
macropodHi James,
If the fields are ‘updating’ automatically, that’s because you’ve set Word to do so.
You can either disable Word’s auto-update (the process differs for pre/post Word 2007) or, after the line:
.Code.Text = Replace(.Code.Text, OldPath, NewPath)
add a new line with:
.Update
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSjedelHi James,
If the fields are ‘updating’ automatically, that’s because you’ve set Word to do so.
You can either disable Word’s auto-update (the process differs for pre/post Word 2007) or, after the line:
.Code.Text = Replace(.Code.Text, OldPath, NewPath)
add a new line with:
.Update
Thanks so much! That worked perfectly.
I really appreciate all of your work on this solution 
WSjedelmacropodThis solution worked great for my Word files, but now I am trying to do the same thing with my Excel links and not finding any solutions.
Do you have a solution to accomplish the same thing with Excel?
Thanks
Hi James,
You shouldn’t need a ‘solution’ for Excel. Excel maintains links on a relative addressing basis by default.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSjedelHi James,
You shouldn’t need a ‘solution’ for Excel. Excel maintains links on a relative addressing basis by default.
That’s what I keep reading, but it seems to be giving me the same trouble Word did.
Example : When I link a field from one document to the other, it creates a relative link.
=[Filea.xlsx]Sheet1!$B$5
This works fine so long as I don’t save and close the file. But, once saving the reopening the file it turns it in to a fixed link :
=[c:filesFilea.xlsx]Sheet1!$B$5
So when I move my files to a different directory, it no longer links properly.
It’s a similar problem to what your solution fixed for me in my Word files.
macropod
WSrush-rs09Hi macropod
I have some tables and charts in MSExcel2007, which is linked to MSWord 2007
I used the macro specified here,saved it, removed public option for AutoOpen,Set the Macro Settings to Low.
I copied the Excel & Word files to a different folder. When i open the Word File, i asks me whether to update the links or not,
When I say Yes, and check the links, the links are not updated.( I checked by inserting message boxes to confirm that macro is indeed run, when the file is opened)
I go to Macro and run it manually.First time it says the Excel file is still open and will not be able to update the links
Second time I run the macro the links are updated and refreshed.
I have 2 problems
1) How to get the macro to run automatically?
2)How to overcome this problem of It saying that Excel file is open, the first time?(I checked task bar/process view and confirmed that excel file is not open)
Request your help & thanks in advance
Regards
RK
macropodHi Rakesh,
I … removed public option for AutoOpen
There is no ‘Public’ option for the AutoOpen macro, so I don’t know what you removed.
When i open the Word File, i asks me whether to update the links or not
That is not caused by the macro. It may be due to your document’s ‘update automatic links on open’ setting. I suggest setting it to ‘off’ by unchecking that option.
When I say Yes, and check the links, the links are not updated.
That suggests the macro is not running. Maybe whatever you changed has caused this.
I go to Macro and run it manually.First time it says the Excel file is still open and will not be able to update the links
Second time I run the macro the links are updated and refreshed.
The Excel file may still be open because Word has been unable to update the links. Again, I suggest setting the ‘update automatic links on open’ option to ‘off’.
Overall, I recommend reinstalling the macro without making any changes, and setting the ‘update automatic links on open’ option to ‘off’. You should then get the results you’re after. You might also want to implement the code change I suggested to James Edel in post #67 above.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSrush-rs09Hi Macropod,
Many Thanks for your reply.
I set the update option to manual in my word 2007 template
I also added the line .Update in VBA macro and ensured that excel file is not open
Still i can’t get the macro to
1)Run automatically & update the links when i open word.
The procedure i did was
1) created word 2007 & excel 2007 files
2) imported macro saved the word file(set update link option to manual,macro security settings to low,saved as docm)
3)Now i copied the word2007 and excel 2007 to a different folder in the same PC(Windows XP SP2)
4) changed the data in excel sheet
5) Opened the MSWord 2007 file
Request your help on this.
Regards
Rakesh
WSrush-rs09macropodHi rakesh,
It seems there is a bug in the way Word 2007 returns the paths to files referenced in LINK fields, at least. For whatever reason, the string returned by ‘.LinkFormat.SourcePath’ sometimes includes both the filename and part of the range reference. I’ve now modified the code to work around the bug. If you download the new version and replace the code you’re now using (eg remove the module, without exporting it, before installing the new one), everything should be OK.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSyackboHi Macropod and all:
I hope I’m following protocol, here; if not please advise.
I am trying to use the suggested autorun macro to update my Excel links to the local folder where the word doc resides. This is in Office 2003. Everything works fine except that there are two Excel links in the header that stubbornly refuse to change their paths. Is there any reason why the header story would not respond to the code? Any advise would be greatly appreciated.
Thx.
macropodHi Brent,
Welcome to the Lounge.
As coded, the macro goes through all of the document’s story ranges (ie the body of the document, headers, footers, textboxes, etc), so any links in the headers should get updated.
Have you examined the field code in the headers? Simply changing the links doesn’t mean the content will change – you would need to update them for that to occur, as per the code change I suggested to James Edel in post #67 above. Even then, if the fields are locked (which they shouldn’t be if you want to update them), they may not update, so you’d have to add the line:
.Unlock
before the:
.Update
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSyackboThanks so much for your reply. Yes, I have verified that the field codes in the header are keeping the old path. It is as if the header is being bypassed for some reason. I am quite perplexed by it and am to inexperienced with vba to know how to test and get to the source of the problem. I have even moved the files to a completely different computer to rule out a problem with the current Word application. Same result. I don’t know what to do next.
Again, thanks very much for your attention and quick reply.
macropodWSyackboThank you- I will do that.
I decided to try to create a new document from normal.dot, add a header or two with links and test that way. This was under the presumption that my main document might be corrupted in some way. I learned that the macro works fine for the first header in this test document but fails to update the second header (second section). I my main document, there are several sections with differing headers. If you need it, I can send along the Excel workbook too- although it only consists of a couple of text lines.
WSyackboHi Macropod and all:
I have (hopefully) applied what I have learned over the past couple of days…
The subsequent macro does it for me. It penetrates all of the stories of my document and re-points Excel links (even in multiple unlinked headers in multiple sections) to the local folder. Many thanks to Macropod for doing the HEAVY lifting. My enhancement is merely a final polish on an already awesome vba code. Perhaps it will help someone and perhaps not. It might well be a detriment to the overall code, since I’m very much a novice at this stuff. All I can say, is that it is working for me currently. Hopefully, it will be a help to somebody down the road.
Many thanks to this resource and the fine folks who provide it!
Option Explicit
' Word macro to automatically update field links to other files (re-points links to local folder)
' Created by macropod; Enhanced SLIGHTLY by Brent Smith November 2010
' New solution posted at [url]http://lounge.windowssecrets.com[/url]
' Macropod and associates deserve credit for this macro
' Brent Smith makes the disclaimer that he is a novice and might have done more harm
' than good to this code. User take caution. The original code is also available at
' the aforementioned website. Macropod is AWESOME!!!
Dim TrkStatus As Boolean ' Track Changes flag
Dim Pwd As String ' String variable to hold passwords for protected documents
Dim pState As Boolean ' Document protection state flag
Sub AutoOpen()
' This routine runs whenever the document is opened.
' It calls on the others to do the real work.
' Prepare the environment.
With ActiveDocument
' Insert your document's password between the double quotes on the next line
Pwd = ""
' Initialise the protection state
pState = False
' If the document is protected, unprotect it
If .ProtectionType wdNoProtection Then
' Update the protection state
pState = True
' Unprotect the document
.Unprotect Pwd
End If
Call MacroEntry
' Most of the work is done by this routine.
Call UpdateFields
' Go to the start of the document
Selection.HomeKey Unit:=wdStory
' Clean up and exit.
Call MacroExit
' If the document was protected, reprotect it, preserving any formfield contents
If pState = True Then .Protect wdAllowOnlyFormFields, Noreset:=True, Password:=Pwd
' Set the saved status of the document to true, so that changes via
' this code are ignored. Since the same changes will be made the
' next time the document is opened, saving them doesn't matter.
.Saved = True
End With
End Sub
Private Sub MacroEntry()
' Store current Track Changes status, then switch off temporarily.
With ActiveDocument
TrkStatus = .TrackRevisions
.TrackRevisions = False
End With
' Turn Off Screen Updating temporarily.
Application.ScreenUpdating = False
End Sub
Private Sub MacroExit()
' Restore original Track Changes status
ActiveDocument.TrackRevisions = TrkStatus
' Restore Screen Updating
Application.ScreenUpdating = True
End Sub
Private Sub UpdateFields()
' This routine sets the new path for external links, pointing them to the current folder.
Dim oRange As Range, oField As Field, i As Integer
Dim OldPath As String, TmpPath As String, NewPath As String, Parent As String, Child As String
Dim done As Boolean
Dim j As Integer
Dim ct As Integer
' Set the new path.
' If your files are always in a folder whose path bracnhes off, one or more levels above the current
' folder, replace the second '0' on the next line with the number of levels above the current folder.
For i = 0 To UBound(Split(ActiveDocument.Path, "")) - 0
Parent = Parent & Split(ActiveDocument.Path, "")(i) & ""
Next i
' If your files are in a Child folder below the (new) parent folder, add the Child folder's name
' (or path - minus the leading & trailing "" - if there's more than one folder level) on the next line.
Child = ""
If Child "" Then Child = Child & ""
NewPath = Replace$(Parent & Child, "", "\")
' Go through all story ranges in the document, including shapes, headers & footers.
For Each oRange In ActiveDocument.StoryRanges
done = False
While Not done
ct = oRange.Fields.Count
For j = 1 To ct
'Set f = oRange.Fields
' Go through the fields in the story range.
For Each oField In oRange.Fields
With oField
' Skip over fields that don't have links to external files.
' A '.Linkformat' Property test should work, but is not reliable.
If .Type = wdFieldHyperlink Or .Type = wdFieldImport _
Or .Type = wdFieldInclude Or .Type = wdFieldIncludePicture _
Or .Type = wdFieldIncludeText Or .Type = wdFieldLink Or .Type = wdFieldRefDoc Then
' Get the old path. A .LinkFormat.SourcePath result should do, but is not reliable.
TmpPath = ""
For i = 0 To UBound(Split(.Code.Text, "\")) - 1
TmpPath = TmpPath & Split(.Code.Text, "\")(i) & "\"
Next i
OldPath = TmpPath
For i = 0 To UBound(Split(TmpPath, " "))
If InStr(Split(TmpPath, " ")(i), Chr(34)) > 0 Then Exit For
OldPath = Trim(Replace(OldPath, Split(TmpPath, " ")(i), "", 1, 1))
Next i
If InStr(OldPath, Chr(34)) > 0 Then OldPath = Replace(OldPath, Chr(34), "")
' Replace the link to the external file if they differ
If OldPath NewPath Then .Code.Text = Replace(.Code.Text, OldPath, NewPath)
End If
End With
Next oField
Next j
Set oRange = oRange.NextStoryRange
If oRange Is Nothing Then done = True
Wend
Next oRange
End Sub
WSwaldguyHi Macropod and all:
I have (hopefully) applied what I have learned over the past couple of days…
The subsequent macro does it for me. It penetrates all of the stories of my document and re-points Excel links (even in multiple unlinked headers in multiple sections) to the local folder. Many thanks to Macropod for doing the HEAVY lifting. My enhancement is merely a final polish on an already awesome vba code. Perhaps it will help someone and perhaps not. It might well be a detriment to the overall code, since I’m very much a novice at this stuff. All I can say, is that it is working for me currently. Hopefully, it will be a help to somebody down the road.
Many thanks to this resource and the fine folks who provide it!
Thanks to you! For some reason the original code was caught in an infinite loop — didn’t seem to recognize that it was at the end of file and kept trying to replace the same code.
NICE.
macropod
WSJoepi5Hello,
I’m new here so I don’t quite know how to post my problem,
I got a few problems with this macro;
1. I use this macro to link a word file to an excel file, the ‘root’ files need to be copied to various locations and the macros should still be intact.
But my links keep being linked to the ‘root’ files location. (i.e. if the template files are C:/Documents/Bas/report/template.xls and C:/Documents/Bas/report/template.doc. and i copy them to C:/documents/template.xls and C:/documents/template.doc, the links in the word file are still linked to C:/Documents/Bas/report/template.xls)
and i can’t find out why. what should I upload/post so someone can take a look at it.
2. I created my version by following a already made file (that has to do the exact same thing) and I exported the macro from that file and imported it into the new one. now I get a error everytime I start my new file. is this because of the macro being ‘linked’ to the older version?
If you need further explanation please don’t hesitate to ask me.
with kindest regards,
Bas
P.S. I’m very very new to macros, but have quite a large knowledge of excel and word itself.
macropodWSJoepi5I don’t know exactly what a sanitized version is, so i put the entire text of the macro in a notepad file.
And I wrote these files in Office 2003 (what my office uses, and the real files i will write in office 2010, I heard someone say there is a difference between 2003 and 2010 macro’s, is that correct?)
macropodHi Bas,
By ‘sanitized’, what is meant is files with no sensitive or personal information that you or your employer would be unhappy with others seeing.
Your document didn’t have the macro in it, so I added the macro. When I did that and saved and closed the file, the links updated properly next time I opened it.
Did you allow the macro to run? There is nothing about the code that is adversely affected by changes from Office 2000 to Office 2010. Office 2007 & later do work differently than Office 2003 and earlier, but that isn’t having an effect here. What could be having an effect is having your Office 2010 security settings set so high as to prevent all macros from running. You can check this in Office 2010 by going to Word Options|Trust Centre|Trust Centre Settings and making sure the ‘Disable all macros without notification’ option is not selected – I’d suggest using the ‘Disable all macros with notification’ option.
Updated document attached.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSJoepi5hi macropod,
I set the security level to the lowest possible.
I don’t know what is wrong is the macro not adding, that maybe my problem.
Have you tried copying the 2 files to different locations? that’s where it all goes wrong with me, it updates in the foler they stand in but when I copy the 2 files o a different folder the macro isn’t doing it’s work by changing the folder to the new one, it’s still linked to the old folder location.
can you explane how to save the word file whe nyou add the macro, office 2010 (and 2007) have a special way to save files with macro’s do I have to follow that or do it in a different way? and do I need to add the macro to excel aswell?
but thanks already for you help.
Bas
macropodWSJoepi5macropodHi Bas,
I’m wondering if your problem all along has been that you’ve added the ‘AutoFldUpdt.bas’ code to, say, your ‘Normal’ template instead of to the document you’ve been working with. After all, you confirmed that the macro worked correctly in the document I attached to a previous post. Since the macro code exists in that document and works correctly, the only logical conclusion is that you haven’t inserted the code into your document, but have inserted it into something else instead.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
macropodWSJoepi5macropodHi Bas,
How can I implement the code into my file properly?
By following the instructions given on the first page in this thread. Above all, make sure you import the code into the correct file!
Did your work when you copied them both to another location?
The code works correctly even if only the Word file is moved; but in that case, of course, the updated link won’t be able to find the Excel file is should be linked to.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSJoepi5The code works correctly even if only the Word file is moved; but in that case, of course, the updated link won’t be able to find the Excel file is should be linked to.
that is the whole deal it needs to do for me, I need to keep a excel file and a word file, the word with links from the excel file.
I have 2 saved as templates (1 Excel, 1 Word) and i need to multiply them and copy to different folders (keeping the excel and word together). But the word file links keep being linked to the folder the templates are in, and it won’t update to the new folders location.
to clarify where I use the for : the excel file is a standard form for testing ladders and step ladders, in the form you fill in the results from variable test done on the ladder/stepladder.
these results need to be in a test report, this is where the word file comes in. so now it is a standard test report with different outcomes.
because this is standard you have to keep the 2 template files clean and empty, but you should be able to multiply and copy them to a new folder for a new test.
macropodHi Bas,
I have 2 saved as templates (1 Excel, 1 Word) and i need to multiply them and copy to different folders (keeping the excel and word together). But the word file links keep being linked to the folder the templates are in, and it won’t update to the new folders location
If you’re using a proper Word template (.dot extension), that’s undestandable as the macro will be in the template and not in the documents created from it. And, when you created the new document, the macro in the template naturally directed the link back to its own folder.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSJoepi5macropodHi Bas,
But is the macro code actually in each of the files you’re creating? From all you’ve described, it would seem it isn’t.
I saved my templates (just a term for the clean empty files) as .doc with activted macro files
I don’t know what you mean by that – any doc file with a macro in it can run the macro – there is no such thing as a ‘.doc with activated macro’ file. Conversely, and just in case you’re saving the files in the Office 2007 & later format, files with a dotx or docx extension cannot have macros in them (you need to use the dotm and docm extensions). Having said that, if the macro is in your documents, the macro will run and update the link paths each time the documents are opened – unless of course, there is still something about your system’s secuirty settings that don’t allow macros to run. To test this, you could try running the ‘AutoOpen’ macro manually via Tools|Macro|Macros.
A completely different approach would be to save your ‘template’ as a true Word template (.dot or dotm file, without the ‘AutoFldUpdt.bas’ code) in your User Templates folder with the Document_New macro below in the template’s ‘This Document’ module and put the ‘AutoFldUpdt.bas’ file into the same User Templates folder. The Document_New macro will then automatically add the ‘AutoFldUpdt.bas’ code to each document created from your template. You will, of course have to with run the ‘AutoOpen’ macro after the first time you save the document (you can do this manually via Tools|Macro|Macros, or simply close & reopen the document and allow the macro to run of its own accord.
Private Sub Document_New() ' Imports code from a bas file into the Active Document. Dim SourceFile As String SourceFile = Options.DefaultFilePath(wdUserTemplatesPath) & "AutoFldUpdt.bas" If Dir(SourceFile) = "" Then Exit Sub On Error Resume Next ActiveDocument.VBProject.VBComponents.Import SourceFile On Error GoTo 0 End Sub
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSJoepi5hi,
thanks for that last post. i’ il try that.
in (atleast office 2010) when you import the macro to your file and try to save it it says (next is free translated by me from dutch to english, so it maybe be in other words/sentences then you are familiar with, but the meaning is the same):
” the next can not b saved as document without macros:
– VBA-project
If you you want to save a document without these functions, you have to click no to return to the dialogue screen ‘ save as’. choose a file type in which macros are turned on in the selection list file type.
do you want to continue savind the document wihout macros?
[Yes] [No] [Help]”
that is the message I receive then. when i press No, you have to select another file type, in the selection list there is a file tpye with the name : ” Word document with macros turned on”. When i save it like that the icon changes to an icon with a normal word file behind a orange exclamation mark. And the actual extension is indeed .docm
when I copy the files the macro is still in but but the path from which it get’s it’s links isn’t updated (the path when i press Alt+F9 I mean, that one is then still pointed to the folder I copied it from, not to where I have copied it to, updating the link doesn’t work aswell), I will post a screenshot about this aswell.
I will post a screenshot when I return home and have acces to internet on my personal computer.
WSJoepi5Hi macropod,
I only have insert the macro into the clean (the mother document so to speak), and then copied that ‘mother’ to other folders.
After that I didnot insert the macro again, when I open a copied version and press Alt+F11 the macro is in the same location as it is in ‘mother’ file.
Is this the error I am facing? that you have to manually add the macro again to the file?
as we speak I am also trying to implement your idea from a few posts ago, so far it looks like that is going to work.
I will keep you informed about this.
macropodHi Bas,
I only have insert the macro into the clean (the mother document so to speak), and then copied that ‘mother’ to other folders.
After that I didnot insert the macro again, when I open a copied version and press Alt+F11 the macro is in the same location as it is in ‘mother’ file.
Is this the error I am facing? that you have to manually add the macro again to the file?
If you inserted the macro into your document, then any copies you made of that document would also have the macro in them. When you press Alt+F11, which ‘project’ does the project window show the module as being in? It’s the bold entry above the module.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSJoepi5Hi Macropod,
It’s located in Project () so for me in this case it’s, Project (Template)
If I collapse that I see Microsoft Word -opjects, Modules (this is where the AutoFldUpd is in), and “verwijzingen”, I don’t know the correct english term for it something along the lines of redirections I assume.
I’m done with the new macro you wrote for the template (.Dotm) approach, that gives me the same problem, then it’s still linked to the file path’s path I created the .Dotm from.
I retyped the original AutoFldUpd, and came accros something I don’t understand, it’s about this sentence:
If your files are in a Child folder below the (new) parent folder, add the Child folder’s name
‘ (or path – minus the leading & trailing “” – from the parent if there’s more than one folder level)
‘ on the next line.
what does it do here and what should i type between the “”, after Child = “”
macropodI retyped the original AutoFldUpd, and came accros something I don’t understand, it’s about this sentence:
If your files are in a Child folder below the (new) parent folder, add the Child folder’s name
‘ (or path – minus the leading & trailing “” – from the parent if there’s more than one folder level)
‘ on the next line.
what does it do here and what should i type between the “”, after Child = “”[/quote]
Unless the linked file is kept in a different folder to the Word file, you don’t need to worry about that.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSJoepi5WSJoepi5Hi Macropod,
I’m glad to inform you that it now is doing the job :).
What I did was to import it to another project in the ‘mother’ file and BINGO! it worked.
but now when i copy them 2 files I need to run the macro manually, can this be automated aswell?
thank you very very much for all your help and support.
macropodHi Bas,
but now when i copy them 2 files I need to run the macro manually, can this be automated aswell?
If the macro in the source file runs automatically, I can’t see why it wouldn’t do so in the copies. If it doesn’t, then I think there is something odd about your system.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSalexburnHi,
I am having some problems implementing your macro.
I have a word document with several LINKs to external excel ranges as follows:-
{ LINK Excel.Sheet.8 "C:\Documents and Settings\user\My Documents\Development\Test1\Excel.xlsx" Sheet1!R2C2 a f 4 r }
I am trying to remove the Absolute path reference and have it linked by Relative path reference so that I can move this document to other directories and retain the links.
I have imported the macro you provided, saved as .docm, closed and then reopened.
When it opens, it asks me if I want to Update links.
I click either yes or no, it doesn’t matter which, then the document closes and reopens as expected.
But unfortunately, this is as far as I get – the document just freezes (crashes) and becomes unrecoverable.
I am using Windows XP (SP3) and Word 2007 (SP2) and Excel 2007 (SP2).
Desperate for any help.
Thanks
WSalexburnJust for reference, tried breaking the code down and running line by line.
The code seems to be stuck at the following:-
Next oRng
' The preceding code won't update linked *shape objects* in page headers
' or footers, so use the following code to update those links.
For Each oShp In .Shapes
With oShp
' Don't bother with the body of the document, as that's already
' been processed, and footnotes & endnotes can't contain shapes.
If .Anchor.StoryType wdMainTextStory Then
If Not .LinkFormat Is Nothing Then
' Get the old path. OldPath = .LinkFormat.SourcePath should work,
' but is unreliable as it sometimes returns part of the filename.
OldPath = GetPath(.LinkFormat.SourceFullName)
If OldPath NewPath Then .LinkFormat.SourceFullName = _
Replace(.LinkFormat.SourceFullName, OldPath, NewPath)
End If
End If
End With
Next oShp
Not sure if that helps with finding a solution to my problem.
Thanks again
macropodHi Alex,
Re:
I am trying to remove the Absolute path reference and have it linked by Relative path reference so that I can move this document to other directories and retain the links
You should leave the complete path information in the field code – deleting it could have unpredictable effects (an incomplete path may even interfere with the macro).
As for the code stalling at the ‘For Each oShp In .Shapes’ loop, I can’t see why it would do that. However, I now can’t seem to get that code to work reliably. Accordingly, I’ve deleted it from the module.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSalexburnSo am I now to come to terms with the fact that I cannot have relative paths in {LINK} fields?
Is there any other way that I can simply update paths in {LINK} fields to the documents current directory?
I find it very disappointing that we cannot easily link one Microsoft document with another without in depth VBA knowledge!
Cheers
macropodHi Alex,
As clearly stated in the very first post in this thread, all field paths in Word are absolute. Simply deleting the path from the field code doesn’t change that. For anything other than a LINK field, a field-code workaround is possible. The process for modifying fields other than LINK fields is explained in the first post.
For LINK fields,a macro is required. The macro attached to the first post in this thread updates the paths to all fields in the document, including LINK fields. What it doesn’t work with, though, is linked shape objects (such as Excel objects whose layout is formatted to anything other than in-line with text) in the document header or footer. Aside from that exception, the macro should do what you want. For headers & footers, the solution is to use ‘ in-line with text’ formatting. whilst this might not always be convenient, there should be few cases where such formatting can’t be made to work with the desired document layout.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSbustamantejbHi Alex,
What it doesn’t work with, though, is linked shape objects (such as Excel objects whose layout is formatted to anything other than in-line with text) in the document header or footer. Aside from that exception, the macro should do what you want. For headers & footers, the solution is to use ‘ in-line with text’ formatting. whilst this might not always be convenient, there should be few cases where such formatting can’t be made to work with the desired document layout.
Hi Macro,
Regarding the linked shaped objects you mentioned, does that include excel charts? My reason for asking this is because i want to make the links paths for excel charts relative in ms word.
macropodRegarding the linked shaped objects you mentioned, does that include excel charts? My reason for asking this is because i want to make the links paths for excel charts relative in ms word.
As per the passage you quoted, yes – unless they’re in the header or footer and formatted as anything other than in-line.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSChris ScottHi Macropod,
I have been trying your method of creating relative links and it is still giving me some trouble. It seems as though once I save and close the document that the FILENAME field changes from an updatable field, to just the path.
The ‘main’ document that I am using is located here: D:Installation Files– Next Co-op Student –Co-op Resources Guide-good – Copy.docx. This is the document that I created the FILENAME field in, so that may be why it is showing up in the re-opened doc.
Here is the field code before closing: { HYPERLINK “{ FILENAME p }\..\01 PEAKS Testing 5.2” }
And then after re-opening: { HYPERLINK “file:///D:\Installation%20Files\–%20Next%20Co-op%20Student%20–\Co-op%20Resources%20Guide-good%20-%20Copy.docx\..\01%20PEAKS%20Testing%205.2” }
I was wondering if you could help me figure out why this is happening.
Thanks!
Edit: Also I should note that when the file is re-opened the hyperlink still works.
macropodHi Chris,
I see you’re working with docx files, which suggests Word 2007 or later. Word 2007 introduced some changes to the way fields with external links are handled, and this may be one of its victims. The macro solution should still work, though.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSChris ScottHi Chris,
I see you’re working with docx files, which suggests Word 2007 or later. Word 2007 introduced some changes to the way fields with external links are handled, and this may be one of its victims. The macro solution should still work, though.
It seems as though I am having the same issue as some else. It is giving me error 91 when running the macro.
I included that extra code line and it still won’t work. I have deduced that the issue is that it cannot find the old path. Here is the line that it now fails at:
MsgBox OldPath & vbCr & NewPath & vbCr & Replace(.LinkFormat.SourceFullName, OldPath, NewPath)
When hovering my mouse over the OldPath item there are just empty quotes (” “).
Thanks!
macropodWSChris ScottHi Chris,
Actually, that message box shouldn’t be there – it’s left over from testing. I’ll have to remove it.
Nonetheless, that doesn’t solve your problem. Are you adding the code to a new (unsaved) document, or to an existing document?
Hi,
Ok. And I am adding it to an existing document.
Thanks
Edit: So I removed the msg box line and also created a new document to test it in. I am still getting the error with it not being able to find the OldPath. It is now occurring in this line:
If OldPath NewPath Then .LinkFormat.SourceFullName = _
Replace(.LinkFormat.SourceFullName, OldPath, NewPath)
There seems to be an error setting the OldPath variable. Could this possibly have something to do with Hyperlinks? What would you suggest I do?
Thanks
macropodWSChris ScottHi,
Yes the the hyperlink still shows the path to the old location and the link is still valid.
This MsgBox still gives an error. Same as before with the OldPath not being set.
Also, in the OldPath variable set, this portion – .LinkFormat.SourceFullName – is giving the message .
Thanks
macropodHi,
Yes the the hyperlink still shows the path to the old location and the link is still valid.
This MsgBox still gives an error. Same as before with the OldPath not being set.
Also, in the OldPath variable set, this portion – .LinkFormat.SourceFullName – is giving the message .
Thanks
Hi Chris,
Can you upload a sanitized copy of the document for me to look at?
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSjerry393Hi there macropod.
Thx for this great macro you saved a lot of time. It works perfectly for me, except for the fact that it changes formating of the linked data, which is a porblem for me.
Changes:
from:
{ LINK Excel.Sheet.8 “old path” “Načrti zbirnik!R3C2:R8C9” a f 4 h * MERGEFORMAT }
to
{ LINK Excel.Sheet.8 “new path” “Načrti zbirnik!R3C2:R8C9” a f 4 h }
Some testing showed that I need the * MERGEFORMAT to keep the formating of the tables.
Do you have any ideas how to change the macro, so it will keep the * MERGEFORMAT parameter?
macropodThx for this great macro you saved a lot of time. It works perfectly for me, except for the fact that it changes formating of the linked data, which is a porblem for me.
Changes:
…
{ LINK Excel.Sheet.8 “old path” “Načrti zbirnik!R3C2:R8C9” a f 4 h * MERGEFORMAT }to
{ LINK Excel.Sheet.8 “new path” “Načrti zbirnik!R3C2:R8C9” a f 4 h }
…
Do you have any ideas how to change the macro, so it will keep the * MERGEFORMAT parameter?
Hi Jerry,
There’s nothing in the code to delete the * MERGEFORMAT parameter – it only changes the filepath and (should) leave everything else in the field code alone. Which version of Word are you working with?
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSSimonB1978Hi,
There seems to be a problem with the following statement:
.LinkFormat.SourceFullName = Replace(.LinkFormat.SourceFullName, OldPath, NewPath)
While the link gets updated (wdFieldLink), I get an “execution interrupted” dialog box, as if I pressed ctrl+break on the following line (End If). No matter what the next line is it does the same.
When I try and do it in debug mode (pressing F8) it gets done properly…
Any idea?
Simon
Windows XP(SP3), Word 2003(SP3)
macropodHi Simon,
Your description suggests a problem with your system.
Have you tried repairing Word (Help|Detect & Repair)?
Have you tried running Word without any add-ins (winword.exe /a from the Start|Run panel) and then opening the document with the macro?
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSSimonB1978Hi Paul,
Tried both suggestions without success. Does the same thing on a co-worker computer. I tried adding .LinkFormat.AutoUpdate = True before the SourceFullName, and now the code stops on the SourceFullName.
It seems to have a problem accessing any property of the .LinkFormat
I made sure all my fields are unlocked too. If it matters, the link is a range in an Excel document
Any help would be greatly appreciated!!
Simon
macropodHi Simon,
It’s strange that everything works OK when the macro is run manually but not when it’s run automatically. That still suggests an issue with your system, though what that issue might be I have no idea. Do the LINK fields in the document actually have the full path to the Excel file?
I suppose you might avoid the error by adding an ‘On Error Resume Next’ statement before the ‘.LinkFormat.SourceFullName = Replace(.LinkFormat.SourceFullName, OldPath, NewPath’ line (I haven’t tested this), but then the link might not update, which sort of defeats the purpose of having the macro …
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSSimonB1978Hi Paul,
The OnError trick has already been tried, but no error is actually thrown, it’s more like if there was a breakpoint (without being one). Anyways, I just tried with a blank dummy Word and Excel documents and it works (yeah, should have tried that before…). I now need to figure out what is wrong with the documents I need to work with.
Thanks for your time!
Simon
WSSimonB1978
WSlobstermanWSSimonB1978
WSsliebermacropod
WSLeilaniHi Macropod,
I have IncludeText links that are in a text box, and those links are not getting updated by the macro. Any links outside the text box are being updated correctly. How do I modify the macro to get the links inside the text box to get changed to the newpath?
Update:
I created a small test and was able to get the IncludeText paths in a text box working. I took my original file (which has quite a bit going on in a single page) and eliminated parts of the file, testing after deleting each small part. I eventually got down to one file where the text box include paths did NOT work and with one elimination (of another text box, but with no includetext in it) it did work. So then I thought it might be a problem with two text boxes, but again my simple tests with two text boxes did not have any problems. I’m not sure what it is about my file that is causing the text box to get skipped in the macro (in debugging it appears that it doesn’t ever look at the fields.)
I have included the two files. The only difference should be the elimination of the one text box represented a perforation (dashed line).
Thanks,
Leilani
macropodWSLeilaniWe figured out the problem. All TextFrames are in a single StoryRange so once you have the TextFrames StoryRange you then need to loop through each TextFrame in the TextFrames StoryRange. We added another loop in UpdateFields():
Dim oSubRng As Range
With ThisDocument
[INDENT]For Each oRng In .StoryRanges[/INDENT]
[INDENT][INDENT] ‘ Go through each SubStoryRange in the story range[/INDENT][/INDENT]
[INDENT][INDENT] Set oSubRng = oRng[/INDENT][/INDENT]
[INDENT][INDENT] While Not (oSubRng Is Nothing)[/INDENT][/INDENT]
[INDENT][INDENT]’ Go through the fields in the story range.[/INDENT][/INDENT]
[INDENT][INDENT]For Each oFld In oSubRng.Fields[/INDENT][/INDENT]
[INDENT][INDENT][INDENT]With oFld[/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT] ‘ Skip over fields that don’t have links to external files.
‘ A ‘.Linkformat’ Property test should work, but is unreliable.
If .Type = wdFieldImport _
Or .Type = wdFieldInclude Or .Type = wdFieldIncludePicture _
Or .Type = wdFieldIncludeText Or .Type = wdFieldLink Or .Type = wdFieldRefDoc Then[/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT] ‘ Get the old path. OldPath = .LinkFormat.SourcePath should work,
‘ but is unreliable as it sometimes returns part of the filename.
OldPath = GetPath(.LinkFormat.SourceFullName)
‘ Replace the link to the external file if they differ.
If OldPath NewPath Then .LinkFormat.SourceFullName = _
Replace(.LinkFormat.SourceFullName, OldPath, NewPath)
End If[/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT]End With[/INDENT][/INDENT]
[INDENT] Next oFld
Set oSubRng = oSubRng.NextStoryRange
Wend[/INDENT]
Next oRng[/INDENT]
End With
WSDiesel94Thank you Paul.
The code in the attached file is along the lines of what I figured I’d end up doing. I’m going to have to brush off my VBA hat and do a little research into some of the methods I saw in the code, but it looks very similar to what I had in mind. It’s a shame (if not a crime) that MS fails to recognize just how important their software becomes to the operation of small businesses like mine. To release this half baked, less than reliable POS (Piece Of Software) to people that depend on its faithful operation is bad business. They also seem to forget that this software is a tool, not entertainment. I’ve never opened Word because I thought it would be fun to write a document. I open it because I need a tool to produce work product. If that tool can’t function predictably and reliably, I’ve got to find another….but I digress. I’m getting a little cranky because of all the problems my “upgrade” to Office 2010 is causing.
Again, thanks!
macropodHi Diesel,
The issue of relative paths has nothing to do with an upgrade. All Word versions have worked this way, at least as far back as Word 4 (1993). And all Word versions have included tools for updating link paths. The macro just does it automatically.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSelmsy2424I have been testing the above macro on a word 2010 document. I have links in the word document coming from an excel document. The links will update fine as long as I move the documents around on my actual machine. As soon as I put the documents on a network the links from word to excel no longer work. I believe it has to do with the UNC path. Is there any way around this? Is there a way to write the macro any differently to accommodate this? This has been a major issue for our organization. Thank you.
macropodHi Elmsy,
The links should work on a network just as they do on a stand-alone machine. Are you sure the macro runs on the network (ie Word’s macro security isn’t too high) and that the network permissions are appropriate for what you’re trying to do?
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
macropodHi Himanshu,
Thanks for the feedback.
If the Excel file is always in the same location, and only the Word file is being shared, you shouldn’t need any code at all – the standard absolute path should remain unchanged.
I don’t know why you’d be getting a syntax error at line 85, unless the code hadn’t been copied correctly. You’ll note that no-one else has had that problem.
As for the network path issue, there is some code you could use to retrieve the UNC path at: http://support.microsoft.com/kb/160529. To use, you’d need to add a test for the presence of a drive identifier (eg C:) at the start of the NewPath and, if not, call the function to retrieve it. There is also an implementation you might be interested in at: http://access.mvps.org/access/api/api0003.htm
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSelmsy2424Thank you for responding to my earlier post. My macro security settings seemed to be ok along with my network security settings. I have uploaded two documents which are linked to each other. Would you be able to test them with the Macro’s that are currently in them. The issue arises when I put them onto a network. It looks like it is going to work when I open the word doc and then when it is done trying to update my links are gone. Please bear with me as I am new to creating macro’s. It would be greatly appreciated if I could get this to work for my company. Thank you very much.
macropodHi Himanshu,
If the code works on a stand-alone PC, I can’t see why it wouldn’t work on a network – without any changes, unless there’s a permissions issue with the network setup (and that isn’t a Word issue). As I no longer have access to a network to test the code on (I’m retired), I can’t test that side of things for you anyway.
Also, the fact that you got an error with ‘If .Type = wdFieldHyperlink’ strongly suggests there is something else that’s wrong with your system. The wdFieldHyperlink variable has existed since Word 97 at least. And, since there’s no code in your Word document, I can’t even check whether you’ve implemented it correctly.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSloftymeatHi Macropod,
I put your macro into a blank word file and linked an excel sheet. I can see the data and everything is Ok. Once I close the word file and reopen it, and it auto runs the macro, I get an error that the excel file that I have linked is already open and you can’t have two of the same file name open. I’ll get this error a few times before it asks to End or Debug the VBA script. Excel isn’t running, but if I look at my task manager when I open the word file, I can see it start the Excel.exe process. When it eventually asks me to End or debug it shows it gets stuck at the OldPath NewPath line.
The weird thing is that if I force close the excel process 2 or 3 times when I first open the word file, it will actually update the link to the excel file like it is suppose to do. Am I doing something wrong?
Thank you
macropod
WSkniggetHi, this macro is a great idea but I am having problems and wonder if a little clarity would help to get me going..
If your files are always in a folder whose path bracnhes off, one or more levels above the current
‘ folder, replace the second ‘0’ on the next line with the number of levels above the current folder.
or
If your files are in a Child folder below the (new) parent folder, add the Child folder’s
‘ path from the parent (minus the leading & trailing “” path separators) on the next line.
Which files? The files that contain the data (a spreadsheet in my case) or the files that are pulling the information (a word document in my case)?
WSjfmillerI have Word 2010. So far, this seems to work for me.
My explanation is a bit lengthy. Understanding my folder structure will help you understand the field code.
Example Project Structure
10100824_Project folder is the top-level folder.
The 10100824_Project folder has individual Word files for publication 10100824; there is one Word file per chapter.
Inside the 10100824_Project folder is a sub-folder named 10100824_Pictures.
The 10100824_Pictures sub-folder has PNG files of screen captures of entire software screens.
Inside the 10100824_Pictures sub-folder is another sub-folder named Button_Images.
The Button_Images sub-folder contains PNG files of screen captures of individual software control buttons.
Your task: You want to link graphics by reference into a Word file.
Here is the field code to create a relative link that displays a PNG file of a screen capture of an entire software screen:
{INCLUDEPICTURE 10100824_Pictures/10100824_XXXX.PNG}
Where:
Here is the field code to create a relative link that displays a PNG file of a software control button:
{INCLUDEPICTURE 10100824_Pictures/Button_Images/ZZZZ_ZZZZ_.png}
Where:
macropod
WSrwhitmoreWSrwhitmoremacropodWSrwhitmoreYes, I have lots of links to bookmarks within the same document in addition to links to bookmarks in a second document.
Without knowing what field it’s throwing the error on, the only thing I can think of is that you have a hyperlink in the document that only points to another location within the same document. Do you have such hyperlinks?
macropod
WSmqdiasDeat macropod,
I’ve found this great post of your’s and congratulations.
I’ve downloaded and installed as per your instructions. I’ve having the same message as in rply #152: “Run-time error ’91’: Object variable or With block variable not set”. When I clicked on Debug, it sent me to this line in the macro code: OldPath = GetPath(.LinkFormat.SourceFullName)
I also have some links to other objects in the same doc..and i’ve tried this:
For links to bookmarks within the same document, one would ordinarily use cross-references, not hyperlinks.
You could try inserting:
On Error Resume Next
before:
If .Type = wdFieldHyperlink Or .Type = wdFieldImport _
After i close and open again the documente it returns a message that Word doesn’t have enough memory…
Can you help me?
Thanks
macropod
WSChrisAHi
I have a problem that may have been addressed here but am looking to create a template word document linked to an excel worksheet to contain loads of jobsite information and service criteria to populate the word report. The idea was to create the template with its worksheet in a folder and then copy the entire folder and rename it to the different job folders we have.
Using word and excel 2007 when we copy the folder to a new location the links in the word document still go back to the original excel file in the original folder.
I have tried changing the ‘Link’ by using alt f9 and changing the text but it doesn’t seem to accept the change in folder name for the file location.
Any help would be greatly appreciated.
Many thanks
Chris
WSjfmillermacropodHi Chris,
Changing the file & folder names should work. In fact, changing the folder name is what the macro in http://windowssecrets.com/forums/showthread//102080-Relative-Paths-in-Word-Fields-(All)?p=584769&viewfull=1#post584769 does.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
macropodWSChrisAHi Paul
I have tried putting this macro back in and changing the file paths again and they revert back to original when i re open the document.
Maybe i am expecting too much, i am trying to create numerous word documents each tied to its own dedicated spreadsheet and then move them as pairs to each jobfile, would i be better to embed the entire spreadsheet in the word document and then could i copy anywhere?
Thanks
Chris
macropodWSChrisAHi Paul
Unfortunately i am trying to achieve the opposite effect in some ways. I am trying to create a word doc that has a number of tables controlled by an excel sheet and then clone the combination to make multiple versions i can then paste certain components into to make site specific. Is there a simple way of doing this?
macropodI really don’t understand what you’re trying to do, or how it relates to relative paths.
This thread is about linking Word documents to Excel files, etc, when they’re in the same folder as the Word document, or in a related folder, even when the folders for both change in concert.
For linking to Excel workbooks, you’d need the macro solution, which will automatically update the links to point to whatever folder the Word document is in (or, as discussed in the code, a related folder).
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSChrisAHi Paul
What I am trying to do is to create a template linked word and excel file and then to clone copies that can be put in separate folders. I wanted to be able to change the link paths to match the new folder as such.
C/Reports/word template
C/Reports/excel template
with both the above linked.
Then create copies of both and place in
C/Job1/Reports/Word Job1
C/Job1/Reports/Excel job1
and be able to change the links to mean the new word doc looked for the new excel sheet the do the same again for job 2 etc ctteating clones of the original templates to avoid having to re create the whole report structure from scratch
Many thanks for your time thinking on this, if i am in the wrong thread i apologise.
Regards
Chris
macropodWSChrisAmacropodHave you added the macro to the document as desribed in the post (http://windowssecrets.com/forums/showthread//102080-Relative-Paths-in-Word-Fields-(All)?p=584769&viewfull=1#post584769)?
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSChrisAHi Paul
Yes I have added the macro and enabled it to run automatically but i still get the links tied to the original excel spreadsheet when i move the word document as oposed to the copy i have made. The change that has happened is that i am now able to edit the paths in the word document to move the links to the location of the excel copy and the word report copy then updates to the excel copy only.
The frustrating thing here is that i was hoping to create the two templates (word and excel) and copy them into the 1000 or so job folders we have and only then have to edit their names and change data in the individual excel sheets.
Thanks
Chris
macropodWSChrisAHi Paul
I have been looking at the macro and it might be down to the security settings on our systems, is there a way af asking for a component part of the new path to link to as if i make the changes manually it works.
What i am doing is this;
Here is 1 of the links {LINK Excel.Sheet.8″\\svr1\VT\Properties\Blank\Audits\Template.xlsm”R!R1C2:R25C11ap*MERGEFORMAT} and there are quite a few within the word document.
When i make a copy of the word document the links stay to the original excel file but what i would like to be able to do is to save the new word doc to a new folder where the ‘blank’ in the path is the new folder name then create a copy of the excel sheet placing it in the new job folder and have some way of updating the ‘blank’ in the path of the link in the word doc to the new folder name and hence the new excel sheet.
i am probably making this more complicated than i need but am already siccessfully copying linked excel sheets to and fro the job folders and they update each other, if i allow it,
Many thanks for your help
Chris
macropodHi Chris,
You can only work with ‘real’ paths – if you have an invalid or missing folder name, the link won’t work.
Question: Are you adding the macro to a document, or to a template? It’s really only designed for documents. If you are using a template, it’s no good adding the code there; what you’d need is code that imports the bas file into the new document. Then, once you’ve saved the new document, if you close and open it the macro will update the link paths.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSChrisAHi Paul
Thanks for that, I have put the macro in a template that i use to create documents and once they are created the links work perfectly as you said they would updating as required. The path i have created is ‘real’ as there is a template folder named ‘blank’ that is used to create all the site files by copying and then renaming. What i am trying to find is a way of converting or creating a macro that inserts the new folder name into the path in place of the folder ‘blank’. Structure wise they will always be in the same level as the blank folder so it only that name that has to change each time, your thoughts would be greatly appreciated.
Many thanks
Chris
macropodHi Chris,
With the macro, you don’t need to worry about what the ‘blank’ folder name becomes; all you need to be concerned with is which folder the Word document will be in. If that’s not the ‘Audits’ folder, there are comments in the code to show you how to add the child path from, say, whatever the ‘blank’ folder becomes (and you don’t even need to know the new name), to the ‘Audits’ folder. The only time there would be an issue is if the Word document was located in the ‘Properties’ folder or lower.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSChrisAHi Paul
The idea of the ‘blank’ folder name was to be able to get the new word doc to link to the newly created copy of the excel template saved in its new folder rather than the original one it was linked to. It seems there may be a way by setting the child path but I have no experience in that.
The new folder will be at exactly the same level as ‘blank’ in the properties folder.
I will start looking up info on child and parent folders to see if i can understand this some more.
Many thanks for your help.
Chris
macropodWSChrisAmacropodWSChrisA
WSaJacomHi.
I’m trying to get the file name removed from the FILENAME field by using macropod’s suggestion in Office 2010:
{INCLUDEPICTURE “{FILENAME p}\..\Image.png”}
How this works: The FILENAME p field extracts the current file’s name and path. The following ‘\..\’ or ‘/../’ tells Word to ignore the filename and get just the path. Then all you need to do is to add the source file’s name & extension.
The same approach can be extended to implement a form of relative addressing. For example:
{INCLUDEPICTURE “{FILENAME p}\..\My PicturesImage.png”}
looks in the child folder named ‘My Pictures’ and:
{INCLUDEPICTURE “{FILENAME p}\..\..\Image.png”}
looks in the parent folder, while:
{INCLUDEPICTURE “{FILENAME p}\..\..\My Pictures\Image.png”}
looks in the parent folder, then its child folder named ‘My Pictures’ (a sibling folder, so to speak).
But I can’t get Word to go to the parent folder. See 1.PNG and 2.PNG. I’ve tried //..//, \..\, .., /../, /, \, //, , and everything in between. I’m also interested in some other way of achieving this. Maybe through string manipulation using the = (Formula) field?
Thanks
macropodHi aJacom,
You can’t remove the Filename from a FILENAME field. I’m not aware of anything in the post you refer to that suggests otherwise.
I see that you’re also trying to use this technique with a LINK field. As clearly stated in the post you’ve quoted from, you cannot use this technique with a LINK field – you need the macro-based solution for LINK fields. The macro-based solution also works with the INCLUDETEXT, INCLUDEPICTURE, etc. fields.
Formula fields are incapable of performing the required string manipulations.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSaaljoHi Paul,
I’ve had a look at your solution for relative addresses in LINK fields in Word, linking to info in Excel sheets. Unfortunately, my understanding of VBA is extremely limited and I am currently unable to get it to work.
The company I work for is unwilling to move their system to a totally digital database, be it SQL or whatever, thus the whole thing has to be semi-manual. As a result I am keen to get a pseudo-autonomous database running from excel, while launching ‘reports’ in the form of word documents (not templates). Thus I need the word file to be able to update its LINK field addresses every time the excel file and the word document linked to it is copied to any other folder from a central folder that contains the ‘templates’ (they’re all saved as .xlsm for Excel and .docm for Word).
macropod
WSjcvarneyI have implemented the macro for a Word 2010 file with numerous links to an excel file and it works well in my initial folder location. I then moved the files to a flash drive and all links update fine. When moving the files back to my original folder some of the links, but not all, now show “Error! Not a valid link”. I have checked the links using ALT-F9 they appear to be correctly updating the file path like those that are still working. Any ideas on why some of the links become broken while others keep working. Have you seen this before?
macropod
WSsoapyxHi,
Thank you so much for doing all this work, Paul! I’ve been having a small issue using your macro, though. So I’ve created two folders, and in one folder I have everything set up the way I want. Suppose that I have WordDocument1.docm with your macro imported and Table.xlsx. So then I copy and move them to a different folder, still named WordDocument1.docm and Table.xlsx. However, when I open the new copy of the document in the new folder and enable macros, it says “There is already a file called Table.xlxs open, please close it etc, etc…” and the link isn’t updated to the new folder path. Is there something I’m doing wrong? Thanks!
WSjcvarneyThat’s not something I’ve come across before. What happens if you press Ctrl-A, F9? Do the fields then refresh correctly?
Paul, I have come back to this issue (see post #186 in this thread) finally and my latest observation is that the initial folder is nested pretty deep and includes several folder names that include a space in them.
When I move the files to the flash drive, the path is pretty simple. When moving back to the initial location, the links that are pictures update fine still but all others get the error. So I close the file without saving. If I then change the spaces in the folder names to underscores, the macro works fine for all links.
So my work around is to instruct the other users of the file to ensure no folders in the new path have spaces in their names. The problem is that some of the users may not be allowed to control the folder names when they have to use the files from a shared location.
Any ideas?
Regards,
macropodWSsoapyxYes, of course I understand I shouldn’t have them both open at the same time. What I mean is that even when Word is the only application that I had running on my entire system and I only opened one file – the .docm that I’m talking about – from one location, it would still give me this error. Is there anything that might cause that you can think of?
macropodHi soapyx,
That’s rather odd. It’s a message you typically get from Excel if you try to open two files with the same name (regardless of folders) at the same time. The only possible explanations I can think of are that:
• There is a timing issue on your system, such that the opening/closing of Excel to update one link to the file doesn’t conclude before the next attempt to update a link to that file occurs;
• You have two or more documents open that are concurrently trying to updating links to Excel files with the same names; or
• There’s a background instance of Excel (eg as a result of running a macro) that failed to terminate, leaving the source file open.
The last of these should be easy to test: Simply try opening the file in Excel. If you get the same message, that’s what the problem is.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSCheeSiongHi Paul,
Had the macro running and links got updated without error.
Only problem is every time I open the document, will be prompted to save when closing. I thought the “ActiveDocument.Saved = True” is supposed to tell Words not to prompt when closing? Seems like not working.
I’ve also tried “ThisDocument.saved = True” and “Me.Saved = True” but all don’t work.
macropodSetting ‘ActiveDocument.Saved = True’ tells Word the document has been saved, but that status only applies at the point the command is executed. If you were to immediately close the document, you shouldn’t get a prompt to save. However, if you make any changes (even the kind that a print preview might trigger), the saved status is lost and you’ll be prompted to save upon closing.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSCheeSionghi Paul,
I get prompted to save even if I close the document right after opening and executing the AutoOpen script.
I have tried putting “ActiveDocument.saved = True” after the “Call MacroExit” command, right before Exit Sub. And I had tried setting a breakpoint at the Exit Sub point. Even right after executing the “ActiveDocumant.Saved = True” command, the value of “ActiveDocument.Saved” is still “False”.
Is there any setting in Words that make “ActiveDocument.Saved” read only?
macropodGiven that ‘.Saved = True’ is the last procedure to be executed before the macro finishes, it would seem you have something else that undoes that state afterwards. Alternatively, given that there is no actual ‘ActiveDocument.Saved = True’ line in the code I posted, your repeated references to it suggest you may have changed the code.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSCheeSiongmacropodThe Mod I made was to add “.Update” in the UpdateFields() subroutine, After replacing old path with new path.
That may or may not be needed; it really depends on whether the source files are likely to have been changed and whether you have Word’s ‘update automatic links at open’ option checked. You shouldn’t need both. If you have the latter set, though, that could explain the effects you’re seeing.
I have tried adding “ActiveDocument.Saved = True” right before the End Sub of AutoOpen().
That really isn’t going to add anything useful to what the code’s already doing.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSCheeSiongHi Paul,
To investigate my problem, I had create a small Macro (Macro1) that has only one line: ActiveDocument.Saved = True
Below is my observation:
When I open a document, the AutoOpen macro runs, all my links get updated (within a few seconds). Spellcheck also completed within seconds.
If try to close Document now, will get prompted to save. Tried running Macro1 and still get prompted to save when close.
But if I wait about 1 minute after opening the document, then run Macro1, document can then be closed without prompt to save!
Even if I tried editing the document within the one minute, as long as I run Macro1 one minute after opening document, won’t be prompted to save.
Any idea what could be the cause of this strange phenomenon?
WSCheeSiongHad isolated the problem to the .Update command
If I do not execute the .Update command, ActiveDocument.Saved = True works.
If I Execute the .Update command, ActiveDocumentSaved = True can only work 1 minutes later.
Problem is I need the .Update command to force the links to get updated. If not, no matter the link is set to automatic or manual updating, they don’t get updated.
macropodSeems to me the simple solution, though I don’t know why it would be necessary, is to wait at least one minute after opening the document before closing it (or simply press Ctrl-S to save anyway).Either that or omit the .Update command, which you should only need if the file locations have been changed and the source file’s content has been changed.
How often do you need to open the document in a new location? If it’s irregular, consider changing the macro’s name so that it has to be run manually instead of automatically.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSCheeSiongHi Paul, For some unknown reason, the linked fields in my word document that points to an Excel file on the network doesn’t get updated automatically. This has nothing to do with your Macro because even on a document without any Macro, the links don’t get updated automatically. I had tried setting the links to automatic update, added the network path to Word’s trusted location, turn on “Update automatic links when open”, but nothing works. Only way is for me to select all, then F9 to update. Or use a macro for that purpose.
That is the reason why I had to use the .Update command in my Macro to force Word to update the link, even if the location doesn’t change.
I had “by-passed” the problem by creating a Wait() subroutine:
Sub Wait(sec As Single)
Dim tim As Single
tim = Timer
While Timer < tim + sec
DoEvents
Wend
End Sub
then in AutoOpen (), added Wait 60 before ActiveDocument.Saved = True, and problem solved (bypassed).
But I'm still curious why this problem happened…..
macropodIn that case, you don’t need something as elaborate as the relative paths macro – probably all you need to force the update is a Document_Open macro in the document’s ‘ThisDocument’ module, coded as:
Private Sub Document_Open() ThisDocument.Fields.Update ThisDocument.Saved = True End Sub
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSCheeSiongHi Paul,
Yes I had tried a simple “Update” macro before. But that only works for some users.
Due to security reasons, different users in the company were given different access to the network drive. So it ended up that for some users, the folder is “\Volume_1EOEngineeringReports” while for some others, the folder is “\Engineering1Reports” although they actually points to the same file. Thus the need for your relative path macro.
macropodOK, then rather than using a fixed-time loop, you might try something along the lines of:
While .Saved = False DoEvents Wend
after the initial ‘.Saved = True’ line, though the only reason I can think of that this could make any difference is if there’s some kind of timing issue with the link updating.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSbcnHi Paul,
Sometimes, when we move files from our server to a local machine, all our relative paths for INCLUDEPICTURE links become absolute paths. Our Word documents are in the same level of a folder called PICTURES with all the pictures to which the pictures in the document link to. The paths in the Word file pictures look like this: { INCLUDEPICTURE “Pictures/Docking1.png” * MERGEFORMAT d }. So I guess the pictures themselves are one level below the Word document. Taken this into account, what needs to be changed in your code (AutoFldUpdt.zip) to convert the absolute paths to relative paths?
Thanks a lot!
Daniel
macropodHi Daniel,
From what you’ve described, you don’t need the macro solution, just the field code one – which is more than sufficient for INCLUDEPICTURE fields.
Assuming ‘Pictures’ is a sub-folder (you can check this in Windows Explorer), you should be able to use:
{INCLUDEPICTURE “{FILENAME p}/../Pictures/Docking1.png” d}
or:
{INCLUDEPICTURE “{FILENAME p}\..\Pictures\Docking1.png” d}
Note: The ‘* MERGEFORMAT’ switch contributes nothing.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSbcnHi Paul,
Thanks for your help. I’m not quite sure I understand your explanation. What I need is a macro that will find all INCLUDEPICTURE absolute paths in the document (there are hundreds) and convert them to relative paths.
This is the explanation I made for the users to fix the problem manually, but I would like to offer them a macro to save them all this work:
1. Press CTRL + A to highlight the whole document and then press SHIFT + F9 to toggle the field code display.
2. Check that the hyperlinks to pictures are relative, not absolute.
Example of an absolute path:
{ INCLUDEPICTURE “../../AppData/Roaming/Microsoft/Word/Pictures/Docking1.png” * MERGEFORMAT d }
Example of a relative path:
{ INCLUDEPICTURE “Pictures/Docking1.png” * MERGEFORMAT d }
3. Should you see absolute links, highlight the whole document again and press F9. Check if the absolute hyperlinks have been updated to relative hyperlinks. If this does not work, perform a Find and replace based on the following example:
Find what: ”../../AppData/Roaming/Microsoft/Word/Pictures
Replace with: ”Pictures
(So, in other words, there is no need to know the name of the picture. We just want to find and replace a small part of the path.)
4. Check again that all hyperlinks are correct.
5. Highlight the whole document and press F9 to toggle the field codes and display the images. Before updating the fields, Word will display the following dialog box:
(“Update Table of Contents” dialog box screenshot)
6. Select “Update page numbers only” and click “OK”.[/COLOR]
The pseudo code would be like this:
Find all fields that start with:
”../../AppData/Roaming/Microsoft/Word/Pictures
and replace them with:
”Pictures
So my question is… How should I twick your code in order to perform this operation in all the document?
And something else… Will the method work both for images that were inserted using “Link to file” and for images that were inserted using “Insert and link”?
Thanks a lot again,
Daniel
macropodHi Daniel,
What you’re calling a relative path would not be considered a valid path at all in most Word versions. The only one I know of that might recognise it is Word 2010; in all other versions, the absolute path is stored in the file’s metadata and would be used. Were that not so, simply copying the document and the folder of images it refers to to another location would not break the links. If you want true relative paths, the field code needs to have something that updates the metada. That’s what the field construction I referred to does.
Here’s one way of achieving the required field structure:
1. Press Alt-F9 to expose the field codes.
2. Execute a Find/Replace to delete all references to ‘../../AppData/Roaming/Microsoft/Word’
3. Insert a FILENAME field into the document, with the /p switch, then cut it from the document so that it’s on Word’s clipboard.
4. Execute a Find/Replace to replace all references to ‘/Pictures/’ with ‘^c\..\Pictures\’.
The technique works for images that were inserted using “Link to file” and for images that were inserted using “Insert and link”.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSbcnThank you very much for the input, Paul. But I’m afraid I’m still not capable of translating that into VBA code.
(Please also note that we do move the file with the Pictures folder together to a new location. Sometimes, the so called “relative path” links are preserved. Other times, the links get changed to the longer format (absolute path), and then the screenshots don’t appear in the document any longer; you only see the place holder and the caption below. So I guess this change from the original ‘/Pictures/’ to ‘../../AppData/Roaming/Microsoft/Word’ or anything else is caused because for some reason, in certain situations I cannot determine, Word decides to replace the so called “relative path” with an “absolute path” that I guess is stored in the metadata info.)
Daniel
macropodWSbcnOh, ok. So what is the purpose of
3. Insert a FILENAME field into the document, with the /p switch, then cut it from the document so that it’s on Word’s clipboard?
And why do I want it in the clipboard?
BTW: The reason why I wanted to offer a macro to do this is that not all users will be able to do it manually (at least corectly).
Thanks,
Daniel
macropodThe purpose is to get the FILENAME field into the clipboard so that it can be included in the Find/Replace expression (that’s what the ‘^c’ part of the Replace expression represents. This is not vba you’re working with but field coding, two fundamentally different concepts.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSbcnmacropod
WSdeltaops88I’m using microsoft word 2010, and I’m still unable to get this to work. I get an error that the path is incorrect (although I cannot see the path generated by the field, just an image placeholder). Do you have a simple working example (a word doc) that demonstrates the use of any one of these implementations?
Thanks!
macropodDo you have a simple working example (a word doc) that demonstrates the use of any one of these implementations?
See, for example: http://www.msofficeforums.com/word/11888-possible.html#post31405
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
macropodOrdinarily, Word inserts double-quotes around paths/filenames with spaces, and some fields have these regardless. What should work is adding some code to enforce the use of the double quotes. To do that, replace the existing ‘UpdateFields’ sub with:
Private Sub UpdateFields()
' This routine sets the new path for external links, pointing them to the current folder.
Dim oRng As Range, oFld As Field, i As Integer
Dim OldPath As String, NewPath As String, Parent As String, Child As String, StrTmp As String
' Set the new path.
' If your files are always in a folder whose path bracnhes off, one or more levels above the current
' folder, replace the second '0' on the next line with the number of levels above the current folder.
For i = 0 To UBound(Split(ActiveDocument.Path, "")) - 0
Parent = Parent & Split(ActiveDocument.Path, "")(i) & ""
Next i
' If your files are in a Child folder below the (new) parent folder, add the Child folder's
' path from the parent (minus the leading & trailing "" path separators) on the next line.
Child = ""
NewPath = Parent & Child
' Strip off any trailing path separators.
While Right(NewPath, 1) = ""
NewPath = Left(NewPath, Len(NewPath) - 1)
Wend
' Go through all story ranges in the document, including shapes, headers & footers.
With ThisDocument
For Each oRng In .StoryRanges
' Go through the fields in the story range.
For Each oFld In oRng.Fields
With oFld
' Skip over fields that don't have links to external files.
' A '.Linkformat' Property test should work, but is unreliable.
If .Type = wdFieldHyperlink Or .Type = wdFieldImport _
Or .Type = wdFieldInclude Or .Type = wdFieldIncludePicture _
Or .Type = wdFieldIncludeText Or .Type = wdFieldLink Or .Type = wdFieldRefDoc Then
' Get the old path. OldPath = .LinkFormat.SourcePath should work,
' but is unreliable as it sometimes returns part of the filename.
OldPath = GetPath(.LinkFormat.SourceFullName)
StrTmp = Replace(.LinkFormat.SourceFullName, "", "\")
If InStr(.Code.Text, " " & StrTmp & " ") > 0 Then
.Code.Text = Replace(.Code.Text, StrTmp, Chr(34) & StrTmp & Chr(34))
End If
' Replace the link to the external file if they differ.
If OldPath NewPath Then .LinkFormat.SourceFullName = _
Replace(.LinkFormat.SourceFullName, OldPath, NewPath)
End If
End With
Next oFld
Next oRng
End With
End Sub
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSjcvarneyPaul,
Thanks for the fix for folder names with spaces in them.
Another issue has just come up re: Sharepoint. One of the users wants to store and use the file within a Sharepoint project. If they run the files from within sharepoint the links are updated the first time, but since the path in Sharepoint uses HTTP:// format, the macro results in errors each successive time the file is opened (foward slashes vs. back slashes). I considered checking for the presense of “http” and then having an alternative GetPath to deal with that. Any thoughts on your end? I expect the code for NewPath will also need to be dealt with.
Regards,
Jeff
macropod
WStzankmaleHi all!
What I’m trying to do is to have a Word document embedded in an Excel workbook to work as a report of its “host” workbook.
I tried to use the proposed code, but the ActiveDocument.Path gives an empty string and the macro fails.
Furthermore, the {FILENAME /p} field in the document gives “Document in Book1”, no path, no nothing…
So, is there any way to obtain the path and name of the containing workbook in the Word doc, and use it in code?
Do you suggest an other way to achieve an “automated” Word report of an Excel workbook?
Thanks!
macropodWhy would you try to put such a field inside an embedded object? Embedded objects don’t have a path of their own – only the host file has one.
If you want to add the workbook name to a document embedded in the workbook, you would need to use automation to open the document and add the data to the embedded document (eg in the header or footer).
Either way, that is an entirely different issue from what this thread is about. If that’s what you want to do, please start a new thread for it.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSmbowserHi,
I’ve used the solution posted at http://windowssecrets.com/forums/showthread//102080-Word-Relative-Field-Paths-Discussion?p=778412#post778412 (the one posted above doesn’t seem to work for me for some reason).
It updates some of the links, the ones that are direct cell references or tables, but does nothing to the charts.
Is there any way of getting this to work with charts? (Using Office 2010)
WSmxpxsnobroHi there Macropod,
Having the same issue as relayed in #119, macro works great, but having an issue with it dropping the MERGEFORMAT parameter.
The issue seems to only manifest itself when I copy the folder containing the word doc and spreadsheet and paste it in an alernate location. I have tried both formatting options when pasting and linking to excel, keep source formatting as match detination table style. Seems to make no difference. The tables resizing themselves is the primary concern, it may or may not be macro related, but the common denominator is the MERGEFORMAT no longer being there. Ideas???
WSLonemascotHi apologies if this has been asked before but is there a workaround to linking to an Excel file on Onedrive?
Macropod’s code works beautifully on locally stored files but I can’t get it to work on the file stored on one drive.
When linking from the workbook this is the link path that is shown:
{ LINK Excel.SheetMacroEnabled.12 “file://d.docs.live.net/9661871e62308fe0/Documents/Hylands Homes/Quotes – HH/Estimates/_TEMPLATE_FOLDER/test.xlsm” “E Output!R9C2″ a t }
This is what I have tried to no avail:
{ LINK Excel.SheetMacroEnabled.12 ” D:OneDriveDocumentsHylands HomesQuote – HHEstimates_TEMPLATE_FOLDERtest.xlsm ” “E Output!R9C2″ a t }
{ LINK Excel.SheetMacroEnabled.12 ” D:\OneDrive\Documents\Hylands Homes\Quote – HH\Estimates\_TEMPLATE_FOLDER\test.xlsm ” “E Output!R9C2” a t } which is what the locally stored path looks like
Any help would be appreciated, even if it is just to say it has been covered and I’ll start trawling the last 16 pages to find it.
macropodWSmxpxsnobromacropodYou might try replacing the ‘UpdateFields’ routine with:
Private Sub UpdateFields()
' This routine sets the new path for external links, pointing them to the current folder.
Dim oRng As Range, oFld As Field, i As Integer
Dim OldPath As String, NewPath As String, Parent As String, Child As String
' Set the new path.
' If your files are always in a folder whose path bracnhes off, one or more levels above the current
' folder, replace the second '0' on the next line with the number of levels above the current folder.
For i = 0 To UBound(Split(ActiveDocument.Path, "")) - 0
Parent = Parent & Split(ActiveDocument.Path, "")(i) & ""
Next i
' If your files are in a Child folder below the (new) parent folder, add the Child folder's
' path from the parent (minus the leading & trailing "" path separators) on the next line.
Child = ""
NewPath = Parent & Child
' Strip off any trailing path separators.
While Right(NewPath, 1) = ""
NewPath = Left(NewPath, Len(NewPath) - 1)
Wend
' Go through all story ranges in the document, including shapes, headers & footers.
With ThisDocument
For Each oRng In .StoryRanges
' Go through the fields in the story range.
For Each oFld In oRng.Fields
With oFld
' Skip over fields that don't have links to external files.
If Not .LinkFormat Is Nothing Then
With .LinkFormat
OldPath = .SourcePath
' Replace the link to the external file if they differ.
If OldPath NewPath Then .SourceFullName = Replace(.SourceFullName, OldPath, NewPath)
End With
End If
End With
Next oFld
Next oRng
End With
End Sub
Caveat: At one time, Word had a bug that meant testing .LinkFormat and retrieving .SourcePath were both unreliable. I don’t know if that bug has been fixed and I have nothing to test it with.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Kayem68Hi Paul,
First off, thanks to you and anyone else that’s contributed toward this effort. You’re filling a much needed niche.
My aim is to use VBA in a Word document to, update upon opening, linked data from one or more spreadsheets after copying and moving the Word and Excel files to a new folder in same relative folder tree relationship as the original. Unless I’m waaay off base your code does exactly that. Great
My problem was originally that the code “seemingly” wouldn’t run at all. I say “seemingly” because I think it was actually running but finding no “valid” links there’s nothing to update so it seems to finish without changing anything.
And I think the code is not finding any “valid” links in the Word document because of the peculiar, to me anyway, way Word is changing the link layout between opening, updating, and saving the Word document.
The attached images should show what I’m experiencing…
When I open a Word document containing a link to a Spreadsheet, the link is formatted as such… which doesn’t look like a properly formatted link to me.
35112-Link-layout-upon-Opening-a-Saved-.docm
However, when I update the link the formatting changes to the following… which does look like a properly formatted link to me.
35113-Link-layout-after-Updating
You can see that the link formatting changes upon updating and saving.
Consequently when the document opens from it’s last save the link is not formatted accurately and the UpdateFields code runs but doesn’t change anything as it detects no “valid” links that it should act upon.
If I update the link so that the link formatting changes and then run the UpdateFields manually I can see that the code is attempting to re-write the path, but I get an infinite loop where the code continually attempts to re-write the same link but I think that’s further down the troubleshooting path.
Question: Is the link “reformatting” between opening, updating, and saving; normal and expected behavior for Office 2010?
macropodHi Kaye,
The first link image is indeed incorrect. A valid link has four parts –
1. The LINK descriptor and file format
2. The file’s full name
3. The addess within the file
4. formatting switches.
Conversely, the second link image is correct.
From what you describe, what is happening after updating is that parts 2 & 3 are being split into their correct components. On my system, a newly-created link is already correctly formatted. The only difference I can see is that I’m not using a network, but why linking across a network should cause a malformed link I don’t know.
Since you say that updating the link causes it to reformat itself correctly, the solution may be to issue an .Update command before & after the path updating. In other words:
If Not .LinkFormat Is Nothing Then
.Update
With .LinkFormat
OldPath = .SourcePath
' Replace the link to the external file if they differ.
If OldPath NewPath Then .SourceFullName = Replace(.SourceFullName, OldPath, NewPath)
End With
.Update
End If
Doubtless this change will make the code run somewhat slower.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSmbowsermacropodHow are the charts inserted? Are they inserted as fields, or as embedded objects with their own links back to the source data? Can you attach a document to a post with a representative chart? You do this via the paperclip symbol on the ‘Go Advanced’ tab.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSmbowserHow are the charts inserted? Are they inserted as fields, or as embedded objects with their own links back to the source data? Can you attach a document to a post with a representative chart? You do this via the paperclip symbol on the ‘Go Advanced’ tab.
They were pasted from the excel document using the paste option to link back to the excel document they relate to – ie F9 still refreshes their data from the workbook.
macropod
WSspikie123I know this problem has been adressed before but I suspect a different reason for this error.
Situation: Word document with relative path code as a module plus a linked Excel workbook.
If I copy the word and excel file to a new folder, and opening the Word file from it’s new location it throws an error: “cannot open two workbooks with the same name”.
If I rename the Excel file in the old location, the error does not appear. This suggests that Word opens both the old and the new excel file resulting in the Error.
I am sure that the code is not in the normal Template (and tehrefore executed twice).
Any suggestions on solutions?
macropodAs has been suggested before (see /showthread//183852-Word-Fields-and-Relative-Paths-to-External-Files-Problem?p=1099410&viewfull=1#post1099410) you should be able to resolve that by turning off the ‘update automatic links at open’ option (File|Options|Advanced>General).
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
macropodWSLonemascotIf you’re linking to a file stored on OneDrive, why would you need to make the path relative. More specifically, to what would it be relative?
Hi Macropod. The Excel and Word files are in the same “template” folder on my OneDrive. Each new job that comes in is added to my system by copy-pasting the template folder, renaming it as the job reference and then editing its contents to suit the job requirements. The excel workbook is an estimating spreadsheet which is linked by the word document.
The word document in the newly created copied folder still links to the Excel workbook in the template folder. Instead, I want it to link to the workbook that is sitting next to it in the copied folder hence the relative path rather than the dynamic path.
This all works as you’d expect when the files are stored conventionally on a local hard drive but the second you link to an excel file on OneDrive it swaps out C:/Onedrive/Documents etc to the https:// internet pathway instead. What I was trying to achieve was linking to the excel file using a conventional pathway rather than this web path that seems to break your code.
macropodTry replacing the UpdateFields macro with:
Private Sub UpdateFields()
' This routine sets the new path for external links, pointing them to the current folder.
Dim Rng As Range, Fld As Field, Shp As Shape, iShp As InlineShape, i As Long, NewPath As String
' Go through all story ranges in the document.
With ThisDocument
NewPath = .Path & ""
For Each Rng In .StoryRanges
' Go through the shapes in the story range.
For Each Shp In Rng.ShapeRange
With Shp
' Skip over shapes that don't have links to external files.
If Not .LinkFormat Is Nothing Then
With .LinkFormat
.SourceFullName = NewPath & .SourceName
On Error Resume Next
.AutoUpdate = False
On Error GoTo 0
End With
End If
End With
Next Shp
' Go through the inlineshapes in the story range.
For Each iShp In Rng.InlineShapes
With iShp
' Skip over inlineshapes that don't have links to external files.
If Not .LinkFormat Is Nothing Then
With .LinkFormat
.SourceFullName = NewPath & .SourceName
On Error Resume Next
.AutoUpdate = False
On Error GoTo 0
End With
End If
End With
Next iShp
' Go through the fields in the story range.
For Each Fld In Rng.Fields
With Fld
' Skip over fields that don't have links to external files.
If Not .LinkFormat Is Nothing Then
With .LinkFormat
.SourceFullName = NewPath & .SourceName
On Error Resume Next
.AutoUpdate = False
On Error GoTo 0
End With
End If
End With
Next Fld
Next Rng
.Save
End With
End Sub
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSLonemascotTry replacing the UpdateFields macro with:
Code:Private Sub UpdateFields() ' This routine sets the new path for external links, pointing them to the current folder. Dim Rng As Range, Fld As Field, Shp As Shape, iShp As InlineShape, i As Long, NewPath As String ' Go through all story ranges in the document. With ThisDocument NewPath = .Path & "" For Each Rng In .StoryRanges ' Go through the shapes in the story range. For Each Shp In Rng.ShapeRange With Shp ' Skip over shapes that don't have links to external files. If Not .LinkFormat Is Nothing Then With .LinkFormat .SourceFullName = NewPath & .SourceName On Error Resume Next .AutoUpdate = False On Error GoTo 0 [COLOR=#ff0000][/COLOR][COLOR=#ff0000] End If[/COLOR] End With End If End With Next Shp ' Go through the inlineshapes in the story range. For Each iShp In Rng.InlineShapes With iShp ' Skip over inlineshapes that don't have links to external files. If Not .LinkFormat Is Nothing Then With .LinkFormat .SourceFullName = NewPath & .SourceName On Error Resume Next .AutoUpdate = False On Error GoTo 0 [COLOR=#ff0000]End If[/COLOR] End With End If End With Next iShp ' Go through the fields in the story range. For Each Fld In Rng.Fields With Fld ' Skip over fields that don't have links to external files. If Not .LinkFormat Is Nothing Then With .LinkFormat [COLOR=#ff0000].SourceFullName = NewPath & .SourceName (runtime error 5360)[/COLOR] On Error Resume Next .AutoUpdate = False On Error GoTo 0 [COLOR=#ff0000]End If[/COLOR] End With End If End With Next Fld Next Rng .Save End With End Sub
Hi Paul
Thanks for having a look at this. I replace the Private Sub Updatefieds() code with your new code. The red highlights above were block compile errors so I removed those lines but then it got stuck on a runtime error 5360.
macropodWSLonemascotTry deleting the three problem ‘End If’ lines. Code updated
Hi Paul.
I had deleted those three lines previously which cleared those issues but the row:
.SourceFullName = NewPath & .SourceName
is causing problems.
Even if I remove all existing links and paste in a new link, without messing around with duplicating folders etc, the code runs into a problem on that line. I think somehow the NewPath reference isn’t correct or it’s changing the path format and is breaking the code. The path is showing as the https://d.docs.live.net/xxxxxxxxxxxxx onedrive location to the original linked excel file rather than the folder path to the current word file.
[Code]Private Sub UpdateFields()
‘ This routine sets the new path for external links, pointing them to the current folder.
Dim Rng As Range, Fld As Field, Shp As Shape, iShp As InlineShape, i As Long, NewPath As String
‘ Go through all story ranges in the document.
With ThisDocument
NewPath = .Path & “”
For Each Rng In .StoryRanges
‘ Go through the shapes in the story range.
For Each Shp In Rng.ShapeRange
With Shp
‘ Skip over shapes that don’t have links to external files.
If Not .LinkFormat Is Nothing Then
With .LinkFormat
.SourceFullName = NewPath & .SourceName
On Error Resume Next
.AutoUpdate = False
On Error GoTo 0
End With
End If
End With
Next Shp
‘ Go through the inlineshapes in the story range.
For Each iShp In Rng.InlineShapes
With iShp
‘ Skip over inlineshapes that don’t have links to external files.
If Not .LinkFormat Is Nothing Then
With .LinkFormat
.SourceFullName = NewPath & .SourceName
On Error Resume Next
.AutoUpdate = False
On Error GoTo 0
End With
End If
End With
Next iShp
‘ Go through the fields in the story range.
For Each Fld In Rng.Fields
With Fld
‘ Skip over fields that don’t have links to external files.
If Not .LinkFormat Is Nothing Then
With .LinkFormat
.SourceFullName = NewPath & .SourceName [The code is getting stuck here. NewPath displays the long https:// onedrive link. .SourceName displays the correct filename.]
On Error Resume Next
.AutoUpdate = False
On Error GoTo 0
End With
End If
End With
Next Fld
Next Rng
.Save
End With
End Sub[/code]
macropodWSLonemascotThat suggests the document is being opened from OneDrive, not from your local drive. Moreover, the code you’ve posted is missing the from:
NewPath = .Path & “”
hmm odd. I copied the code directly from VB and pasted here. Just checked code my end and it has the . I must have accidentally deleted it somehow.
Anyway, perhaps I miscommunicated what I originally wanted to achieve.
I don’t use onedrive via the website. I open files in the onedrive folder on my local hdd. I installed Onedrive to be on a D: partition of HDD. I have several workstations that need access to the same up to date content, hence why one drive is used.
With some programs if you link or hyperlink to onedrive you can do so with a local path. eg. D:/Onedrive/documents/hylands homes/quotes hh, etc etc. If the document is opened on another PC as long as onedrive is stored on D:/ then it’ll find the linked file.
What Word seems to do automatically is link to the online file using the internet path whether you copied the link from the file on D:/ or not.
This is what I want to achieve:
From my local Onedrive, I would like to be able to copy a template folder (which contains the linked Excel and Word files) and paste that folder somewhere else on my local Onedrive as a duplicate but renamed as the reference of a job. So, at this point the contents of the folders are identical but the folder names are different. The original linked files are still intact in their original location but I can now edit the duplicated Excel file which will then update the duplicated Word file which is within the same folder (not the original Word file). In effect, I would have lots of pasted folders each containing a linked Excel and Word file which are all full of different job info.
macropodWSLonemascotWhat does the macro return if you insert:
Msgbox NewPath
after:
NewPath = .Path & “”
Hi Paul, thanks for staying with me on this.
It returns the following:
https://d.docs.live.net/6961871e62308fe0/documents/Hylands%20Homes/Quotes%20HH/Estimates/%5eN%20HH18-TEST
that is the name of the duplicated folder and its path, albeit the internet path.
.SourceName is “Estimate.xlsm” which is the name of the excel file in the same.
Would it have anything to do with a # in my folder name?
WSLonemascotI removed the # but that doesn’t help.
When I show links on the document this is the link path that shows up.
LINK Excel.SheetMacroEnabled.12 “https://d.docs.live.net/6961871e62308fe0/Documents/Hylands Homes/Quotes – HH/Estimates/HH18-TEST/Estimate.xlsm” “E Output!R9C2” a t
so, they match except for the %20 instead of actual spaces.
WSLonemascotJust to eliminate any weird anomalies that may have been lurking in my files I have done the following:
Moved the folder to the first child level within Onedrive and removed all spaces and characters from folder names.
Created a new excel file with just 1 cell with data
Created a new word file, loaded on your code and pasted the link from excel via paste special/paste link/unformatted text
Run the macro
the same error gets stuck .SourceFullName = NewPath & .SourceName
macropodWSLonemascotIf the paths work without the %20, you might try:
Code:NewPath = Replace(.Path, "%20", " ") & ""
Still getting stuck on that row. Even a path without spaces to start with gets stuck. Even running the code before any folders have been moved breaks it.
.SourceFullName (displays original file path with spaces as actual spaces – correct) = NewPath (displays current document folder path – correct) & .SourceName (displays name of linked excel file)
That, on the face of it, should work. But it doesn’t.
There’s something wrong with that SourceFullName. If I paste a link and change the source cell data and update word with ctrl+A, F9 the data will update. If I run the macro it gets stuck on that same line. So at this stage, moving the folder isn’t even the problem. The code does not seem to like links to onedrive.
Interestingly, (and not really related to above) on a path WITH spaces, if I change the code around to insert the %20 instead ie.
NewPath = Replace(.Path, " ", "%20") & ""
Then you would expect .SourceFullName to display the path with these %20 within it but they don’t. VBA is swapping them out automatically. Even with NewPath = .path & “” it takes them out.
WSLonemascotFound this on another site. I don’t really understand the code but it seems to address the issue with Onedrive and perhaps could be incorporated into your code?
source https://social.msdn.microsoft.com/Forums/office/en-US/1331519b-1dd1-4aa0-8f4f-0453e1647f57/how-to-get-physical-path-instead-of-url-onedrive?forum=officegeneral
Private Function GetDocLocalPath(doc As Document) As String
'return the local path for doc, which is either already a local document or a document on OneDrive
Const strcOneDrivePart As String = "https://d.docs.live.net/"
Dim strRetVal As String, bytSlashPos As Byte
strRetVal = doc.Path & ""
If Left(LCase(doc.Path), Len(strcOneDrivePart)) = strcOneDrivePart Then 'yep, it's the OneDrive path
'locate and remove the "remote part"
bytSlashPos = InStr(Len(strcOneDrivePart) + 1, strRetVal, "/")
strRetVal = Mid(doc.Path, bytSlashPos)
'read the "local part" from the registry and concatenate
strRetVal = System.PrivateProfileString("", "HKEY_CURRENT_USEREnvironment", "OneDrive") & strRetVal
strRetVal = Replace(strRetVal, "/", "") & "" 'slashes in the right direction
strRetVal = Replace(strRetVal, "%20", " ") 'a space is a space once more
End If
GetDocLocalPath = strRetVal
End Function
macropodWSLonemascotTo use that Function, simply change:
NewPath = .Path & “”
to:
NewPath = GetDocLocalPath(ThisDocument)
Note that the backslashes are missing from the Function as posted.
Private Sub UpdateFields()
' This routine sets the new path for external links, pointing them to the current folder.
Dim Rng As Range, Fld As Field, Shp As Shape, iShp As InlineShape, i As Long, NewPath As String
' Go through all story ranges in the document.
With ThisDocument
NewPath = GetDocLocalPath(ThisDocument)
MsgBox NewPath
For Each Rng In .StoryRanges
' Go through the shapes in the story range.
For Each Shp In Rng.ShapeRange
With Shp
' Skip over shapes that don't have links to external files.
If Not .LinkFormat Is Nothing Then
With .LinkFormat
.SourceFullName = NewPath & .SourceName
On Error Resume Next
.AutoUpdate = False
On Error GoTo 0
End With
End If
End With
Next Shp
' Go through the inlineshapes in the story range.
For Each iShp In Rng.InlineShapes
With iShp
' Skip over inlineshapes that don't have links to external files.
If Not .LinkFormat Is Nothing Then
With .LinkFormat
.SourceFullName = NewPath & .SourceName
On Error Resume Next
.AutoUpdate = False
On Error GoTo 0
End With
End If
End With
Next iShp
' Go through the fields in the story range.
For Each Fld In Rng.Fields
With Fld
' Skip over fields that don't have links to external files.
If Not .LinkFormat Is Nothing Then
With .LinkFormat
.SourceFullName = NewPath & .SourceName
On Error Resume Next
.AutoUpdate = False
On Error GoTo 0
End With
End If
End With
Next Fld
Next Rng
.Save
End With
End Sub
Private Function GetDocLocalPath(doc As Document) As String
'return the local path for doc, which is either already a local document or a document on OneDrive
Const strcOneDrivePart As String = "https://d.docs.live.net/"
Dim strRetVal As String, bytSlashPos As Byte
strRetVal = doc.Path & ""
If Left(LCase(doc.Path), Len(strcOneDrivePart)) = strcOneDrivePart Then 'yep, it's the OneDrive path
'locate and remove the "remote part"
bytSlashPos = InStr(Len(strcOneDrivePart) + 1, strRetVal, "/")
strRetVal = Mid(doc.Path, bytSlashPos)
'read the "local part" from the registry and concatenate
strRetVal = System.PrivateProfileString("", "HKEY_CURRENT_USEREnvironment", "OneDrive") & strRetVal
strRetVal = Replace(strRetVal, "", "") & "" 'slashes in the right direction
strRetVal = Replace(strRetVal, "%20", " ") 'a space is a space once more
End If
GetDocLocalPath = strRetVal & ""
End Function
Still gets stuck on that same row
NewPath now equals C:OneDrive/Test which is what you’d expect for a local path. Can you mix and match slash directions?
Add to that the SourceName of Estimate.xlsm and SourceFullName should be equal to C:OneDrive/TestEstimate.xlsm. But it’s still reverting back to the https://d.docs.live.net pathway. Is the LinkFormat having an effect on this?
macropodWSLonemascotAs I said in my previous post
You haven’t correctly implemented:
strRetVal = Replace(strRetVal, “”, “”) & “” ‘slashes in the right direction
Check the code in your link.
strRetVal = Replace(strRetVal, "/", "") 'slashes in the right direction
That seems to sort the slashes as back slashes.
.SourceFullName is still taking the NewPath & .SourceName (which are both correct) and turning them into a weblink which is breaking the code.
Any other ideas.
macropod.SourceFullName is still taking the NewPath & .SourceName (which are both correct) and turning them into a weblink which is breaking the code.
I note that you’ve previously referred to the link paths variously being prefixed with ‘http:’ and ‘file:’. Does either of those work? Which form does the weblink which is breaking the code take?
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
WSLonemascotI note that you’ve previously referred to the link paths variously being prefixed with ‘http:’ and ‘file:’. Does either of those work? Which form does the weblink which is breaking the code take?
I can’t seem to replicate the situation where File: is shown as a link.
I feel like we need to go back to the drawing board. Just to recap what we know:
1) If the folders are saved to a local directory i.e. on the desktop. then copying the folder, renaming it, opening up the new word and excel files and running the original code works as it should by updating the links to the new folder path. Links in the word document look like this:
LINK Excel.SheetMacroEnabled.12 “C:\Users\jon\Desktop\TEST\DesktopEstimate.xlsm” “Sheet1!R6C1” a t
2) Moving the folder to a OneDrive directory and the code breaks on this line.
.SourceFullName = Replace(.SourceFullName, OldPath, NewPath)
Where the original SourceFullName is shown as C:UsersjonDesktopTESTDesktopEstimate.xlsm
OldPath is shown as C:UsersjonDesktopTEST
NewPath is shown as https://d.docs.live.net/6961871e62308fe0/TESTMovedandRenamed (notice last backslash instead of forward slash. That wouldn’t be causing the problem would it?)
3) Adding in the private function GetDocLocalPath and changing code
NewPath = .Path & ""
to:
NewPath = GetDocLocalPath(ThisDocument)[/code]
will allow NewPath to display in the format of D:onedriveTestMovedandRenamed
so, on the problem line it gets stuck on
.SourceFullName = Replace(.SourceFullName, OldPath, NewPath)
Where the original SourceFullName is shown as C:UsersjonDesktopTESTEstimate.xlsm
OldPath is shown as C:UsersjonDesktopTEST
NewPath is shown as D:onedriveTestMovedandRenamed
Combined together it should display D:onedriveTestMovedandRenamedEstimate.xlsm but breaks the code
I tried
.SourceNameFullName = NewPath & .SourceName
which should have the same effect as Replace(.SourceFullName, OldPath, NewPath) but it still breaks the code.
macropodWSLonemascotFrom what you’re saying, it seems one can’t make the LINK path work with OneDrive. In that case, nothing can be done to programmatically update the path to work with a OneDrive folder.
Ok thanks for trying anyway, appreciate the back and forth. I’ve got a friend that is an expert VBA coder. I’ll get in touch with him and if anyone can work it out, it’ll be him. Thanks again.
Allen Edwall
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Alex5723
Susan Bradley
Alex5723
Alex5723
crown
jkitc
Botswana12
Susan Bradley
Alex5723
Dave Easley
gtd12345
gtd12345
wadeer
CWBillow
Peter Deegan
Susan Bradley
Will Fastie
Will Fastie
Alex5723
Scott
Davidhs
Alex5723
MickIver
Alex5723
J9438
joep517
joep517
Cybertooth
dmt_3904
CWBillow
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.
Notifications