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.

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?

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.

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.

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....

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....

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.

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.

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!