+ Reply to Thread
Results 1 to 16 of 16

Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network folders

  1. #1
    Registered User
    Join Date
    10-23-2014
    Location
    Charleston, SC
    MS-Off Ver
    2010
    Posts
    10

    Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network folders

    I am pretty good with excel, but know nothing about VBA or using macros. I have seen similar posts, but because I know so little, I don't know how to make them work for me.

    I have several files where different pieces of data in the file are hyperlinked to a network folder (such as a customer's name in a list hyperlinked to the customer's folder) or a file on the network (such as a date hyperlinked to a pdf file of a document with that expiration date). I want to be able to highlight in purple (just to be sure I'm using a different color than anything else in the sheet) any links that are dead or broken on all sheets of the workbook.

    Most of the time the issue is that the file has been moved or renamed, so when the link is clicked I get the error "Cannot open specified file.". I just want to be able to identify those hyperlinks without having to actually click every one. Then I can correct them as needed.

    I'm hoping for something that doesn't actually open every file, as that would be a show stopper because there are so many links. And, I'm hoping for something that would work in any excel file, because I don't know enough to change the VBA every time.

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    See if this works for you.

    Please Login or Register  to view this content.
    To adjust the color, experiment by turning on the macro recorder and changing the current cell fill color to various colors or shades of purple.
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

  3. #3
    Registered User
    Join Date
    10-23-2014
    Location
    Charleston, SC
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    TYSM! It works, but I'm curious about a couple things...because I'm not familiar with macros.

    I can paste the code in, save it, and run it while the workbook is still open. But, if I close and reopen the file, the macro is not there. Am I doing something wrong? And is there a way to make it available for all workbooks I open?

  4. #4
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    If you're code module is part of the workbook, it will save and stay with the workbook. Make sure you're saving as a .xlsm, but if you weren't, it would have warned you when you tried to save it. You might want to check your Trust Center Settings - Macro Settings. I'd recommend "Disable all macros with notification". You will then need to click "Enable" when you open the workbook.

    You can search the internet for instructions on how to make a macro available for all workbooks. Here's one of the results: http://www.ehow.com/how_6555333_make...workbooks.html

  5. #5
    Registered User
    Join Date
    10-23-2014
    Location
    Charleston, SC
    MS-Off Ver
    2010
    Posts
    10

    Wink Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    You're awesome! I got it working. I thought about running on open, but I'm thinking I'll see how it works for a little while.

  6. #6
    Registered User
    Join Date
    10-23-2014
    Location
    Charleston, SC
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    You're still awesome, but I spoke too soon on it working perfectly. I tried it on a different file, and it colored every cell with a link, but the links are working....?

  7. #7
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    Hmm. I don't know why that would be. You might put a debug stop on the IF statement, and when it hits it, see what's in hl.Address and see if something looks fishy.

  8. #8
    Registered User
    Join Date
    10-23-2014
    Location
    Charleston, SC
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    I don't know enough. It didn't color every link, but almost every one. It did go through several sheets...could it be something to do with being on a network? I can run the file and then click on one of the links and it goes right to it.

  9. #9
    Registered User
    Join Date
    10-23-2014
    Location
    Charleston, SC
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    Sorry I meant to say run the macro instead of run the file.

  10. #10
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    Maybe, but if the link works, it would need to be something different about how the code interprets the link address. Try the debugger option. I should have explained how: In the code window, notice the vertical band just to the left of the code. Click in the band where it aligns with the If statement. This will put a debug stop at that point, and is indicated by highlighting that line of code. It's a toggle that you can turn off and on. Then press f5 to start the code. It will stop on that line. Hover your mouse pointer over hl.Address, and it will show you the value. Press f5 again and it will run until it hits the stop point again. Repeat and compare the differences between the ones that work correctly and those that don't. If you prefer, you can press f8, which steps through the code one line at a time, rather than f5, which runs until a debug stop or until it's finished.

  11. #11
    Registered User
    Join Date
    10-23-2014
    Location
    Charleston, SC
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    I tried the debugger and it showed several of the paths that were correct, so that looked fine. Just to see, I tried the macro from a similar posting...

    Sub TestHLinkValidity()

    Dim rRng As Range
    Dim fsoFSO As Object
    Dim strFullPath As String
    Dim cCell As Range

    Set fsoFSO = CreateObject("Scripting.FileSystemObject")
    Set rRng = Selection
    For Each cCell In rRng.Cells
    If cCell.Hyperlinks.Count > 0 Then
    strFullPath = ActiveWorkbook.Path & "\" & cCell.Hyperlinks(1).Address
    If fsoFSO.FolderExists(strFullPath) = False Then
    cCell.Interior.ColorIndex = 3
    Else
    cCell.Interior.ColorIndex = 0
    End If
    End If
    Next cCell
    End Sub


    Yours is definitely faster. But it did the same thing. It marked everything with a hyperlink, both to files and network folders even if the links worked. The only links it did not highlight were cells with conditional formatting.

  12. #12
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    Please try the following: In your code workbook, add a sheet and name it Results. Then run the following code. It will create Results columns for what it sees as good and bad link addresses. Then you can compare and maybe find something unique in the drive, path, folder, or format that causes them to be interpreted incorrectly.

    Please Login or Register  to view this content.
    I'll be out for the weekend, and will check your response when I get back.

  13. #13
    Registered User
    Join Date
    10-23-2014
    Location
    Charleston, SC
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    After running it, it did list every link as bad. It isn't listing the full path. Instead of listing it as I would think:

    For a file: Drive:\Folder Level 1\Folder Level 2\filename.pdf

    Or for a folder: Drive\Folder Level 1\Folder Level 2

    It's instead just listing - Folder Level 2\filename.pdf on the files, and just the folder name, like Folder Level 2 - on the ones to folders.

    Then for some links that may not have been updated recently, it just says:

    ...\Folder Level 2\Folder Level 3\filename.pdf

    So, I'm not sure if that's allowing it to really check the path. Does that make sense?

    There are quite a few email addresses in the file, so they just show as mailto: email address. I really wouldn't care if it just skipped those, because it's not really going to check the addresses anyway, so it really doesn't need to worry about them.

  14. #14
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    Well, I'm no expert on hyperlinks, but I can't get any to work if they only contain a partial address, even if I set my current path to the higher level folder, so I don't know why your links work at all. Can you reset your hyperlinks to full valid path names?

    Regarding the email address, you could add an IF to eliminate them:

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    10-23-2014
    Location
    Charleston, SC
    MS-Off Ver
    2010
    Posts
    10

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    After doing some research, it appears that the first file I tried the code on that lists the results, was using a starting folder for hyperlinks. So it was listing relative links. That's why the whole link didn't appear. So, that's a whole other mess.

    But, I found another file that does not have a starting folder. It listed all the links that were to specific files and that were working, in the Good column. But it listed all the links to a folder in the Bad column whether they worked or not.

  16. #16
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Excel 2010 - Highlight dead or broken links to files (pdf, jpg, etc.) and network fold

    Yea, I don't suppose the FileExists method would work on folders. If you need it to verify the folder links as well, try .FolderExists. Otherwise, look for something in the address text to exclude them (like I did with mailto).

+ 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. Replies: 4
    Last Post: 04-10-2014, 12:11 PM
  2. Replies: 0
    Last Post: 03-26-2013, 08:34 AM
  3. Replies: 1
    Last Post: 02-29-2012, 01:15 PM
  4. [SOLVED] Excel 2003 won't load files from shared network folders.
    By Lee886 in forum Excel General
    Replies: 0
    Last Post: 01-09-2006, 02:10 AM
  5. links to same files in different folders
    By Henk in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 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