How to Print multiple Hyperlink pdf file using VBA?

Shahin Jack

New Member
Joined
Feb 6, 2023
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Hello Everyone . I want to print my hyperlinked all pdf file by 1 Click by using VBA.
Capture.JPG
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
What type of hyperlinks are they? =HYPERLINK formulas or inserted via the Insert Hyperlink dialogue?
 
Upvote 0
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 .
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
Its working Properly thank a lot, You are a great. Actually my Pdf file link is create by =HYPERLINK formulas.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,189
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top