Excel to PDF - Hyperlink Problems 1

[dobber]

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

 

[SkipVought]

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,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]