Hyperlinks

[lizemi]

Hi I found this VBA code that opens PDF hyperlinks and prints them
It works perfectly
I was wondering if it can be changed to instead of printing it to the printer it saves it to PDF creating a pdf file of the hyperlinks.
So basically I want to put hyperlinks to pdf documents that needs to print to pdf resulting in al the hyperlinks being merged into one new pdf document

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

 

[bobsan42]

Immediate answer - "No". Not in this way, anyway.
1. "print" will invoke printing to the default system printer.
2. It offers no control of the new process, afaik
3. I am no sure there are still "PDF printers" which would allow you to merge the produced files. (To be honest I know of only one such tool, but it was long ago and it is probably not supported anymore)

A better and easier way to do it would be to use another tool, which is focused on PDF processing and allows more options and control.
one example is ghostscript which has plenty of command line options and allows scripting.

'an example of a command line string
    strcommand = """C:\Program Files\gs\gs9.53.3\bin\gswin64c.exe""" & _
                " -sDEVICE=pdfwrite -dNOPAUSE -dBATCH -sOutputFile=""" & _
                target_file & """" & _
                " """ & input_file_1 & """," & _
                " """ & input_file_2 & """," & _
                " """ & input_file_3 & """," & _
                " """ & input_file_4 ' ... etc.
'or with a list of input files in a file
    strcommand = """C:\Program Files\gs\gs9.53.3\bin\gswin64c.exe""" & _
                " -sDEVICE=pdfwrite -dNOPAUSE -dBATCH -sOutputFile=""" & _
                target_file & """" & _
                " @""" & input_files_list & """"


'then you execute the command like this:
CreateObject("Wscript.Shell").Exec(strCommand)

'or run it like this:
CreateObject("WScript.Shell").Run strCommand, 0, True