Hello,
I now there was much entries over this theme.
I selected the way over Distiller and PS-File.
The Code see below or in Forum
http://p2p.wrox.com/archive/vba_excel/2002-11/37.asp

I activated the Distiller library
and checked off the "Do not send fonts to Distiller" option.
It works correct until it comes to the Line
    Set myPDF = New PdfDistiller
then it breaks with error 429 -can not creat object through Active-X component - or like that. (I do not have the original english message syntax - only in German language)
I activated following additional Libraries:
- VB for applications
- MS Excel 9.0 Object Library
- MS Forms 2.0 - " -
- MS Office 9.0 - " -
- MS DAO 3.6 - " -
- MS Outlook 9.0 - " -
- OLE Automation and last but not least
- Acrobat Distiller
My Versions are Excel 2000 and Acrobat 5.0
I have no more ideas to solve that problem.
Can somebody help me? Thanks!

'***********************************************
' Define the postscript and .pdf file names.
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "d:\temp\myPostScript.ps"
PDFFileName = "d:\temp\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet

MySheet.PrintOut copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, _
prtofilename:=PSFileName

' Convert the postscript file to .pdf

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""
***

I tried this code but I can only run with administrator permissions. Without these permissions I get the following error:
“ActiveX component can't create object or return reference to this object (Error 429)”

Could you give me some tip?
Thanks in advance
Best regards

Under Windows XP (I suppose Windows 2000 too) you need to be Administrator of your PC to use Distiller API.

Power users and simple users receive the error 429 "ActiveX Component Can't Create Object"

I have the same problem with Windows NT.
It's only work with administrator permissions.

Do you know which permissions I have to set (maybe on database register) on simple users profil to don't have the 429 error with API Distiller ?

First, is it possible ?

Thanks in advance.

Hi All,

I use Win2K, not local admin and this code works fine.

However, I'm embedding it into a loop to print 23 XL books (active sheet only) and I get the following error on sheet 2;

Method 'FileToPDF' of object 'IPdfDistller' failed.

Anyone else had this? Individually the code works fine, but you'd might as well print each sheet manually if that were the case!

I'm guessing that at the Distiller buffer needs to be empty before attempting to print the next file, anyone have any ideas?

I've added a code to tag the PS file with the loop counter, creating 23 PS files as a start to debug, but I don't really understand the error message so it's a bit 'stab in the dark' until I can find some documentation on this.

Cheers,

Jon

Hi JonTout,

I am trying to write a script that sounds nearly identical to yours. I am working on a VBA script to generate PDFs from all of the worksheets in an Excel file, without User Interaction. I use Win2K as well, but I cannot even convert the first Excel sheet to PDF. I have Administrator priviledges.

When I run the script listed above, the script halts at this line:
"Dim myPDF As PdfDistiller"
The error states:
"Compile error: User-defined type not defined"

You said that you were able to generate the first page. Could you post your script online so that I could compare it to mine and see how you were at least able to get 1 page out of it.

Much thanks in advance!

Gary

Hello,
can you tell me how I could use the third parameter in the command:
myPDF.FileToPDF PSFileName, PDFFileName, ""

I try to find a way to preserve my URLs in the excel file and transfer them into the pdf without manually recreating them in Acrobat. The only way that seems to work is via the Acrobat menu in Excel, but then I have to input the file name which doesn't work for batch processes.

Thanks
Frank

Hi there,

This problem is very annoying!!!!!

To solve take "Set myPDF = New PdfDistiller" out of the Loop so it is called once. Also put "DoEvents" before and after the conversion line ("myPDF.FiletoPDF PSFileName, PDFFileName, "") to clear the stack.

Distiller sucks. oh well.

Hi

I wanted to print custom views to pdf and used the hints above and the following to get it to work. It worked just great - I used it to put out about 100 pdf file 4 times.

 - Create some custom views.
 - Put in references in the VBA to Acrobat reader
 - Need adobe writer 7 or 8
 - Go to system printers and right click ‘Adobe PDF’ , select ‘Printer Preferences’, uncheck ‘Rely on system fonts only, do not use document fonts’


Put the following code in the module
Sub printpdfs1()
 Dim path1 As String
 Dim model1 As String

 Dim filename As String

 path1 = GetCostModelPath()
 model1 = "d"

  filename = path1 & "Tables\ps\table18" & model1 & "_p1.ps"
  Call Printpdfs2("CapDepr_p1", filename)
  filename = path1 & "Tables\ps\table18" & model1 & "_p2.ps"
  Call Printpdfs2("CapDepr_p2", filename)
  filename = path1 & "Tables\ps\table18" & model1 & "_p3.ps"
  Call Printpdfs2("CapDepr_p3", filename)
  filename = path1 & "Tables\ps\table18" & model1 & "_p4.ps"
  Call Printpdfs2("CapDepr_p4", filename)
  filename = path1 & "Tables\ps\table20" & model1 & "_p1.ps"
  Call Printpdfs2("EstOwnOprCost_p1", filename)

End Sub

Function Printpdfs2(ShNameView As String, PSFileName As String)
' Define the postscript and .pdf file names.
' Print the Excel range to the postscript file
'put references in excel to adobe and acrobat reader
'go control panel> printers>right click on ADOBE and then desect the automatically use fonts options

Application.ScreenUpdating = False
ActiveWorkbook.CustomViews(ShNameView).Show

Dim mysheet As Worksheet

ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, _
ActivePrinter:="Adobe PDF", printtofile:=True, collate:=True, _
prtofilename:=PSFileName

' Convert the postscript file to .pdf

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
Dim PDFFileName As String
PDFFileName = Mid(PSFileName, 1, Len(PSFileName) - 2) & "pdf"

myPDF.FileToPDF PSFileName, PDFFileName, ""
'***

End Function

Function GetCostModelPath() As String
'Returns the current place where the model is
'needs to be set manually

GetCostModelPath = "C:\projs\costs_V2\"
End Function