Consulting

Results 1 to 3 of 3

Thread: Problems with OLE excel files linked to a word file.

  1. #1
    VBAX Newbie
    Joined
    Dec 2006
    Posts
    2
    Location

    Problems with OLE excel files linked to a word file.

    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

  2. #2
    VBAX Regular
    Joined
    Sep 2004
    Posts
    65
    Location
    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.

  3. #3
    VBAX Newbie
    Joined
    Dec 2006
    Posts
    2
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •