CODEWITHSUNDEEP
vbaexcelpdfprinting
petgiraffe19
petgiraffe19

Reputation: 23

Automate PDF creation to include hyperlinks

I've looked all over the internet for this. So far I know that:

VBA itself cannot work in a pdf file. VBA is a Microsoft proprietary programming language that only works in some Microsoft products like Office, and the pdf format is not from Microsoft and does not support VBA.

Because printing to PDF involves third party software, be it Adobe or another PDF printer, Excel, as far as I know, cannot send the file name and location directly to the PDF printer.

Therefore .ExportAsFixedFormat will NOT include hyperlinks in the final pdf.

Is there ANY way, at all, using other software or coding or anything, to create a pdf that WILL include the hyperlinks, other than by creating it manually. i.e. How can I automate the pdf creation to include the hyperlinks? I have tried Excel 2016 and 2010.

We have software to automate the creation of an excel report daily, saves the file, saves the pdf, and emails out to people. I know you can write scripts in Adobe, but how can I get those to run daily by itself? Or maybe some other software can do this?

Any ideas would be greatly appreciated.

Upvotes: 2

Views: 3056

Answers (2)

Mads
Mads

Reputation: 31

Like petgiraffe19 I experienced complications using the Hyperlink function and the PDF-export simultaneously. My solution was to create the hyperlink through VBA like:

Worksheets("Sheet1").Hyperlinks.Add Anchor:=Range("A1"), _
Address:="https://stackoverflow.com", _
ScreenTip:="Link to stackoverflow", _
TextToDisplay:="Link"

Dependent on the font settings it might be necessary to reset those, which also can be done through VBA:

With Range("A1").Font
    .Name = "Calibri"
    .Size = 9
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
End With

Finally the ExportAsFixedFormat can be used for export, which includes the cell with a hyperlink.

Upvotes: 3

Jeremy
Jeremy

Reputation: 1337

Using the below code, hyperlinks work for me on PDF:

Sub SaveAsPDF()
Dim sPath As String

sPath = Environ("Temp") & "\"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sPath & "File name here" & ".pdf", Quality:= _
xlQualityStandard, includedocproperties:=False, ignoreprintareas:=False, _
openafterpublish:=False

ActiveWorkbook.Save

End Sub

Upvotes: 0

Related Questions