+ Reply to Thread
Results 1 to 9 of 9

Find broken formulated hyperlinks to files in my network

  1. #1
    Registered User
    Join Date
    02-08-2017
    Location
    Cucuta, Colombia
    MS-Off Ver
    2013
    Posts
    5

    Find broken formulated hyperlinks to files in my network

    Good morning all,

    I'm working right now with a document that contents a lot of formulated hyperlinks, some of the original files where named by a coworker an there is the possibility that some of them weren't named right, so i need to check which ones of the formulated hyperlinks aren't working; i found a similar thread in this forum, but unfortunately the macro they used as a solution wasn't working for me, because my links are formulated.

    here is the thread i found

    http://www.excelforum.com/showthread.php?t=760760

    I hope you can help this guy who only knows the basics in VBA, thank you all for your help.

  2. #2
    Registered User
    Join Date
    02-08-2017
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Find broken formulated hyperlinks to files in my network

    Hi - not sure I understand your question fully. What do you mean with "links are formulated". Also - why do the links not work: is the link incorrect or does the file not exist?

  3. #3
    Registered User
    Join Date
    02-08-2017
    Location
    Cucuta, Colombia
    MS-Off Ver
    2013
    Posts
    5

    Re: Find broken formulated hyperlinks to files in my network

    Hi, they are formulated with 'Hyperlink' and 'Concatenate' formulas, for example: =Hyperlink(Concatenate(Depu!$A$7,"Expedientes\",A3,".docx"),A3) ;

    The Depu!$A$7 cell contains a references to the location of my workbook, the "expedientes\" text completes the direction where my files are located, and the "A3" cell brings the name of the file that is hyperlinked. So, the A3 cell and the file name should be equal.

    The links could be incorrect because some files were named wrong (misspelled) so they don't exist at all.

    I hope you understood me, English isn't my first language as you can probably tell, thanks a lot for taking your time to read my post, i really appreciate it.

  4. #4
    Registered User
    Join Date
    02-08-2017
    Location
    Cucuta, Colombia
    MS-Off Ver
    2013
    Posts
    5

    Re: Find broken formulated hyperlinks to files in my network

    Hi, they are formulated with 'Hyperlink' and 'Concatenate' formulas, for example: =Hyperlink(Concatenate(Depu!$A$7,"Expedientes\",A3,".docx"),A3) ;

    The Depu!$A$7 cell contains a references to the location of my workbook, the "expedientes\" text completes the direction where my files are located, and the "A3" cell brings the name of the file that is hyperlinked. So, the A3 cell and the file name should be equal.

    The links could be incorrect because some files were named wrong (misspelled) so they don't exist at all.

    I hope you understood me, English isn't my first language as you can probably tell, thanks a lot for taking your time to read my post, i really appreciate it.

  5. #5
    Registered User
    Join Date
    02-08-2017
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Find broken formulated hyperlinks to files in my network

    so - do any of the links work, or only some?
    One item you want to look at, does Depu!$A$7 finish with "\"? If not, that may cause an error, which you can resolve by changing your formula to ...A$7,"\Exped..
    Have you tried to see what the formula looks like without the hyperlink, i.e. =Concatenate(Depu!$A$7,"Expedientes\",A3,".docx") and does that give the correct name? Copy and paste the result in your explorer to see what happens....

  6. #6
    Registered User
    Join Date
    02-08-2017
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Find broken formulated hyperlinks to files in my network

    so - do any of the links work, or only some?
    One item you want to look at, does Depu!$A$7 finish with "\"? If not, that may cause an error, which you can resolve by changing your formula to ...A$7,"\Exped..
    Have you tried to see what the formula looks like without the hyperlink, i.e. =Concatenate(Depu!$A$7,"Expedientes\",A3,".docx") and does that give the correct name? Copy and paste the result in your explorer to see what happens....

  7. #7
    Registered User
    Join Date
    02-08-2017
    Location
    Cucuta, Colombia
    MS-Off Ver
    2013
    Posts
    5

    Re: Find broken formulated hyperlinks to files in my network

    Hi, and thanks for taking your time to answer again, my problem isn't really with the formula, almost all the hyperlinks are working and the formula is doing ok, the problem is that just a few hyperlinks aren't working, and I can't check them one by one to see which ones are working and which ones are not because there are too much rows; so what I want is to identify which hyperlinks aren't working so I can fix the .docx filenames.

    For example:
    Case A (Hyperlink works): My A2 cell has the username "154283", in my folder “expedients” there is a .docx with the name “154283.docx”, when that’s the case, the hyperlink works correctly.
    Case B (Hyperlink doesn’t work): My A2 cell has the username "154283", in my folder “expedients” there isn’t a .docx with the name “154283.docx”, because when it was created there was a typing error so the file has the name “154238” when that’s the case, the hyperlink doesn’t work.

  8. #8
    Registered User
    Join Date
    02-08-2017
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Find broken formulated hyperlinks to files in my network

    aha - now I understand. You basically want a QA check on the list of filenames..... Whilst checking if the link works is one option, I would argue that you are better off using one of the existing xls tools to list all your files in your directory and compare that against the list that you have (or just use the automatically generated list.....). If you google "list dir xls" you get plenty of good examples. Good luck, and let us know how you get on.

  9. #9
    Registered User
    Join Date
    02-08-2017
    Location
    Cucuta, Colombia
    MS-Off Ver
    2013
    Posts
    5

    Re: Find broken formulated hyperlinks to files in my network

    I didn't know I could do that, I already solved my problem, I just made a list of the files with an excel format I found on google and compared it with my original list, thanks a lot for your help!

+ 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] Test Hyperlinks to files on Network Drive
    By joe.cross in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-18-2016, 08:34 PM
  2. Broken hyperlinks to files
    By biederboat in forum Excel General
    Replies: 8
    Last Post: 12-10-2014, 06:46 PM
  3. Replies: 15
    Last Post: 10-27-2014, 01:03 PM
  4. Find broken hyperlinks and highlight them
    By SIM.SIM in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-08-2013, 05:39 PM
  5. [SOLVED] Hyperlinks to files on network drivers
    By Fornol in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-22-2006, 07:15 PM
  6. Creating hyperlinks to network files
    By akronpow in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-25-2005, 05:45 PM
  7. Hyperlinks to network files
    By Tommc49 in forum Excel General
    Replies: 0
    Last Post: 06-25-2005, 05: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