Automatically Test Hyperlinks in Excel 2007

**Learner22** · 08-01-2012, 05:00 AM

I have generated this as a new thread with the same title, as, apparently, I have not met the forum guidelines in the previous thread.
I am looking for a way to check all the hyperlinks within a workbook. I have several files containing hundreds of hyperlinks to networked files. As the network is accessed by many people, and there are no protocols controlling the movement, I find some of the hyperlinks no longer work because the files are no longer in the hyperlinked location.

In the previous thread the following was posted:

Please Login or Register  to view this content.

This works fine but opens each hyperlink in turn, meaning the PC grinds to a halt through lack of resources.
To overcome this issue the following was posted:

Please Login or Register  to view this content.

This overcomes the problem of opening each hyperlink, but gives an error error code value of '-2147467259' for all files, meaning each hyperlink cell turns red. Note the same files do not show an error in the first code.
I have tried adding a 10 second delay after the '.send' line in case the error is caused by a process timeout, as the files are on our (slow) network. The delay works but the error still exists. The 'Err.Clear' is changing the value to '0' as expected, so the error must be generated within the code.
Does anyone know where I can find out what the error value relates to and better still, how to eradicate it.
How can I modify this code so I do not get an error for each hyperlink?

**UKNinja** · 08-01-2012, 05:07 AM

Have you considered using a FileSystemObject and using the FileExists() method to return true or false if anything is there?

Please Login or Register  to view this content.

This code is quickly written and untested, but should do what you need

**Kyle123** · 08-01-2012, 06:36 AM

or:

Please Login or Register  to view this content.

The xml object won't work since it is used for requesting web addresses, not local resources

**Cutter** · 08-01-2012, 08:52 AM

@ Learner22

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

**Learner22** · 08-14-2012, 05:39 AM

'Kyle' works, in as much as you do not get the files to open, but it returns lots of false negatives. Using a simply workbook with just four hyperlinks I get a length of 12 for one pdf (correct); 0 for one txt (incorrect); 0 for one pdf (incorrect); and 0 for a non-existant file (correct). If I manually re-hyperlink the two 0 files, they still don't work.
'Filetest' gives me 'runtime error 13' for

Please Login or Register  to view this content.

Any ideas?

**Kyle123** · 08-14-2012, 05:47 AM

It's nothing to do with the filetypes. Can you post some sample links?

**Learner22** · 08-14-2012, 06:39 AM

In my workbook I am editing the hyperlink display to only show the file, the four files I am using for my test are:
Big.pdf - works
Test.txt - does not work
Test.pdf - does not work
Copy of Test.txt - does not work

The full path is:
H:\My Documents\Excel\My VBAs\Big.pdf
H:\My Documents\Excel\My VBAs\Test.txt
H:\My Documents\Excel\My VBAs\Test.pdf
H:\My Documents\Excel\My VBAs\Copy of Test.txt

Is this what you wanted?
Rather strangely, now I have added the full hyperlink path, none of the hyperlinks are showing as corrcet, yet they all work when clicked on.