K
Koulla
Hi I want help
I use following code VB code in Excel to create a pdf file.
Public Sub CreatePdf()
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
ActiveWorkbook.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
This work ok and results is ok !! BUT I want bookmarks to be added in pdf
and it doesn't do that. If I do the convertion of xls to pdf manually
bookmarks is added ! And bookmarks is very important to me to be added !!!
Do I have to make any changes in my code or any additions in order bookmarks
to be added ?? I use PDFMakre and as I check the properties bookmarkes is
selected to be added ! So where is the problem ?? Maybe is the command
printout that doesn't support bookmarks?
Is there any other way to add bookmarks with VB in Excel to Acrobat 7 ?
Please help me is important for me !
Thanks in advance
I use following code VB code in Excel to create a pdf file.
Public Sub CreatePdf()
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
ActiveWorkbook.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
This work ok and results is ok !! BUT I want bookmarks to be added in pdf
and it doesn't do that. If I do the convertion of xls to pdf manually
bookmarks is added ! And bookmarks is very important to me to be added !!!
Do I have to make any changes in my code or any additions in order bookmarks
to be added ?? I use PDFMakre and as I check the properties bookmarkes is
selected to be added ! So where is the problem ?? Maybe is the command
printout that doesn't support bookmarks?
Is there any other way to add bookmarks with VB in Excel to Acrobat 7 ?
Please help me is important for me !
Thanks in advance