Missing PDF filles hyperlinked to Excel

[ilyas87]

Dear All,

I have a log of inspection files (pdf) hyper linked with Excel log sheet, the problem is I have linked hundreds of such pdf files within single excel sheet. some of pdf files are suppose to be added in future, however its files names and location were already updated in the list.
Need to code to highlight such missing pdf links, so that it can be sort out and updated.

Looking forwards suggestion and solution to resolve.

Thanks and Regards

Ilyas

[CK76]

Use Dir() function on hyperlink.address, assuming hyperlink address is absolute reference (and not relative path to pdf).

Ex:

Sub Demo()
Dim hl As Hyperlink
For Each hl In ThisWorkbook.Sheets("Sheet1").Hyperlinks
    If Dir(hl.Address) = vbNullString Then
        hl.Delete 'Or some other operation
    Else
        'Do something?
    End If
Next
End Sub

But you'll need to adjust the code to suite your need as I'm not sure how your workbook is set up and what exactly should be done by the code.

Note: Above also assumes hyperlinks are not built using HYPERLINK() function.

[ilyas87]

Thanks for the reply,

hyperlink are relative path and hyperlink created using hyperlink function. Kindly find sample excel sheet for your reference and pdf file names for your reference.

Thanks and regards

Ilyas

[CK76]

Hmm? You are using absolute path. Not relative path in relation to workbook itself.

Relative path would be something like ..\Folder\File.pdf. You have absolute path using "drive letter:\Path\File.pdf".

Since, HYPERLINK formula cannot be accessed via Hyperlink object. You must read the formula text and manipulate it.

Ex: With your sample.

Sub Demo()
Dim cel As Range
For Each cel In Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row).Cells
    x = Split(cel.Formula, """")
    y = x(1) & x(3) & cel.Value & x(5)
    If Dir(y) = vbNullString Then
        'Do something when linked file does not exist
    Else
        'Do nothing. Or something when file exist
    End If
Next
End Sub

[ilyas87]

Thanks for the reply.

But I got run time error:9 Subscript Out of range.
Kindly help to resolve the issue.

Thanks

[CK76]

So you have pattern that's different from what you had in your sample.

Split() function splits string based on double quote. Use that logic or some other text manipulation as needed.
If you are unable to modify. Then upload sample that's more representative of your setup.

[ilyas87]

Thank you so much for support. It's work great.