How to Print multiple Hyperlink pdf file using VBA?

[Shahin Jack]

Hello Everyone . I want to print my hyperlinked all pdf file by 1 Click by using VBA.

 

[John_w]

What type of hyperlinks are they? =HYPERLINK formulas or inserted via the Insert Hyperlink dialogue?

 

[Shahin Jack]

Hello dear, I have many PDF files in My C Drive folder in my computer. I'll just print some of files that have Hyperlinks in my Excel file which module picture are already share. I just want to print only hyperlink pdf file by 1 Click .

 

[John_w]

You haven't answered my question, so I wrote this macro which should work for both types of hyperlink. It assumes the links are in column B on the active sheet, starting at B2.

The PDFs are printed using the default application for PDF files, since it calls the 'Print' option of the right-click context menu for .pdf files in File Explorer.

Public Sub Print_Hyperlink_PDFs()
    
    Dim r As Long
    Dim PDFfile As String
    
    With ActiveSheet
        For r = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
            PDFfile = GetHyperlinkLocation(.Cells(r, "B"))
            If PDFfile <> "" Then
                Print_PDF PDFfile
            Else
                MsgBox "Link location not found for cell " & .Cells(r, "B").Address, vbExclamation
            End If
        Next
    End With
    
End Sub

Private Function GetHyperlinkLocation(cell As Range) As String

    Dim p1 As Long, p2 As Long
    
    With cell.Item(1, 1)
        If .Hyperlinks.Count = 1 Then
            GetHyperlinkLocation = .Hyperlinks(1).Address
        Else
            p1 = InStr(1, .Formula, "HYPERLINK(", vbTextCompare)
            If p1 > 0 Then
                p1 = p1 + Len("HYPERLINK(")
                p2 = InStr(p1, .Formula, ",")
                If p2 > 0 Then
                    GetHyperlinkLocation = Evaluate(Mid(.Formula, p1, p2 - p1))
                End If
            Else
                GetHyperlinkLocation = ""
            End If
        End If
    End With
    
End Function


Private Sub Print_PDF(PDFfullName As String)
    CreateObject("Shell.Application").Namespace(Left(PDFfullName, InStrRev(PDFfullName, "\"))).Items.Item(Mid(PDFfullName, InStrRev(PDFfullName, "\") + 1)).InvokeVerb "Print"
End Sub

 

[Shahin Jack]

Its working Properly thank a lot, You are a great. Actually my Pdf file link is create by =HYPERLINK formulas.