+ Reply to Thread
Results 1 to 6 of 6

Checking for broken =hyperlink() file links

  1. #1
    Registered User
    Join Date
    10-10-2019
    Location
    Calgary
    MS-Off Ver
    Vista
    Posts
    3

    Checking for broken =hyperlink() file links

    Hi,
    I have a huge spreadsheet that has thousands of hyperlinks linking to various files on my local drive.

    The way I set up my hyperlink was to refer to other cells to get the folder and file name, essentially:

    =hyperlink(A1&"\"&A2&"\"&A3&".pdf",A3")
    Say A1 has folder1
    A2 has folder2
    A3 is the File Name.

    Ultimately what I end up with are the local hyperlinks to the files:
    folder1\folder2\file.pdf

    I want to check which links actually open files properly (the file pdf), and which links do not (meaning the file is missing, something isn't named properly, etc).

    If the link doesn't work, I get the following error:
    "Cannot open the specified file"

    I want to somehow highlight the links that give that error so I can go and fix them.

    I tried several different scripts that I found on this forum but none of them worked for me. Any help would be much appreciated! Thanks!

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Checking for broken =hyperlink() file links

    Can you upload sample workbook that replicates your actual workbook set up? With about 10 hyperlinks to demo.

    Code will largely depend on your workbook structure.

    To upload, use "Go Advanced" button, follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

    If unable to, try using DIR("FullFilePath").

    Ex:
    Please Login or Register  to view this content.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    10-10-2019
    Location
    Calgary
    MS-Off Ver
    Vista
    Posts
    3

    Re: Checking for broken =hyperlink() file links

    Hi, okay, I've attached a sample sheet.

    My hyperlink formula is a MESS, apologies! I hope that doesn't prevent a macro. The formula scans through and if a sub-folder isn't relevant, then it moves up, etc.
    Plus it reads structure documents and some other file types a little differently.

    But if you hover over the hyperlink it should give the proper file path.

    Thanks for your help!
    Attached Files Attached Files

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Checking for broken =hyperlink() file links

    So you have relative path to the linked file.

    As well, you are using "[@[DOCUMENT NO.]]" to return friendly name for the hyperlink formula.

    This makes it difficult to check using code alone.

    What I would suggest, is to add helper column to end of your table and add formula, but removing friendly name portion of hyperlink formula.

    This will give you relative path to your linked file.

    Something like below should work. I would suggest making a copy of your workbook and running code in it to test.
    Please Login or Register  to view this content.
    EDIT: Below may be faster.
    Please Login or Register  to view this content.
    Last edited by CK76; 10-10-2019 at 01:23 PM.

  5. #5
    Registered User
    Join Date
    10-10-2019
    Location
    Calgary
    MS-Off Ver
    Vista
    Posts
    3

    Re: Checking for broken =hyperlink() file links

    That appears to work perfectly, thank you very much!!

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Checking for broken =hyperlink() file links

    You are welcome and thanks for the rep

    Oh, if you are satisfied with the answer provided, please mark the thread as solved using thread tools found at top of your initial post.

+ 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. [SOLVED] Checking for broken hyperlinks
    By ChrisMattock in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 06-22-2018, 02:46 PM
  2. Delete broken/obsolete links from the 'Links' manager
    By LeaIngemann in forum Excel General
    Replies: 0
    Last Post: 07-01-2015, 09:16 AM
  3. [SOLVED] Get file links and Hyperlink, Not getting first file
    By ratdogexcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-31-2013, 11:18 PM
  4. Broken links in Word to Excel file
    By tezread in forum Excel General
    Replies: 1
    Last Post: 12-18-2013, 11:42 AM
  5. Checking for broken URLs
    By excelforum123 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-09-2013, 02:25 PM
  6. Reading Filename from a Hyperlink in a cell and renaming the file it links to.
    By pontneddfecahn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-16-2013, 03:31 PM
  7. [SOLVED] checking file exists before adding hyperlink
    By TaliKL in forum Excel General
    Replies: 8
    Last Post: 03-02-2013, 06:26 AM

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