Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relative link to Excel spreadsheet with macro problem

    Hi all,

    I've been trying to use the macro posted by macropod here:
    http://windowssecrets.com/forums/sho...External-Files

    ...to link excel worksheets to my Word document and have the links automatically updated when the containing folder is moved.

    Somehow I always get an error telling me that a file with the name of my Excel document is already open. This message pops up a couple of times, then I get a runtime error from the code. I ran the code step by step and all of the filepaths are ok, but still I get the error when running the replace() function.
    I really tried everything I could think of, without luck.

    FYI, the Word and the Excel documents are side by side. Only the containing folder is meant to change place
    Also, here is an example of the links I have:
    { LINK Excel.Sheet.12 OldPath\SpreadSheet.xlsx WorkSheet!L1C1:L17C4 \a \f 4 \h }


    Any ideas?

    Thanks so much,
    20Syl

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Try changing the links from auto update to manual update. You may also encounter problems in the linked objects are in the page header/footer.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Dec 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by macropod View Post
    Try changing the links from auto update to manual update. You may also encounter problems in the linked objects are in the page header/footer.
    I have already tried switching to manual update (by removing the \a command). Didn't work. And my links are not part of the header nor footer.
    It seems that every time a link is replaced, Word automatically opens an instance of the linked file which I can't see. Is there something I could add to the code to detect if the file is opened and if so, close it?

    Also, I have tried .xls as well as .xlsx file types to see if this could help. It worked the first time after the file type change, then stopped working. I noticed Word automatically updated Excel.Sheet.8 to Excel.Sheet.12 at that moment... Is this normal? What file types are preferrable?

    Thank you,
    20Syl

  4. #4
    New Lounger
    Join Date
    Dec 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi again,

    I ran some further tests.
    I can confirm that, when opening the Word document when the Excel document is not opened, an Excel process is automatically started and I get a runtime error from the code. What I can do is debug the code and once debugging, kill the Excel process, then finish running the code. This works, however it is not a viable solution.

    I also tried opening the Excel file first, then opening the word document. Doing this makes the error popup from Excel appear, but I can dismiss it and then everything works fine without runtime errors from Word. This is a more viable solution, but stil not the best.

    Any suggestions?
    Thanks.

  5. #5
    Star Lounger
    Join Date
    Feb 2011
    Location
    Washington, DC
    Posts
    98
    Thanks
    8
    Thanked 6 Times in 6 Posts
    Have you tried using application.DisplayAlerts = False ?

    This shuts off all alerts. It's best to turn it back on at the end of the macro via

    Application.DisplayAlerts = True

  6. #6
    New Lounger
    Join Date
    Dec 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just tried it. This doesn't prevent any of the error messages to pop up.

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by jweissmn1 View Post
    Have you tried using application.DisplayAlerts = False ?

    This shuts off all alerts. It's best to turn it back on at the end of the macro via

    Application.DisplayAlerts = True
    That really isn't going to help as it has nothing to do with the underlying issue.

    I'll have to devise a different approach, but I don't have time for that ATM - perhaps next week.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    New Lounger
    Join Date
    Dec 2014
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, thank you. I will work on something else for the moment.
    Perhaps it will help telling you that this issue does not occur on every PCs. I had it tried out by two of my colleagues, and one of them could open the file without problem, and the other had the same issue as me.

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by 20Syl View Post
    Perhaps it will help telling you that this issue does not occur on every PCs. I had it tried out by two of my colleagues, and one of them could open the file without problem, and the other had the same issue as me.
    In that case, it seems the problem is likely to be a configuration issue, perhaps caused by an errant 3rd-party addin, or a faulty Office installation. Have you tried repairing the Office installation (via Start > Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change)?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Tags for this Thread

Posting Permissions

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