Msgbox to also show pdf number thats missing

[ipbr21054]

Hi,

Using the code below i am trying to have a msgbox pop up with a message & also the number of the pdf that ive been advised wasnt found

Private Sub HyperlinkInvoiceNumber_Click()
Dim lastRow As Long
Dim myPath As String, fileName As String


myPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" 'FOLDER LOCATION OF WHERE PDF ARE SAVED
lastRow = Range("P" & Rows.Count).End(xlUp).Row

For i = 6 To lastRow

    fileName = myPath & Range("P" & i).Value & "*.pdf"
    
    If Len(Dir(fileName)) <> 0 Then 'IF THE PDF FILE EXISTS THEN
    
        ActiveSheet.Hyperlinks.Add Range("P" & i), myPath & Dir(fileName)
        Else
      MsgBox "PDF DOES NOT EXIST" & p.Value, vbExclamation, "PDF IS MISSING IN THE FOLDER"
    
    End If
    
Next
End Sub

 

[mumps]

Try:

Private Sub HyperlinkInvoiceNumber_Click()
    Dim LastRow As Long, myPath As String, FileName As String, fName As Variant
    myPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" 'FOLDER LOCATION OF WHERE PDF ARE SAVED
    LastRow = Range("P" & Rows.Count).End(xlUp).Row
    For i = 6 To LastRow
        If Not Dir(myPath & Range("P" & i) & ".pdf", vbDirectory) = vbNullString Then
            ActiveSheet.Hyperlinks.Add Range("P" & i), myPath & Range("P" & i) & ".pdf"
        Else
            MsgBox "PDF DOES NOT EXIST " & Range("P" & i), vbExclamation, "PDF IS MISSING IN THE FOLDER"
        End If
    Next i
End Sub

 

[ipbr21054]

Hi,
That now advises me of the number that wasnt found thanks.

In some cells there will be N/A of which the old code ignored.
This code mentioned above now advises me that the N/A was also not found as like a number.

Can we have the code ignor any cells that have N/A in them.

Thanks

 

[ipbr21054]

Can you advise on the following please.

I would like the message to advise PDF 123 DOES NOT EXIST

Private Sub HyperlinkInvoiceNumber_Click()
    Dim LastRow As Long, myPath As String, FileName As String, fName As Variant
    
    myPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" 'FOLDER LOCATION OF WHERE PDF ARE SAVED
    
    LastRow = Range("P" & Rows.Count).End(xlUp).Row
    For i = 6 To LastRow
    
        If Not Dir(myPath & Range("P" & i) & ".pdf", vbDirectory) = vbNullString Then
            ActiveSheet.Hyperlinks.Add Range("P" & i), myPath & Range("P" & i) & ".pdf"
        Else
            MsgBox "PDF " & Range("P" & i), vbExclamation, "PDF IS MISSING IN THE FOLDER"
        End If
    Next i
End Sub

Currently i can get PDF 123 only without any kind of errors.
Not sure on correct characters to be used in the syntax

Thansk

 

[mumps]

Try:

Private Sub HyperlinkInvoiceNumber_Click()
    Dim LastRow As Long, myPath As String, FileName As String, fName As Variant
    myPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" 'FOLDER LOCATION OF WHERE PDF ARE SAVED
    LastRow = Range("P" & Rows.Count).End(xlUp).Row
    For i = 6 To LastRow
        If Range("P" & i) <> "N/A" Then
            If Not Dir(myPath & Range("P" & i) & ".pdf", vbDirectory) = vbNullString Then
                ActiveSheet.Hyperlinks.Add Range("P" & i), myPath & Range("P" & i) & ".pdf"
            Else
                MsgBox "PDF " & Range("P" & i) & " DOES NOT EXIST.", vbExclamation, "PDF IS MISSING IN THE FOLDER"
            End If
        End If
    Next i
End Sub

 

[ipbr21054]

That did the trick
Thanks very much for your time

 

[mumps]

You are very welcome.