When I try to run this from Excel 2000 I'm getting an error "Named argument not found" and it's referring to the prttofilename argument. I've got "Do not send fonts to distiller" unchecked and I'm using Distiller 5.0. Any idea what could be causing this?

Thanks,
David Rock

Can you post the code you are using?

Game Over, Man!

I'm using Office 2K Pro SR-2 on Windows 2K Pro SP3 and have Acrobat patched to 5.0.5 if that helps any. This is the code:

Public Sub Distill_it()

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

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

    MySheet.Range("A1:E4").PrintOut copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller on Ne01:", printtofile:=True, collate:=True, prttofilename:=PSFileName

    ' Convert the postscript file to .pdf
    Dim myPDF As PdfDistiller
    Set myPDF = New PdfDistiller
    myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

Thanks

"Make sure that you uncheck the "Do not send fonts to Distiller" option in the Distiller properties.  You do this by entering the Distiller properties>>General Tab>>Printing Preferences>>Adobe PDF Settings and uncheck the "Do not send fonts to Distiller" option.  You will get an error if you don't do this."

-- How would someone do this in VBA so that one would not have to point and click each time they wanted to print something to Distiller?

David Rock,

It sounds like an Excel issue.  The "prttofilename:=" is an Excel paramater for printing to a postscript file.  The Acrobat Distiller is not used until after Excel prints to the postscript file.

Your setup appears to be almost identical to mine.  I did notice that in my code (not the code I posted above) I used ActivePrinter:="Acrobat Distiller" instead of what I posted above (ActivePrinter:="Acrobat Distiller on Ne01:").  Give that a try.  One other difference is that I activated the worksheet that I intended to print.  For example...

Dim MyWkSht As Worksheet
Worksheets("Sheet1").Activate
Set MyWkSht = ActiveSheet

I don't know if this difference would cause your error.

Game Over, Man!

I could not get past the error so I used "sendkeys" to pump the .ps filename into the keyboard buffer for when Distiller asked for it and it seems to be working fine.

Thanks

Geez - I just stumbled across the answer to my problem: "prttofilename" should be "prtofilename" (no extra "t").

Thanks for the initial script - it got me to where I needed to be finally.

Awesome!  Your right.  I checked my original code and it is "prtofilename".  I'm sorry for the mistype.  My bad :{

I hope this has not caused anyone else any undue grief.

Game Over, Man!

Jabba- I've found your thread on converting Excel to pdf- congratulations! I tried it and it really does work! But I and probably some others on this forum, have been trying to use that code to saving/converting Access reports to pdf (for the client, you know).

 The trouble is with the ActivePrinter object and method. Works in Excel but I've been spending all day trying to find the Access alternative to "ActivePrinter". The closest I can find in Access is Docmd.Printout. However, it lacks the parameters that ActivePrinter has in Excel. Any suggestions from you or anyone else for a workaround?

Thanks, I'll check back later.

accessguy52
www.aspensys.com

Jabba, your tips have literally saved my hours upon hours of hard labor.  However, lately, during execution of the macro, I get an error message telling me to make sure the "Do not send fonts to Distiller" item is unchecked.  

Per your instructions, I did do that before running the macro, but it mysteriously turns itself on while it is running.

Is anyone else having this issue?  If so, then is there anyway to have this item unchecked within the code?

Thanks.

Hi everybody,
I would like to use your code, but it does not run with me. I have the following message :

PdfDistiller
"Erreur de compilation : Type défini par l'utilisateur non défini"

Somebody can help me ?

I had this working beautifully in 4.0, but recently upgraded to 5.0. I was using the PDFWriter printer in 4.0, but now I have to use Distiller. I'm not sure why they got rid of the PDFWriter printer?

I haven't tried Jabba's code yet (will right after I post this), but I do know if you try to change the Distiller options throught Excel, they will only work for the current session. In order for the changes to take permanently, you must change the printer options through the Start->Settings->Printers menu. Here you can uncheck the "Do not send fonts to Distiller" option.

Update: Jabba, thanks much, it worked perfectly.

BTW: I use a Visual Basic Executable instead of VBA. Example code is below if interested:

Public Function ConvertFile(strSourceFileName As String) As Boolean
    On Error GoTo ErrorHandler
    
    Dim msExcel As Excel.Application
    Set msExcel = GetObject(Class:="Excel.Application")
    
    Dim p As String
    p = "Acrobat Distiller on Ne00:"
    
    msExcel.Visible = False
    msExcel.Workbooks.Open strSourceFileName
    
    msExcel.ActiveWorkbook.PrintOut ActivePrinter:=p, PrintToFile:=True, PrToFileName:=strMenuFilePS
    
    Dim myPDF As PdfDistiller
    Set myPDF = New PdfDistiller
    myPDF.FileToPDF strMenuFilePS, strMenuFilePDF, ""
    Set myPDF = Nothing
    
    msExcel.ActiveWorkbook.Close False
    
    ' Should check and quit excel when done
    msExcel.Quit
    Set msExcel = Nothing
    ConvertFile = True
Exit Function

ErrorHandler:
    ' Create Excel for the first time if it is not active
    If Err.Number = 429 Then
        Set msExcel = CreateObject("Excel.Application")
        Err.Clear ' Clear Err object in case error occurred.
        Resume
    End If

    ' All other errors handled here
    If IsCriticalError Then
        ConvertFile = False
        Exit Function
    Else
        Resume
    End If
End Function

Fuzzy...

Your VB example is great. I can think of two situations that I am currently facing for which I can use your example.

Thanks :)

Game Over, Man!

Hi fzylgk,

i tried using the code in VB and still am getting the error
"Named argument not found".

if i remove that "PrToFileName" then it will prompt for save as dialog.....

something wrong?

pls guide me..
ofcourse i changed the setting in Distiller.

thx in advance

vjysing,

When you're writing the "msExcel.ActiveWorkbook.PrintOut" line, does VB show the function definition like below?

PrintOut([From], [To], [Copies], [Preview], [ActivePrinter], [PrintToFile], [Collate], [PrToFileName]).

I'm not really sure why you can't reference the PrToFileName parameter unless you're referencing a different version of Excel.

I don't remember what exact references you will need, but I have the following Project References:

Visual Basic For Applications
OLE Automation
Microsoft Excel 9.0 Object Library

I have some others, but I don't think they're relevant to this problem.

In this line of code:

  myPDF.FileToPDF strMenuFilePS, strMenuFilePDF, ""

what does strMenuFilePS and strMenuFilePDF equal?

Can this code be used to convert .tif files to .pdf?

I had to add the following line before the .PrintOut line to get it working in Excel 97:
Application.SendKeys ("c:\myPDF.pdf{TAB}~")

The VBA help in Excel 97 says:
PrintToFile   Optional Variant. True to print to a file. Microsoft Excel prompts the user to enter the name of the output file. There's no way to specify the name of the output file from Visual Basic.

The sendkeys statement gets around this issue.  However, I still get the following error message when trying to open the PDF file:
There was an error opening this document. File does not begin with '%PDF-'.

Has anyone else come across this issue?  I'm using Excel 97 sr2, NT4, Adobe Acrobat v4.05.

Thanks.

I used the wrong filename is the send keys command.  It should read:
Application.SendKeys ("c:\myPostScript.ps{TAB}~")

It now works fine.

After compiling a vb code and copy the applicqtion on an other computer, no error message is sent and thr program stay in a kind of loop on "mypdf.filetopdf".  The references have been had to my project and the computer has all the same application except not install in the same path.  Do you know what's the problem???

I resolve my problem.
If your using this code on WIN Nt 4.0, you need to be an administrator for the computer, otherwise the program will stall on the line

    Set myPDF = New PdfDistiller
or
    myPDF.FileToPDF strMenuFilePS, strMenuFilePDF, ""*

thanks!

Hello guys.. can someone help me with my simular problem.

I am using VB 6.0 I have some PDF files that I need to print, but i want them to print in a certain order. When I send them to the windows print spool they dont always print in the same order send them in. i want to create a postscript file, that contains all of my PDF's, so I can send that file to the printer.

I have no idea where to start, I have never worked with PS files before. Any suggestions would be appreciated.

Thank you

Hi JabbaTheNut,

It's been almost 2 years since you helped me with my previous question, it was really helpful and I appreciate it very much.

Now I have another mission and would like your expert help.  How do I print mulitple pages to the same pdf file?
Using your previous code, I was able to print one page, but now I have muliptle pages to print to the same pdf output file, the following code would only give me the last page in the pdf file!

Here is part of my vba code:

'   Print page to PDF File
     
       PSFileName = "U:\1Tony\pdf\" & CustNo & "Ps"
       PDFFileName = "U:\1Tony\pdf\" & CustNo & ".pdf"
'      Print Invoice Detail page
       Worksheets("InvDetail").Select
       ActiveSheet.PrintOut Copies:=1, ActivePrinter:=PTR,   Collate:=True, _
         PrintToFile:=True, PrToFileName:=PSFileName
'      Convert the postscript file to .pdf
'      Print Remittance Summary Page
       Worksheets("RemitSum").Select
       ActiveSheet.PrintOut Copies:=1, ActivePrinter:=PTR, Collate:=True, _
         PrintToFile:=True, PrToFileName:=PSFileName
       Set myPDF = New PdfDistiller
       myPDF.FileToPDF PSFileName, PDFFileName, ""
        
Looking forward to your early reply....
Thanks and regards
Tony

Hi Tony.

It pretty simple. Just select multiple sheets first.

Sheets(array("abc", "def", "ghi")).select

Then change your .printout line slightly.

ActiveWindow.SelectedSheets.PrintOut ...

BY THE WAY:

I think it's MUCH easier to just change the activeprinter to the PDFWriter. It's just a printer driver that creates a PDF output to the file you designate. No need to convert from tiff to pdf, etc. in further steps.

If you don't SEE PDFWriter in Acrobat 5.0, it's because Adboe changed the default to Distiller. You have to choose CUSTOM install and pick the PDFWriter. I find it MUCH easier to use than Distiller. Distiller just has too many settings and things to go wrong.

Ted

Dear Jabba,
I'm using your really great piece of code. Like so many others, I spent countless hours testing, then surfing, then finally finding your solution. I am having a slight problem still though.

1) I've uncheched the box in my Distiller settings to not send fonts.
2) I'm still getting the related error message.
3) Attached is my/your code:

Private Sub tektip1()
' *******
' From "JabbaThe Hut"

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

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet
ActiveWorkbook.PrintOut from:=11, To:=17, 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, ""

End Sub

Hi Jabba,
Thanks for this thread its working fine for me.Great help.I got an issue though as I try to run in one of the m/c its giving me an error
Active X cannot create object while instancing pdf distiller.
The same code is working on other m/c
Please help
Thank you for your help.
Regards
Jagan

wow.  Excellent thred here, folks.

Hi people, I use the gs632 API (gs32.dll postscript writer) for free instead of distiller, and to join several ps files with vb. (open byte). But when printing, I set the visibilty of the excel object to false, its ok, however, the printing... dialog still shows up? how can I make it invisible? (I have tried excelobject.displayalerts =false but didnt work? )

Kayhan
PAGOS INC.

This thread has almost everything I have been searching for, save one.  I have a single excel file that acts as a GUI to pick and choose which excel document(s) to print.  These choices are all seperate excel files that contain one worksheet only.  Could anyone help me to figure out how I can print multiple excel files into one single PDF?  Can this even be done without have to append each printed file?  Any and all ideas are much appreciated!

Hi Jabba,

I have modified your EXCEL to PDF Visual Basic Code so I can loop through a list of several excel spreadsheets using a simple for next loop. For some reason after I run the first pass (the first excel file) when I go to run the 2nd loop I get the following error.

Run-time error ‘-2147417851 (80010105)’:
Method ‘FileToPDF of object ‘IPdfDistiler’failed

Do you have a copy of your code that loops through a series of excel spreadsheet names using a for next loop? My code is listed below. I am not sure what is wrong with the myPDF.FileToPDF PSFileName, PDFFileName, "" code. I would appreciate any help you could provide


Public Sub Distill_it_Modified()
    
    Dim q As Integer
    Dim TotNumFiles As Integer
    
    Dim zPSFileName As String
    Dim zLogFileName As String
    Dim zPDFFileName As String
    
    Dim ExcelFileName As String
    Dim PSFileName As String
    Dim LogFileName As String
    Dim PDFFileName As String
    
    Dim LocationExcelFile As String
    Dim LocationPSFile As String
    Dim LocationLOGFile As String
    Dim LocationPDFFile As String
   
    Dim Output_Sheet As String
    Dim myPDF As PdfDistiller
     
    TotNumFiles = Range("TotNumFiles").Value
    Range("CurrentFile").Value = 0
    Application.Calculate
    
    For q = 2 To TotNumFiles
        Application.ScreenUpdating = False
         Range("CurrentFile").Value = q
        Application.Calculate
        Output_Sheet = Range("Output_Sheet").Value
        ExcelFileName = Range("ExcelFileName").Value
        zPSFileName = Range("PSFileName").Value
        zLogFileName = Range("LogFileName").Value
        zPDFFileName = Range("PDFFileName").Value
    
        LocationExcelFile = Range("LocationExcelFile").Value
        LocationPSFile = Range("LocationPSFile").Value
        LocationLOGFile = Range("LocationLOGFile").Value
        LocationPDFFile = Range("LocationPDFFile").Value
   
        PSFileName = LocationPSFile & zPSFileName
        LogFileName = LocationLOGFile & zLogFileName
        PDFFileName = LocationPDFFile & zPDFFileName
    
        'Open Excel File
        ChDir LocationExcelFile
        Workbooks.Open Filename:=LocationExcelFile & ExcelFileName
        Sheets(Output_Sheet).Activate
        
        ' Print the Excel range to the postscript file
        Dim MySheet As Worksheet
        Set MySheet = ActiveSheet

        ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller on Ne01:", printtofile:=True, Collate:=True, prtofilename:=PSFileName

    ' Convert the postscript file to .pdf
   
    Set myPDF = New PdfDistiller
    myPDF.FileToPDF PSFileName, PDFFileName, ""
     ActiveWorkbook.Close
    Kill PSFileName
    Kill LogFileName
 
     Application.Calculate
    Next q
    Application.ScreenUpdating = True
    
End Sub

Hi Jabba,

I have modified your code to run as a for next loop so I can loop through several spreadsheets at once. However, I seem to get an error with the line:
myPDF.FileToPDF PSFileName, PDFFileName, ""

The error I get is as follows:
Microsoft Visual Basic
Run-time error ‘-2147417851 (80010105)’:
Method ‘FileToPDF of on ject ‘IPdfDistiler’failed

The macro seems to run fine for the first loop and creates the PDF, but then bombs out on loops 2. Can you post a version of your code with a simple for next loop to show how to correct the problem. My code is listed below. I appreciate your help.

My code is as follows:
Public Sub Distill_it()
    
    Dim q As Integer
    Dim TotNumFiles As Integer
    
    Dim zPSFileName As String
    Dim zLogFileName As String
    Dim zPDFFileName As String
    
    Dim ExcelFileName As String
    Dim PSFileName As String
    Dim LogFileName As String
    Dim PDFFileName As String
    
    Dim LocationExcelFile As String
    Dim LocationPSFile As String
    Dim LocationLOGFile As String
    Dim LocationPDFFile As String
   
    Dim Output_Sheet As String
    Dim myPDF As PdfDistiller
     
    TotNumFiles = Range("TotNumFiles").Value
    Range("CurrentFile").Value = 0
    Application.Calculate
    
    For q = 2 To TotNumFiles
        Application.ScreenUpdating = False
         Range("CurrentFile").Value = q
        Application.Calculate
        Output_Sheet = Range("Output_Sheet").Value
        ExcelFileName = Range("ExcelFileName").Value
        zPSFileName = Range("PSFileName").Value
        zLogFileName = Range("LogFileName").Value
        zPDFFileName = Range("PDFFileName").Value
    
        LocationExcelFile = Range("LocationExcelFile").Value
        LocationPSFile = Range("LocationPSFile").Value
        LocationLOGFile = Range("LocationLOGFile").Value
        LocationPDFFile = Range("LocationPDFFile").Value
   
        PSFileName = LocationPSFile & zPSFileName
        LogFileName = LocationLOGFile & zLogFileName
        PDFFileName = LocationPDFFile & zPDFFileName
    
        'Open Excel File
        ChDir LocationExcelFile
        Workbooks.Open Filename:=LocationExcelFile & ExcelFileName
        Sheets(Output_Sheet).Activate
        
        ' Print the Excel range to the postscript file
        Dim MySheet As Worksheet
        Set MySheet = ActiveSheet

        ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller on Ne01:", printtofile:=True, Collate:=True, prtofilename:=PSFileName

    ' Convert the postscript file to .pdf
   
    Set myPDF = New PdfDistiller
    myPDF.FileToPDF PSFileName, PDFFileName, ""
     ActiveWorkbook.Close
    Kill PSFileName
    Kill LogFileName
 
     Application.Calculate
    Next q
    Application.ScreenUpdating = True
    
End Sub