Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Using VBA to follow Hyperlink (2003 - 2007)

    Hi,

    I was told a scenario today (by a client) that they use Hyperlinks to jump to a file to paste data into that file from the workbook that stores the macro. They said that the workbook (to which the hyperlink points) is housed in a folder whos name changes often. The reason for the hyperlink is that it is dynamic and updates to continue to point to the right file even if the path or folder name changes. This is useful in code as the programmer does not need to modify the path in code when the folder changes.

    I have been trying to test this out, but I cannot seem to simulate the dynamics of a hyperlink. I notice my hyperlink looks like this: ..Test.xls (Now I assume the .. is dynamic as it does not point to a specific folder).

    Can anyone explain where I am missing the point as the scenario I tried to recreate is not doing this.
    Code I have looks like this:

    Sub HyperlinkTest()
    Range("A1:A8").Copy
    'Hyperlink to Workkbook called Test (..Test.xls)
    Range("F1").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Range("A1").Select
    ActiveSheet.Paste
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Range("B1:B8").Copy
    'Hyperlink to Workkbook called TestII (..TestII.xls)
    Range("F3").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    ActiveSheet.Paste
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Range("A1").Select
    Application.CutCopyMode = False
    End Sub

    It runs fine if the path to test and testII are known, but when I change the directory name holding the test files it does not work?
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using VBA to follow Hyperlink (2003 - 2007)

    The hyperlink address ..Test.xls indicates that Test.xls is in the same folder as the workbook. It shouldn't matter what that folder is called, but Test.xls should not be in another folder.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using VBA to follow Hyperlink (2003 - 2007)

    OK, that cleared it up. Although I do not understand now what the client was raving about.

    I was under the impression that they were copying data to WB's in a directory that changed often, whereas the info you tell me (and I tested it out) indicates that the files being copied to must stay in the same place, but the folder containing the file with the hyperlinks can change. This works based on my refreshed test, but why would that be the useful?????

    Tx for clearing up my error.
    Regards,
    Rudi

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using VBA to follow Hyperlink (2003 - 2007)

    You can change the hyperlink address to refer to a file in a particular folder if you give the entire path, instead of as a relative reference.

    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using VBA to follow Hyperlink (2003 - 2007)

    Do you think this is a valid way to run code that refers to WB's that may have relative path locations?

    Another "problem" I came across is that if you do run this code, it stops the macro with the prompt about following hyperlinks could be potentially dangerous.... That is a VERY annoying message!
    Regards,
    Rudi

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using VBA to follow Hyperlink (2003 - 2007)

    I don't get the message about following hyperlinks (but I tested with XL2002). You could try setting Application.DisplayAlerts to false before and True afterwards to see if the message is avoided.

    I don't like the all the selecting and the implicit references. I would suggest:

    <pre>Option Explicit
    Sub HyperlinkTest()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim wkbHL As Workbook
    Dim wksHL As Worksheet

    Set wkb = ThisWorkbook
    Set wks = wkb.Worksheets(1)

    'Hyperlink to Workkbook called Test (..Test.xls)
    wks.Range("F1").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Set wkbHL = ActiveWorkbook
    Set wksHL = wkbHL.Worksheets(1)
    wks.Range("A1:A8").Copy wksHL.Range("A1")
    wkbHL.Close (True)

    'Hyperlink to Workkbook called Test (..Test.xls)
    wks.Range("F3").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Set wkbHL = ActiveWorkbook
    Set wksHL = wkbHL.Worksheets(1)
    wks.Range("b1:b8").Copy wksHL.Range("A1")
    wkbHL.Close (True)

    Application.CutCopyMode = False

    Set wks = Nothing
    Set wkb = Nothing
    Set wksHL = Nothing
    Set wkbHL = Nothing
    End Sub</pre>



    [If you were going to do a lot of ranges of this sort, instead of mulitple copies of the same code, i would create a function with the range of the HL and the range to copy from and copy to as the parameters...]

    Steve

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using VBA to follow Hyperlink (2003 - 2007)

    Thanks for the code Steve. This is not something I am needing myself. I was just experimenting with a theory that I was told by this customer, and I posted because I could not get the hyperlinks to be dynamic. I will heed your advice if the customer needs an improvement on code they are using.

    PS: The message about following hyperlinks also does not come up anymore.
    Cheers
    Regards,
    Rudi

Posting Permissions

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