Problems with OLE excel files linked to a word file.

[JimDavis]

With a word file containing dozens of links to excel files, transferring from one computer to anouther they are all broken because the folder set up is different.
This piece of code works, but not on all files and it takes about 20 seconds a file.

ActiveDocument.Fields(i).LinkFormat.SourceFullName = ActiveDocument.Path & "\" & ActiveDocument.Fields(i).LinkFormat.SourceName

and this is just as slow.
mySourcename = ActiveDocument.Path & "\" & ActiveDocument.Shapes(i).LinkFormat.SourceName
ActiveDocument.Shapes(i).LinkFormat.SourceFullName = mySourcename

also setting autoupdate on false only works if you first repair the link:




in the sense that this gives you a run time error

ActiveDocument.Fields(i).LinkFormat.AutoUpdate = False
ActiveDocument.Fields(i).LinkFormat.SourceFullName = ActiveDocument.Path & "\" & ActiveDocument.Fields(i).LinkFormat.SourceName

If the above worked it would hopefully mean it is not necessary to turn global automatic update options off in word and in excel.

Anybody got any ideas.
Thanks in advance

[Bilby]

Greetings Jim,

I run an application based on Word templates. In order to stop similar problems when changing computers I have all main paths stored in an ini file.

If there is a common path used in your links you may be able to utilise it.

An example would be; Instead of copying a template to Words startup directory explicitly you would search for the users Winword.exe and use this path to find the startup directory. Assuming that the basic folder structure on the machines would have a similar heirachy it should be possible to find these relative paths first.

[JimDavis]

Hi Grilby,
thanks for the suggestion, these files come from many different people in a very large organisations (banking group), Oh an many of them are converted from star office, just to make it really interesting ;-) Anywaw

This piece of code works:
ActiveDocument.Fields(i).LinkFormat.SourceFullName = ActiveDocument.Path & "\" & ActiveDocument.Fields(i).LinkFormat.SourceName

and so does this:
mySourcename = ActiveDocument.Path & "\" & ActiveDocument.Shapes(i).LinkFormat.SourceName
ActiveDocument.Shapes(i).LinkFormat.SourceFullName = mySourcename

but it is oh so slow, (20 secs per word file x 10 excek file links) x 15 word files.
It is quicker if I open excel (obviously this code activates some ole) except that excel keeps asking if you want to update links, so yu have to read the messages and press enter.
So:

setting autoupdate on false only works if you first repair the link:

in the sense that this gives you a run time error

ActiveDocument.Fields(i).LinkFormat.AutoUpdate = False
ActiveDocument.Fields(i).LinkFormat.SourceFullName = ActiveDocument.Path & "\" & ActiveDocument.Fields(i).LinkFormat.SourceName

while this works, but is too late to solve the problem of excel asking you if you want to update the file each time

ActiveDocument.Fields(i).LinkFormat.AutoUpdate = False
ActiveDocument.Fields(i).LinkFormat.SourceFullName = ActiveDocument.Path & "\" & ActiveDocument.Fields(i).LinkFormat.SourceName

In the mean time I have programmatically turned global automatic update options off in word and in excel and then back on again.

Also while building some other functions onto this (importing excel files as rtf tables into word) when I ran the macro word just hanged there. I had to close it with alt ctrl del. When double clicked on an excel file in windows explorer excel opened but the excel file didn't. Microsoft issued a patch for this problem (which with my latest updates I should already have) about 6 months ago. Anyway I used my lenovo rescue and recovery "regenerate your operating system" backup option to solve that one.
jim