×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel to PDF - Hyperlink Problems

Excel to PDF - Hyperlink Problems

Excel to PDF - Hyperlink Problems

(OP)
We have an excel worksheet (running Office 2003 Pro) for which we have written VBA (Excel) code, placed behind a button on the worksheet.  When the button is clicked, the Excel worksheet is converted into a PDF file.  This works fine, except the hyperlinks (links to other PDF files, Word docs, etc.)placed in the spreadsheet, will NOT work from within the created pdf file.  

HOWEVER, if we convert the same worksheet to a PDF file using Adobe Pro 7's PDFMaker (via the Adobe menu button within Excel), all the hyperlinks work correctly from within the created PDF file.  Why the difference?  Does anyone know the code that resides behind the actual Adobe PDFMaker menu button, within Excel?

Below is the code we use to create the PDF file, from behind our own created button, within Excel:

/code
Private Sub cmdCreatePdf_Click()
On Error GoTo err_cmdCreatePdf_Click
'Creates .pdf from current worksheet.
'Derives path & file name from worksheet properties.

    Dim strPsFileName As String
    Dim strPdfFileName As String
    Dim strPsFileNameDoubleQuotes As String
    Dim strPdfFileNameDoubleQuotes As String
    
    Dim strFullWorkbookNameActiveFolder As String
    Dim strAuditFolder As String
    Dim strFileName As String
    
    Dim strDefaultActivePrinter As String
    Dim strDistillerCall As String
    Dim ReturnValue As Variant
    
    Dim intStartPositionOfWorksheetName As Integer
    Dim intEndPositionOfWorksheetName As Integer
    Dim intLengthOfFileName As Integer
    
    ' First a PS file must be created. Then PS will be converted to PDF
    ' Uncheck "Do not send fonts to Distiller" option in the Distiller properties
    ' Define the postscript and .pdf file names.
    
    
    'Briefly displays form to let user know the adobe file is being created.
    UserForms.Add("ShowWorking").Show
    
    ' Saves the workbook before creating .pdf
    ActiveWorkbook.Save
    
    'ActiveWorkbook.Name property is the worksheet file name including path & .xls extension
    strfullActiveWorkbookName = ActiveWorkbook.FullName
    
    ' Substring commands to extract path of audit & workbook filename
    intStartPositionOfWorksheetName = InStrRev(strfullActiveWorkbookName, "\") + 1
    intEndPositionOfWorksheetName = InStrRev(strfullActiveWorkbookName, ".xls") - 1
    intLengthOfFileName = (intEndPositionOfWorksheetName - intStartPositionOfWorksheetName) + 1
    
    strAuditFolder = Left(strfullActiveWorkbookName, (intStartPositionOfWorksheetName - 1))
    
    strFileName = Mid(strfullActiveWorkbookName, intStartPositionOfWorksheetName, intLengthOfFileName)
    
    'Stores filename of the temporary postscript to variable.
    strPsFileName = strAuditFolder & "TempPsFile.ps"
    
    ' Stores filename of .pdf to variable.
    strPdfFileName = strAuditFolder & strFileName & ".pdf"
    
    
  '  MsgBox Prompt:="Expect a short delay while (" & strPdfFileName & ") is created", Title:="PDF Creation"
    
    'Stores user's default active printer for workbook for later restoration.
    strDefaultActivePrinter = Application.ActivePrinter
    
    'Prints to postscript file
    ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True, _
     ActivePrinter:="Adobe PDF", _
     PrintToFile:=True, PrToFileName:=strPsFileName
    
    'Restores user's active printer to former setting.
    Application.ActivePrinter = strDefaultActivePrinter
    
    'Add double quotes around the PS filename and PDF filename
    'necessary for Adobe Distiller call below.
    strPsFileNameDoubleQuotes = Chr(34) & strPsFileName & Chr(34)
    strPdfFileNameDoubleQuotes = Chr(34) & strPdfFileName & Chr(34)
           
    'Call the Acrobat Distiller to distill the PS file. ReturnValue is zero
    'if the application doesn't open correctly:
    DistillerCall = "c:\Program Files\Adobe\Acrobat 7.0\Distillr\Acrodist.exe" & " /n /q /o" & strPdfFileNameDoubleQuotes & " " & strPsFileNameDoubleQuotes
    ReturnValue = Shell(DistillerCall, vbNormalFocus)
      
    If ReturnValue = 0 Then MsgBox "Creation of " & PdfFileName & "failed."
 
exit_cmdCreatePdf_Click:
    
    Exit Sub
  
err_cmdCreatePdf_Click:
    
    MsgBox Err.Description & " (" & Err.Number & ")"
    GoTo exit_cmdCreatePdf_Click

End Sub
/code

RE: Excel to PDF - Hyperlink Problems



Hi,

I don't know anything for sure, but here's a hint.

There may be some Adobe Object(s) that are required to create a hyperlink.

Does your Excel VBA have a reference set to the appropriate Adobe Object Library?

Skip,

glasses Be Advised! The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close