Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Hyperlinks in Excel 97 (Office 97 SR2)

    Steve

    Sounds like there is something wrong with the link. It should not close the workbook, but when you say close the workbook, do you mean that the workbook is no longer accessable or that it is hidden?

    I will try to make such a hyper link, to a Word doc, and test it, but if you would upload the workbook it would be easier.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    New Lounger
    Join Date
    Jan 2001
    Location
    Essex, UK, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks in Excel 97 (Office 97 SR2)

    Wassim,

    You're right - the sheet isn't closed , it's hidden, (although the effect is much the same). I'ver found a Technet article, (Q156264), that seems to suggest that this is the way things should work.

    If the workbook is "dirty" (i.e. amended and unsaved), then it works the way I want it to. If the workbook is saved, however, then it's hidden as the linked document is opened.

    The article mentions a couple of workarounds but neither are really acceptable.

    We have a similar set of files created for a CD sent out earlier in the year and this works impeccably. I can't find a way to excercise any more control over the hyperlink, however, other than the location of the linked file and whether the link is relative or absolute. And, predictably, the chap that created the original set doesn't work for this division any more and is anyway unavailable for a few days.

    Still mystified...

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Hyperlinks in Excel 97 (Office 97 SR2)

    Steve

    Check the folowing MSKB article Q156264. It says that:

    When you click a hyperlink in Microsoft Excel 97, the document that contains the hyperlink is automatically hidden if the document is "clean" (you have not changed the document since it was last saved). If the document is "dirty" (you have changed the document since it was last saved), Microsoft Excel 97 does not hide the document.

    Note that this behavior does not apply to situations when you click a hyperlink that jumps to another location within the same Microsoft Excel workbook.

    Microsoft Excel hides, not closes, the document. You can unhide hidden documents by clicking Unhide on the Window menu.

    Workaround:
    Unhide the document in Microsoft Excel. To do this, use the following steps:
    On the taskbar, click Microsoft Excel.
    On the Window menu, click Unhide.
    Select the document in the list of hidden windows and click Unhide.

    or I would suggest doing a bit of VBA here and make the workbook "dirty".

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    New Lounger
    Join Date
    Jan 2001
    Location
    Essex, UK, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks in Excel 97 (Office 97 SR2)

    Wassim,

    We've found and examined a copy of the CD that was sent to our clients earlier in the year and... it works in exactly the same way, hiding the workbook when the hyperlink is clicked.

    When unhidden, any number of other links from the same sheet can be followed without it hiding because this time, the document is dirty. The followed link has changed colour and I assume Excel reacts to this as an amendment to the workbook.

    In order to get around this and make things as slick as possible for our clients, I think the way forward is to create an AutoOpen macro for the workbook that multiplies the contents of a cell by 1, say. This will immediately make the sheet "dirty" and it won't hide when the link is followed.

    Whoever uses it will still get the "Do you want to save..." dialog when closing the workbook, but that's acceptable. They won't be able to save to a CD anyway. I know that we can probably get rid of that dialog by creating a macro that's run on closure that forces a close without saving, but I'm reluctant to do that in case the clients want to copy the workbook onto their hard drives and play with the data on it, (there's a pivot table and data sheet of Company results on tabs2 and 3, with the top tab having the links to the documents).

    This sounds like one of those "features" that Microsoft insists are assets and yet don't appear to have been implemented in the other members of the Office family.

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Hyperlinks in Excel 97 (Office 97 SR2)

    Steve

    Better yet have the before_Close event save the workbook automatically and the User will not be prompted.

    So if the workbook was clean but made dirty by the Open event code well so be it, it got saved whats the big deal, anyone knows that things get saved, and if it was dirty even after the code made it dirty well so it saved the changes again whats the big deal?

    In any case you are Golden, plus I enjoyed using the Clean and Dirty words here.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    New Lounger
    Join Date
    Jan 2001
    Location
    Essex, UK, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlinks in Excel 97 (Office 97 SR2)

    Dear all,

    This maybe one of those obvious queries to which I can justifiably be told to "RTFM", but I'm being harrassed for an answer and looking as quick as I can.

    One of my users has created a series of Office 97 files (Word docs, Excel workbooks and Powerpoint presentations) for burning to CD, with hyperlinks in the files pointing to others in a logical sequence, (so he tells me).

    The problem we have is that an Excel worksheet contains links to various Word documents which, if clicked on, invoke the correct document, but close down the worksheet in the process. Excel still remains but with an empty window.

    What we'd like to happen is for the link to take us to the correct document but for the worksheet to remain so that a user is free to click on other links without having to open the worksheet again.

    Any suggestions...?

    Many thanks,

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Hyperlinks in Excel 97 (Office 97 SR2)

    If I remember correctly, using Auto_Open or WorkBook_Open event and

    ThisWorkbook.Saved = False

    makes the WB "dirty", and conversely using

    ThisWorkbook.Saved = True

    in a WorkBook_BeforeClose event make it again seem "clean" at user close.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Hyperlinks in Excel 97 (Office 97 SR2)

    John

    That is true.

    BUT what if you do something like:

    Sub WorkBook_Open ()
    ThisWorkbook.Saved = False
    End Sub

    and the user does change something, you still need to know that the workbook is in fact dirty due to the user changes. Conversely you want to know that the workbook is clean if you care about not wasting time to save a clean workbook.

    Its basically some CYA type coding.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Hyperlinks in Excel 97 (Office 97 SR2)

    True, Wassim. In that case I guess you can skip the ThisWorkbook.Saved = True in WorkBook_BeforeClose, and the WB will warn the user of changes.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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