Shahin Jack
New Member
- Joined
- Feb 6, 2023
- Messages
- 3
- Office Version
- 2013
- Platform
- Windows
Hello Everyone . I want to print my hyperlinked all pdf file by 1 Click by using VBA.
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