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

    This topic contains 245 replies, has 49 voices, and was last updated by  macropod 9 months ago.

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

    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.

    Cancel