News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Word Relative Field Paths Discussion

    Posted on WSHansV Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function MS Word and word processing help Word Relative Field Paths Discussion

    Viewing 161 reply threads
    • Author
      Posts
      • #445195 Reply
        WSHansV
        AskWoody Lounger

        Hi 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.

      • #1079276 Reply
        macropod
        AskWoody_MVP

        Hi Hans,

        Thanks for the feedback. This is the first instance I’ve heard of INCLUDEPICTURE fields doing that. They behave as described in Word 2000 and Word 2007.

        Evidently there’s something odd about Word 2002 SP3.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1079282 Reply
          WSHansV
          AskWoody Lounger

          The macro you added does work in Word 2002 SP3 for INCLUDEPICTURE fields (but then, Word 2002 tends to change the path in an INCLUDEPICTURE field to relative at the drop of a hat…)

      • #1124031 Reply
        WSsilverback
        AskWoody Lounger

        Hello, 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

        • #1124033 Reply
          macropod
          AskWoody_MVP

          Hi Silverback,

          Are you sure you’ve put the macro in a module attached to the document you’re trying to update, and not its template or the Normal.dot template?

          Also, have you checked that your macro security level isn’t set too high?

          Cheers,
          Paul Edstein
          [Fmr MS MVP - Word]

          • #1124053 Reply
            WSsilverback
            AskWoody Lounger

            Many thanks to Paul and Hans thankyou
            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

        • #1124034 Reply
          WSHansV
          AskWoody Lounger

          It’s not your fault. AutoOpen doesn’t run when it’s declared as a private procedure. To correct this, change the line

          Private Sub AutoOpen()

          to

          Public Sub AutoOpen()

          • #1124037 Reply
            macropod
            AskWoody_MVP

            Thanks Hans,

            I wasn’t aware of that.

            Cheers,
            Paul Edstein
            [Fmr MS MVP - Word]

            • #1124039 Reply
              WSHansV
              AskWoody Lounger

              To be frank, I wasn’t aware of it either (I had tested your macros by running them manually), but it was the only thing that struck me as a possible explanation for AutoOpen not running, and it turned out to be so.

      • #1127954 Reply
        WSgoaway
        AskWoody Lounger

        Hi Macropod

        Will your addin accept 2007 file extensions now – the version I currently have does not?

        • #1128064 Reply
          macropod
          AskWoody_MVP

          Hi 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]

      • #1128066 Reply
        WSgoaway
        AskWoody Lounger

        Hi,
        I was just reading the text in a previous version that said this only works with doc or dot files – so I was assuming it didn’t like 4 characters .dotx etc in 2007. Thought I’d ask you it is much quicker.

        Thanks

        • #1128096 Reply
          macropod
          AskWoody_MVP

          Hi jen,

          I’m not aware of anything indicating [indent]


          this only works with doc or dot files


          [/indent]

          Cheers,
          Paul Edstein
          [Fmr MS MVP - Word]

      • #1128098 Reply
        WSgoaway
        AskWoody Lounger

        Hi Macropod

        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.”

        Obviously it doesn’t matter then. Thank you.

        • #1128116 Reply
          macropod
          AskWoody_MVP

          Hi 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]

        • #1308406 Reply
          WSbrokenlink
          AskWoody Lounger

          Hi 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

      • #1129904 Reply
        WSburtinh
        AskWoody Lounger

        Hi, 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

        • #1129974 Reply
          macropod
          AskWoody_MVP

          Hi 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]

          • #1130041 Reply
            WSburtinh
            AskWoody Lounger

            Sorry, 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

            • #1130135 Reply
              macropod
              AskWoody_MVP

              Hi 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]

              • #1130613 Reply
                WSburtinh
                AskWoody Lounger

                Hi again, Paul.

                I took the liberty of discussing the operation of your macro with a friend who is well-versed in VB. He modified three lines…

                Dim lPos As Long

                lPos = InStrRev(OldPath, “”)
                OldPath = Left(OldPath, lPos – 1)

                …and the macro is working for me now.

                -Best

              • #1131124 Reply
                macropod
                AskWoody_MVP

                Hi Buddy,

                I’ll take a look at this – I still can’t see how the original code could have had the effects you reported or how change you’ve made could remedy that. Ultimnately, I’d prefer to provide a single solution for all Word versions.

                Cheers,
                Paul Edstein
                [Fmr MS MVP - Word]

      • #1144800 Reply
        WSgiffordpinchot
        AskWoody Lounger

        Hello,

        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!

        • #1144861 Reply
          macropod
          AskWoody_MVP

          Hi,

          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]

          • #1144932 Reply
            WSgiffordpinchot
            AskWoody Lounger

            Thanks, 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.

            • #1144934 Reply
              WSHansV
              AskWoody Lounger

              Our 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)

              • #1144953 Reply
                WSgiffordpinchot
                AskWoody Lounger

                Cool.
                okay, here is my code:

                { INCLUDETEXT “{ MERGEFIELD DBPath }Templates{IF {MERGEFIELD BillUnitTypeID }= 1 “Invoices_Letters_Acres.docx” {IF {MERGEFIELD BillUnitTypeID }= 2 “Invoices_Letters_Tons.docx” “Invoices_Letters_Static.docx”}}}

              • #1145038 Reply
                macropod
                AskWoody_MVP

                Hi,

                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]

              • #1145045 Reply
                WSgiffordpinchot
                AskWoody Lounger

                HUGE 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…

              • #1145122 Reply
                WSjscher2000
                AskWoody Lounger

                Word 2007 adds “tabs” to some fields. That one is a DATE field. Are you saying that the merge does not carry over the DATE field to the new document? Or that it does not convert to the textual result (today’s date)?

              • #1145191 Reply
                macropod
                AskWoody_MVP

                Hi gifford,

                FWIW, I think you’d probably get better results if you changed the field type from DATE to CREATEDATE. Otherwise, the dates are liable to change whenever you open the merged document.

                Cheers,
                Paul Edstein
                [Fmr MS MVP - Word]

              • #1145332 Reply
                WSgiffordpinchot
                AskWoody Lounger

                Thanks Paul and jscher for all the help. Everything is working that way I’d like it now and I’ve put the project to bed.

                cheers!

      • #1145631 Reply
        WSgecko
        AskWoody Lounger

        Hello. 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.

        • #1145663 Reply
          macropod
          AskWoody_MVP

          Hi 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]

          • #1145697 Reply
            WSgecko
            AskWoody Lounger

            Many 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

            • #1145832 Reply
              macropod
              AskWoody_MVP

              Hi 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]

      • #1146650 Reply
        WSjayawanth
        AskWoody Lounger

        Was 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

        • #1148100 Reply
          macropod
          AskWoody_MVP

          Hi 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]

          • #1148120 Reply
            WSjayawanth
            AskWoody Lounger

            Thanks 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

      • #1153374 Reply
        WSTheGoodOmen
        AskWoody Lounger

        I 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.

        • #1153379 Reply
          WSjscher2000
          AskWoody Lounger

          I have two issues

          Sorry, I had split your post off of this thread into its own thread over here: #761784. Would you mind continuing the discussion over there?

      • #1169718 Reply
        WSrussdw
        AskWoody Lounger

        I 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

        • #1169746 Reply
          macropod
          AskWoody_MVP

          I 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]

      • #1169783 Reply
        WSrussdw
        AskWoody Lounger

        Macropod, 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

        • #1169815 Reply
          macropod
          AskWoody_MVP

          Macropod, 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]

      • #1169878 Reply
        WSrussdw
        AskWoody Lounger

        Paul,

        That modification did the trick! Thanks for your help with this. It will be nice knowing this macro is silently corralling external paths that may be attempting to run astray.

        -Russ

      • #1191251 Reply
        WServoneal
        AskWoody Lounger

        Hello,

        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.

      • #1191257 Reply
        WServoneal
        AskWoody Lounger

        As 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.

      • #1191379 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1191430 Reply
        WServoneal
        AskWoody Lounger

        Thank 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”

      • #1191544 Reply
        macropod
        AskWoody_MVP

        Hi Ervin,

        Unless you save the file to a new location and open it from there and then inspect the field code, you shouldn’t see any change.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1191570 Reply
        WServoneal
        AskWoody Lounger

        Macropod,

        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,

      • #1191609 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1192806 Reply
        WServoneal
        AskWoody Lounger

        Macropod,

        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!

      • #1193561 Reply
        WServoneal
        AskWoody Lounger

        Macropod,

        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,

      • #1193571 Reply
        macropod
        AskWoody_MVP

        Hi Ervin,

        I am unable to replicate that behaviour.

        I downloaded your attachments, opened the Word document (with the field code display toggled on) and, once the macro had run, the updated link was correct for my system. No ‘Undo’ required.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1193779 Reply
        WServoneal
        AskWoody Lounger

        Hi 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?

      • #1193801 Reply
        macropod
        AskWoody_MVP

        Hi Ervin,

        I am unable to replicate that behaviour with either .doc or .docm files. Are you using any document management software or other Add-ins? Is your copy of Word 2007 fully updated?

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1194217 Reply
          WSababenchrist
          AskWoody Lounger

          Hi 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

      • #1194235 Reply
        macropod
        AskWoody_MVP

        Hi Gary,

        In my experience, the relative paths work fine in Vista and all previous versions, and I see no reason why they’d behave any differently under Windows 7 (which I haven’t used).

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1194282 Reply
          WSababenchrist
          AskWoody Lounger

          Thank you macropod for your assistance, most greatly appreciated as always.
          I got my fingers crossed for Windows 7, but I’m confident it’ll work.

      • #1233806 Reply
        WSNoche
        AskWoody Lounger

        Hi,

        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?

        • #1233834 Reply
          WSjscher2000
          AskWoody Lounger

          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.

          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:

          Code:
          If Not .LinkFormat Is Nothing Then _

          (The trailing underscore is the continuation character: it runs the two lines together as one.)

          • #1233871 Reply
            WSNoche
            AskWoody Lounger

            I’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.

            • #1237032 Reply
              macropod
              AskWoody_MVP

              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.

              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]

      • #1237265 Reply
        WSjedel
        AskWoody Lounger

        Thanks 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!

      • #1237290 Reply
        macropod
        AskWoody_MVP

        Hi 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]

        • #1237295 Reply
          WSjedel
          AskWoody Lounger

          Hi 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 🙂

      • #1240065 Reply
        WSjedel
        AskWoody Lounger

        This 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

        • #1240770 Reply
          macropod
          AskWoody_MVP

          This 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]

          • #1241957 Reply
            WSjedel
            AskWoody Lounger

            Hi 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.

      • #1242442 Reply
        macropod
        AskWoody_MVP

        Hi James,

        The fact that the path is fully qualified doesn’t change how Excel handles it. Move the files to a new folder and you should see the path change.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1253113 Reply
        WSrush-rs09
        AskWoody Lounger

        Hi 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

      • #1253230 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1253233 Reply
        WSrush-rs09
        AskWoody Lounger

        Hi 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

      • #1253234 Reply
        WSrush-rs09
        AskWoody Lounger

        Hi Macropod,

        Here are the files that i used for testing the macro

        Thanks
        Rakesh

      • #1253427 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1254920 Reply
        WSyackbo
        AskWoody Lounger

        Hi 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.

      • #1254955 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1254964 Reply
        WSyackbo
        AskWoody Lounger

        Thanks 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.

      • #1255049 Reply
        macropod
        AskWoody_MVP

        Hi Brent,

        I you could attach an extract from the document containing the problem headers, I could take a look at it for you.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1255366 Reply
        WSyackbo
        AskWoody Lounger

        Thank 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.

      • #1255492 Reply
        WSyackbo
        AskWoody Lounger

        Hi 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!

        Code:
        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
        • #1363940 Reply
          WSwaldguy
          AskWoody Lounger

          Hi 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.

      • #1255664 Reply
        macropod
        AskWoody_MVP

        Hi Brent,

        The code modifications you posted show that you weren’t using the latest iteration – which I’ve now made further update to, so that it correctly updates linked shapes (eg Excel worksheets) in headers and footers.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1256281 Reply
        WSJoepi5
        AskWoody Lounger

        Hello,
        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.

      • #1256339 Reply
        macropod
        AskWoody_MVP

        Hi Bas,

        It would be helpful if you could upload a Word document with one or more of the links you’re trying to update, together with whatever code you’re using, plus ‘sanitized’ copies of the corresponding source files for the links.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1256392 Reply
        WSJoepi5
        AskWoody Lounger

        I 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?)

      • #1256394 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1256401 Reply
        WSJoepi5
        AskWoody Lounger

        hi 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

      • #1256403 Reply
        macropod
        AskWoody_MVP

        Hi Bas,

        Did you try it with the updated document attached to my post? With that document, the field path automatically updates to point to whatever folder the document is opened from.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1256404 Reply
        WSJoepi5
        AskWoody Lounger

        hi macropod,

        Yes I have tried that, and it worked, but for the future, how was this different from my approach.

        • #1256530 Reply
          macropod
          AskWoody_MVP

          Hi 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]

      • #1256406 Reply
        macropod
        AskWoody_MVP

        Hi Bas,

        I don’t know how that differs from your approach, since I haven’t seen a document of your’s with the macro implemented in it. As I said previously, there was no code in the document you posted – and maybe that’s the answer.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1256408 Reply
        WSJoepi5
        AskWoody Lounger

        hi macropod,

        How can I implement the code into my file properly?
        Did your work when you copied them both to another location?

      • #1256455 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1256472 Reply
        WSJoepi5
        AskWoody Lounger

        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.

        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.

      • #1256484 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1256509 Reply
        WSJoepi5
        AskWoody Lounger

        I knew that, so I saved my templates (just a term for the clean empty files) as .doc with activted macro files.
        And then this problem occurs. and I can’t find a solution for this problem.

        perhaps you know it?

      • #1256512 Reply
        macropod
        AskWoody_MVP

        Hi 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.

        Code:
        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]

      • #1256513 Reply
        WSJoepi5
        AskWoody Lounger

        hi,

        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.

      • #1256531 Reply
        WSJoepi5
        AskWoody Lounger

        Hi 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.

        • #1256532 Reply
          macropod
          AskWoody_MVP

          Hi 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]

      • #1256533 Reply
        WSJoepi5
        AskWoody Lounger

        Hi 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 = “”

        • #1256536 Reply
          macropod
          AskWoody_MVP

          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 = “”[/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]

      • #1256537 Reply
        WSJoepi5
        AskWoody Lounger

        Hi macropod,

        Is the module in the correct place?
        or does it need to be somewhere else?

      • #1256588 Reply
        WSJoepi5
        AskWoody Lounger

        Hi 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.

      • #1256613 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1257009 Reply
        WSalexburn
        AskWoody Lounger

        Hi,

        I am having some problems implementing your macro.

        I have a word document with several LINKs to external excel ranges as follows:-

        Code:
        { 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

      • #1257011 Reply
        WSalexburn
        AskWoody Lounger

        Just for reference, tried breaking the code down and running line by line.
        The code seems to be stuck at the following:-

        Code:
        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

      • #1257140 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1257246 Reply
        WSalexburn
        AskWoody Lounger

        So 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

      • #1257276 Reply
        macropod
        AskWoody_MVP

        Hi 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]

        • #1302223 Reply
          WSbustamantejb
          AskWoody Lounger

          Hi 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.

          • #1302227 Reply
            macropod
            AskWoody_MVP

            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.

            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]

      • #1260082 Reply
        WSChris Scott
        AskWoody Lounger

        Hi 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.

      • #1260123 Reply
        macropod
        AskWoody_MVP

        Hi 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]

        • #1260206 Reply
          WSChris Scott
          AskWoody Lounger

          Hi 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!

      • #1260246 Reply
        macropod
        AskWoody_MVP

        Hi 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?

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1260256 Reply
          WSChris Scott
          AskWoody Lounger

          Hi 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

      • #1260347 Reply
        macropod
        AskWoody_MVP

        Hi Chris,

        Does your existing HYPERLINK field still have the file path?

        Using the MsgBox, what do you get with:
        MsgBox OldPath & vbCr & NewPath & vbCr & .LinkFormat.SourceFullName

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1260351 Reply
        WSChris Scott
        AskWoody Lounger

        Hi,

        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

        • #1260678 Reply
          macropod
          AskWoody_MVP

          Hi,

          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]

      • #1260615 Reply
        WSjerry393
        AskWoody Lounger

        Hi 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?

        • #1260680 Reply
          macropod
          AskWoody_MVP

          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:

          { 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]

          • #1264326 Reply
            WSSimonB1978
            AskWoody Lounger

            Hi,

            There seems to be a problem with the following statement:

            Code:
            .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)

            • #1264338 Reply
              macropod
              AskWoody_MVP

              Hi 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]

      • #1264393 Reply
        WSSimonB1978
        AskWoody Lounger

        Hi 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

      • #1264711 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1264865 Reply
        WSSimonB1978
        AskWoody Lounger

        Hi 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

      • #1264868 Reply
        WSSimonB1978
        AskWoody Lounger

        Hi Paul,

        Just re-did my Excel file and it works… Don’t know what was the problem though…

        Thanks,

        Simon

      • #1269864 Reply
        WSlobsterman
        AskWoody Lounger

        Simon,
        I’m having similar issues and am wondering what you mean by “re-did my Excel file”? My Excel file is pretty hefty and can’t be recreated easily. Is there a certain change that you think may have done the trick?
        Thanks
        Ben

        • #1269866 Reply
          WSSimonB1978
          AskWoody Lounger

          Mine wasn’t very complicated, 2 sheets.

          Copy/paste everything in a new workbook and it worked. I honestly cannot explain…

          Hope that works for you!

          Simon B

      • #1273925 Reply
        WSslieber
        AskWoody Lounger

        Hi Macropod,

        I am interested in your code for relative links – sounds like exactly what I need, but I dont see it posted on the thread. Is there somewhere else that it is posted?

        Thanks!
        Suzanne

      • #1273967 Reply
        macropod
        AskWoody_MVP

        Hi Suzanne,

        In the migration to the new lounge software, somehow the post order got mixed up. The one you’re after is #8 on the first page in this thread.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1280642 Reply
          WSLeilani
          AskWoody Lounger

          Hi 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

          Attachments:
          • #1280689 Reply
            macropod
            AskWoody_MVP

            Hi Leilani,

            The updating doesn’t work because you’ve changed the AutoOpen macro’s name to AutoOpenx …

            Cheers,
            Paul Edstein
            [Fmr MS MVP - Word]

            • #1280724 Reply
              WSLeilani
              AskWoody Lounger

              Paul,

              I changed the macro name so that I could run the macro manually and use debug to see what was happening. I originally had it as AutoOpen and got the bad results.

              Leilani

      • #1280760 Reply
        macropod
        AskWoody_MVP

        Hi Leilani,

        The code works fine for me – with the documents you posted.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1280764 Reply
          WSLeilani
          AskWoody Lounger

          We 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

      • #1282460 Reply
        WSDiesel94
        AskWoody Lounger

        Thank 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!

      • #1282478 Reply
        macropod
        AskWoody_MVP

        Hi 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]

        • #1301653 Reply
          WSelmsy2424
          AskWoody Lounger

          I 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.

      • #1302028 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1308644 Reply
        macropod
        AskWoody_MVP

        Hi 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]

        • #1309030 Reply
          WSelmsy2424
          AskWoody Lounger

          Thank 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.

          Attachments:
      • #1309153 Reply
        macropod
        AskWoody_MVP

        Hi 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]

        • #1313663 Reply
          WSloftymeat
          AskWoody Lounger

          Hi 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

          • #1313712 Reply
            macropod
            AskWoody_MVP

            Hi lofty,

            Your problem description suggests there’s a timing issue on your system. This isn’t something that’s been reported before. If you’re working on a network, perhaps its a network performance issue.

            Cheers,
            Paul Edstein
            [Fmr MS MVP - Word]

            • #1314046 Reply
              WSknigget
              AskWoody Lounger

              Hi, 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)?

              • #1342633 Reply
                WSjfmiller
                AskWoody Lounger

                I 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:

                  [*]INCLUDEPICTURE is the Word field code.
                  [*]10100824_Pictures is the name of the folder that contains PNG files of screen captures of entire software screens.
                  [*]10100824_XXXX is the file name of the PNG with the software screen that you want to display in the Word document.
                  [*]

                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:

                  [*]INCLUDEPICTURE is the Word field code.
                  [*]10100824_Pictures is the name of the folder that contains a folder named Button_Images.
                  [*]The Button_Images folder contains PNG files with images of software control buttons.
                  [*]ZZZZ_ZZZZ.png is the file name of the PNG file with the image of the software control button that you want to display in the Word document.
      • #1314208 Reply
        macropod
        AskWoody_MVP

        Hi knigget,

        In both cases, it’s the file(s) your Word document is linked to (eg an Excel workbook containing the data).

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1337613 Reply
        WSrwhitmore
        AskWoody Lounger

        I tried this macro but got the following error: “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). Any suggestions?

      • #1337615 Reply
        WSrwhitmore
        AskWoody Lounger

        I tried this macro but got the following error: “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). Any suggestions?

      • #1337617 Reply
        macropod
        AskWoody_MVP

        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?

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1337825 Reply
          WSrwhitmore
          AskWoody Lounger

          Yes, 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?

      • #1337840 Reply
        macropod
        AskWoody_MVP

        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 _

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1402659 Reply
          WSmqdias
          AskWoody Lounger

          Deat 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

      • #1342655 Reply
        macropod
        AskWoody_MVP

        Hi jfmiller,

        That approach works with the docx format only, in Word 2007 & later.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1342698 Reply
        WSChrisA
        AskWoody Lounger

        Hi

        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

      • #1342829 Reply
        macropod
        AskWoody_MVP

        Hi jfmiller,

        The whole point of this thread is that the approach you outlined is not reliable with Word 2003 & earlier – or doc files in general.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1343481 Reply
        WSChrisA
        AskWoody Lounger

        Hi 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

      • #1343492 Reply
        macropod
        AskWoody_MVP

        If you are using the macro as intended, the filepaths in the fields will change when the actual paths change; otherwise, I’d expect to see the original paths.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1343527 Reply
        WSChrisA
        AskWoody Lounger

        Hi 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?

      • #1343530 Reply
        macropod
        AskWoody_MVP

        I 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]

      • #1343537 Reply
        WSChrisA
        AskWoody Lounger

        Hi 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

      • #1343542 Reply
        macropod
        AskWoody_MVP

        That’s what the code supports, though it’s designed for use in a document, not a template.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1343547 Reply
        WSChrisA
        AskWoody Lounger

        Hi Paul

        What i am finding though is when i move the word document it is still linked to the original excel sheet and not the new copy. Can i change the link to divorce it from its original excel file?

        Thanks

        Chris

      • #1343553 Reply
        macropod
        AskWoody_MVP

        Have 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]

      • #1343629 Reply
        WSChrisA
        AskWoody Lounger

        Hi 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

      • #1343671 Reply
        macropod
        AskWoody_MVP

        That really does suggest you either haven’t installed the macro correctly, or that your security settings aren’t sufficient to allow it to run. I can assure you (as could the many others who have used it) that the macro does work.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1345031 Reply
        WSChrisA
        AskWoody Lounger

        Hi 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

      • #1345106 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1345125 Reply
        WSChrisA
        AskWoody Lounger

        Hi 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

      • #1345127 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1345128 Reply
        WSChrisA
        AskWoody Lounger

        Hi 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

      • #1345132 Reply
        macropod
        AskWoody_MVP

        hi Chris,

        In the code you’ll see the line:
        Child = “”
        Given what you’ve written, for your purposes this becomes:
        Child = “Audits”
        It’s that simple.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1345198 Reply
        WSChrisA
        AskWoody Lounger

        Hi Paul

        I will give that a go. I just moved the files over to another computer via a stick running Word 2010 and the links updated perfectly to the new clones, is there something clever in 2010?

        Thanks

        Chris

      • #1345201 Reply
        macropod
        AskWoody_MVP

        There’s nothing special about Office 2010 in this regard. The same solution works for all Word versions to date.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1345222 Reply
        WSChrisA
        AskWoody Lounger

        I think i know the answer, the security on the other computer would let the macro run as intended.

        Thanks

        Chris

      • #1345784 Reply
        WSaJacom
        AskWoody Lounger

        Hi.

        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?

        31769-231770-1

        Thanks

        Attachments:
      • #1345791 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1346887 Reply
        WSaaljo
        AskWoody Lounger

        Hi 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).

      • #1346926 Reply
        macropod
        AskWoody_MVP

        Hi aaljo,

        From what you’ve described, if you add the macro solution to each of the Word documents in the way described, they should from then on maintain their links to their Excel workbooks,

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1348861 Reply
          WSjcvarney
          AskWoody Lounger

          I 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?

      • #1348865 Reply
        macropod
        AskWoody_MVP

        That’s not something I’ve come across before. What happens if you press Ctrl-A, F9? Do the fields then refresh correctly?

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1355575 Reply
          WSsoapyx
          AskWoody Lounger

          Hi,

          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!

        • #1375874 Reply
          WSjcvarney
          AskWoody Lounger

          That’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,

      • #1355708 Reply
        macropod
        AskWoody_MVP

        Hi soapyx,

        That suggets you already had the Excel file open and that is preventing Word from updating the links.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1355827 Reply
          WSsoapyx
          AskWoody Lounger

          Yes, 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?

      • #1355829 Reply
        macropod
        AskWoody_MVP

        Hi 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]

        • #1358004 Reply
          WSCheeSiong
          AskWoody Lounger

          Hi 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.

      • #1358064 Reply
        macropod
        AskWoody_MVP

        Setting ‘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]

        • #1358106 Reply
          WSCheeSiong
          AskWoody Lounger

          hi 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?

      • #1358113 Reply
        macropod
        AskWoody_MVP

        Given 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]

      • #1358114 Reply
        WSCheeSiong
        AskWoody Lounger

        The Mod I made was to add “.Update” in the UpdateFields() subroutine, After replacing old path with new path.
        The line in your code is “.Saved = True” within the “With ActiveDocument”
        I have tried adding “ActiveDocument.Saved = True” right before the End Sub of AutoOpen().

        • #1358129 Reply
          macropod
          AskWoody_MVP

          The 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]

      • #1358351 Reply
        WSCheeSiong
        AskWoody Lounger

        Hi 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?

      • #1358374 Reply
        WSCheeSiong
        AskWoody Lounger

        Had 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.

      • #1358376 Reply
        macropod
        AskWoody_MVP

        Seems 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]

      • #1358378 Reply
        WSCheeSiong
        AskWoody Lounger

        Hi 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…..

      • #1358381 Reply
        macropod
        AskWoody_MVP

        In 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:

        Code:
        Private Sub Document_Open()
        ThisDocument.Fields.Update
        ThisDocument.Saved = True
        End Sub

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1359104 Reply
        WSCheeSiong
        AskWoody Lounger

        Hi 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.

      • #1359106 Reply
        macropod
        AskWoody_MVP

        OK, then rather than using a fixed-time loop, you might try something along the lines of:

        Code:
        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]

      • #1359623 Reply
        WSbcn
        AskWoody Lounger

        Hi 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

      • #1359824 Reply
        macropod
        AskWoody_MVP

        Hi 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]

      • #1359876 Reply
        WSbcn
        AskWoody Lounger

        Hi 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

      • #1359880 Reply
        macropod
        AskWoody_MVP

        Hi 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]

        • #1359892 Reply
          WSbcn
          AskWoody Lounger

          Thank 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

      • #1359910 Reply
        macropod
        AskWoody_MVP

        What vba code? This is just a couple of simple Find/Replace operations.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1359927 Reply
          WSbcn
          AskWoody Lounger

          Oh, 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

      • #1359929 Reply
        macropod
        AskWoody_MVP

        The 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]

      • #1359944 Reply
        WSbcn
        AskWoody Lounger

        Thank you, Paul. I know they are two different concepts, but this field coding process cannot be coded with vba so that the user can execute it with a mouse click?

        Daniel

      • #1359950 Reply
        macropod
        AskWoody_MVP

        Somehow I think you still haven’t grasped the concept. There is no need for anything to click to execute as the updating is entirely automatic.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1366864 Reply
        WSdeltaops88
        AskWoody Lounger

        I’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!

      • #1375879 Reply
        macropod
        AskWoody_MVP

        Ordinarily, 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:

        Code:
        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]

        • #1377671 Reply
          WSjcvarney
          AskWoody Lounger

          Paul,

          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

      • #1377723 Reply
        macropod
        AskWoody_MVP

        Hi Jeff,

        I’ve never used SharePoint, so I don’t really know what it requires of field codes. What does a valid field code for SharePoint look like compared to an ordinary one?

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1393497 Reply
          WStzankmale
          AskWoody Lounger

          Hi 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!

      • #1393531 Reply
        macropod
        AskWoody_MVP

        Why 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]

      • #1405866 Reply
        WSmbowser
        AskWoody Lounger

        Hi,

        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)

      • #1409302 Reply
        WSmxpxsnobro
        AskWoody Lounger

        Hi 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???

        • #1598091 Reply
          WSLonemascot
          AskWoody Lounger

          Hi 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.

      • #1414582 Reply
        macropod
        AskWoody_MVP

        Hi all,

        I’ve been OS for 3½ months, hence the delay in replying. Have you resolved the issue, or do you still need help?

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1414819 Reply
          WSmxpxsnobro
          AskWoody Lounger

          I could still use some help. When I first posted I was using 2007. Now I have a new machine and am running 2013 and still have the same issue

      • #1415293 Reply
        macropod
        AskWoody_MVP

        You might try replacing the ‘UpdateFields’ routine with:

        Code:
        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]

      • #1416156 Reply
        WSKayem68
        AskWoody Lounger

        Hi 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?

        Attachments:
        • #1416472 Reply
          macropod
          AskWoody_MVP

          Hi 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:

          Code:
                    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]

          • #1425024 Reply
            WSmbowser
            AskWoody Lounger

            Hi,

            I’m still having trouble with the code whereby it will update the source of the direct cell references and tables in Word, but not the pie charts, etc.

            Is there any solution to this?

            Thanks

      • #1425028 Reply
        macropod
        AskWoody_MVP

        How 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]

        • #1425295 Reply
          WSmbowser
          AskWoody Lounger

          How 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.

      • #1425378 Reply
        macropod
        AskWoody_MVP

        OK, but a document demonstrating the issue as requested (so I can examine the field coding, etc), would be helpful.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1598033 Reply
          WSspikie123
          AskWoody Lounger

          I 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?

      • #1598104 Reply
        macropod
        AskWoody_MVP

        If 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?

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1598105 Reply
          WSLonemascot
          AskWoody Lounger

          If 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.

      • #1598114 Reply
        macropod
        AskWoody_MVP

        Try 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
                  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]

        • #1598115 Reply
          WSLonemascot
          AskWoody Lounger

          Try 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.

      • #1598126 Reply
        macropod
        AskWoody_MVP

        Try deleting the three problem ‘End If’ lines. Code updated

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1598127 Reply
          WSLonemascot
          AskWoody Lounger

          Try 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]

      • #1598128 Reply
        macropod
        AskWoody_MVP

        That 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 & “”

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1598129 Reply
          WSLonemascot
          AskWoody Lounger

          That 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.

      • #1598130 Reply
        macropod
        AskWoody_MVP

        What does the macro return if you insert:
        Msgbox NewPath
        after:
        NewPath = .Path & “”

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

      • #1598136 Reply
        WSLonemascot
        AskWoody Lounger

        I 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.

      • #1598137 Reply
        WSLonemascot
        AskWoody Lounger

        Just 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

      • #1598139 Reply
        macropod
        AskWoody_MVP

        If the paths work without the %20, you might try:

        Code:
        NewPath = Replace(.Path, "%20", " ") & "" 

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1598140 Reply
          WSLonemascot
          AskWoody Lounger

          If 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.

          Code:
          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.

      • #1598141 Reply
        WSLonemascot
        AskWoody Lounger

        Found 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

        Code:
        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
        
      • #1598142 Reply
        macropod
        AskWoody_MVP

        To use that Function, simply change:
        NewPath = .Path & “”
        to:
        NewPath = GetDocLocalPath(ThisDocument)
        Note that the backslashes are missing from the Function as posted.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1598145 Reply
          WSLonemascot
          AskWoody Lounger

          To use that Function, simply change:
          NewPath = .Path & “”
          to:
          NewPath = GetDocLocalPath(ThisDocument)
          Note that the backslashes are missing from the Function as posted.

          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 = 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?

        • #1598146 Reply
          macropod
          AskWoody_MVP

          As I said in my previous post

          Note that the backslashes are missing from the Function as posted.

          You haven’t correctly implemented:
          strRetVal = Replace(strRetVal, “”, “”) & “” ‘slashes in the right direction
          Check the code in your link.

          Cheers,
          Paul Edstein
          [Fmr MS MVP - Word]

          • #1598147 Reply
            WSLonemascot
            AskWoody Lounger

            As 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.

            Code:
                    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.

            • #1598152 Reply
              macropod
              AskWoody_MVP

              .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]

              • #1598153 Reply
                WSLonemascot
                AskWoody Lounger

                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?

                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.

                Code:
                .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

                Code:
                .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

                Code:
                .SourceNameFullName = NewPath & .SourceName 

                which should have the same effect as Replace(.SourceFullName, OldPath, NewPath) but it still breaks the code.

      • #1598156 Reply
        macropod
        AskWoody_MVP

        From 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.

        Cheers,
        Paul Edstein
        [Fmr MS MVP - Word]

        • #1598186 Reply
          WSLonemascot
          AskWoody Lounger

          From 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.

    Viewing 161 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Word Relative Field Paths Discussion

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.