Check hyperlinks

    Hi everyone,


    I really need your help on this case.I have an excel sheet in which there are 2 impotant columns(B and C)As soon as you put a billing number in column B, it will create an hyperlink to the scan report in column C.The scans are found on an external server.


    Here is my problem: I need an automatic way to verify that each url or hyperlink is valid or not for each cell and if possible give me a "OK" or "NOT OK" in column D or even change the cell color.


    Here is my excel sheet.


    Thanks

    Re: Check hyperlinks


    If they're unc shares, you should be able to use:

    Code
    Function FileExists(fPath as string) as Boolean
    If len(Dir(fPath))<>0 then FileExists = True
End Function

    Re: Check hyperlinks


    If they're not somewhere that windows (or you OS) can treat as a drive, then the only way to check that the actually exist is to open them. Otherwise, all you can check is that they match the expected format.

    Re: Check hyperlinks


    Quote from Kyle123;628529

    If they're unc shares, you should be able to use:

    Code
    Function FileExists(fPath as string) as Boolean
    If len(Dir(fPath))<>0 then FileExists = True
End Function


    Hi sorry how does you use this to work as i can't see it in my function list.

    Re: Check hyperlinks


    That's a User Defined Function (UDF); you need to add it yourself by copying the code into a new module in the VBA project. (Alt+F11>Alt+I>Alt+M, then paste the code into the open window.)
    You can then use the UDF with '=fileexists([the file path you're looking for]).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login