[ABabeNChrist]

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.

[Ryan Knight]

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?

[jscher2000]

Ryan Knight, on 2010-07-13 05:16, said:

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:

If Not .LinkFormat Is Nothing Then _

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

[Ryan Knight]

jscher2000, on 2010-07-13 16:02, said:

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.

[macropod]

Ryan Knight, on 2010-07-14 07:16, said:

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.

[James Edel]

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!

[macropod]

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

[James Edel]

macropod, on 2010-08-03 06:42, said:

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

[James Edel]

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

[macropod]

James Edel, on 2010-08-20 06:44, said:

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.

[James Edel]

macropod, on 2010-08-22 04:01, said:

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:\files\Filea.xlsx]Sheet1!$B$5

So when I move my files to a different directory, it no longer links properly.

It's a similar problem to what your solution fixed for me in my Word files.

[macropod]

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.