+ Reply to Thread
Results 1 to 11 of 11

Test Hyperlinks to files on Network Drive

  1. #1
    Registered User
    Join Date
    10-14-2016
    Location
    Pullman, Washington
    MS-Off Ver
    2016
    Posts
    5

    Test Hyperlinks to files on Network Drive

    Hello,
    I am in need of a macro that will check to see if a hyperlink is valid. I am using the hyperlink function in Excel, not the wizard.
    I have a large worksheet, and I would like the macro to test each hyperlink for validity without opening each file.

    I found a thread that set me on the right direction here.

    Here is the code that I have been using.

    Please Login or Register  to view this content.
    This code works great, but only if I hyperlink the files using the wizard. I have been using the formula for hyperlink because I need to include a cellular reference for the name of the file.
    Here is a sample of the formula that I have been using to produce the hyperlink.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I have Excel 2016.
    This seems like a fairly simple change, but I don't know what to do. Please help!
    Thanks,
    Joe

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Test Hyperlinks to files on Network Drive

    I have not tested this but give it a try...

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,575

    Re: Test Hyperlinks to files on Network Drive

    You may need to Replace() file:///. e.g.
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    10-14-2016
    Location
    Pullman, Washington
    MS-Off Ver
    2016
    Posts
    5

    Re: Test Hyperlinks to files on Network Drive

    Thank you both for your reply.

    AlphaFrog,
    I think you may be onto something. The macro now runs without errors, but it tags every cell as though the hyperlink does not exist. I tested it on a few, and I got two correct negatives and two false negatives.

    Kenneth,
    I tried your macro and it resulted in a "subscript out of range" error on this line.
    Please Login or Register  to view this content.
    I would try to tinker with them, but we are so far beyond my understanding of macros and here be dragons.
    Thanks,
    Joe
    Last edited by joe.cross; 10-18-2016 at 10:46 AM.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Test Hyperlinks to files on Network Drive

    I'm not sure how to evaluate the relative file path used in your formulas. Below might work (untested), but don't be surprised if it doesn't. It's a bit of a kludge.

    Please Login or Register  to view this content.
    If it doesn't work, and if you can avoid using relative paths (file:///\) in your formulas, the original suggested code in post #2 should work with absolute paths.

  6. #6
    Registered User
    Join Date
    10-14-2016
    Location
    Pullman, Washington
    MS-Off Ver
    2016
    Posts
    5

    Re: Test Hyperlinks to files on Network Drive

    AlphaFrog,
    That worked exactly the same as before. No errors, but it was unable to distinguish if the file was actually there.
    Are we thinking at this point that there is no way to accomplish this?
    I appreciate your effort.
    Thanks,
    Joe

  7. #7
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Test Hyperlinks to files on Network Drive

    I think there is probably a way to do it, but I'm not sure how using relative paths in the formulas.

    Again, can you avoid using relative paths in the formulas?

  8. #8
    Registered User
    Join Date
    10-14-2016
    Location
    Pullman, Washington
    MS-Off Ver
    2016
    Posts
    5

    Re: Test Hyperlinks to files on Network Drive

    Whoops. I thought I included that in my reply.
    I need to use relative paths. It is a critical feature of the worksheet that I am working on here.

    More information: Each line is a transaction and the hyperlink is linking in the receipt for the transaction that is named as the transaction number.
    Relative references are necessary in the file name so I can grab the transaction number from the excel sheet, and grab the file that is named that number.

    Thanks,
    Joe

  9. #9
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Test Hyperlinks to files on Network Drive

    Quote Originally Posted by joe.cross View Post
    Whoops. I thought I included that in my reply.
    I need to use relative paths. It is a critical feature of the worksheet that I am working on here.

    More information: Each line is a transaction and the hyperlink is linking in the receipt for the transaction that is named as the transaction number.
    Relative references are necessary in the file name so I can grab the transaction number from the excel sheet, and grab the file that is named that number.

    Thanks,
    Joe

    I'm not talking about the file Name, I'm talking about the paths. This file:///\ is a relative file path.

  10. #10
    Registered User
    Join Date
    10-14-2016
    Location
    Pullman, Washington
    MS-Off Ver
    2016
    Posts
    5

    Re: Test Hyperlinks to files on Network Drive

    Wow. I didn't even know what relative paths were.
    I inherrited this formula from my predecessor. Not sure why he used the relative references, but I changed them and now it works!
    Thank you so much! You have saved my office and I so much work!
    You are very skilled with Excel.
    Thanks,
    Joe

  11. #11
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Test Hyperlinks to files on Network Drive

    You're welcome. Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Hyperlink to .pdf files saved on network drive
    By carlw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-03-2017, 12:21 PM
  2. Memory Error on Network drive files
    By ngiesbrecht in forum Excel General
    Replies: 5
    Last Post: 08-17-2015, 05:57 PM
  3. Using Links to Network Drive files in Excel 2010
    By estratosphere in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-02-2015, 05:38 AM
  4. Macro Will not work to process files stored on network drive.
    By rmingram in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-01-2014, 03:31 PM
  5. Hyperlinks to network drive locations keep changing
    By coldscooter in forum Excel General
    Replies: 1
    Last Post: 12-31-2012, 10:40 AM
  6. Save files to a Mapped Network Drive on server
    By wotsup in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-12-2012, 11:46 AM
  7. [SOLVED] Excel creates .XAR & .TMP files on network drive
    By OWN3D!!1 in forum Excel General
    Replies: 1
    Last Post: 11-16-2005, 01:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1