Excel to PDF Using Visual Basic 11

[JabbaTheNut]

I have used the following VB code to print an Excel range to an Adobe Acrobat .PDF file. The steps to accomplish this are as follows:

1. Print the range to a postscript file using Acrobat Distiller
2. Convert postscript file to .PDF using Acrobat Distiller API.

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.

In the Excel Visual Basic Editor, make sure you include a reference to Acrobat Distiller.

Here is the code....

*********************************************
Private Sub CommandButton1_Click()

' 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
MySheet.Range("myRange&quot.PrintOut copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller", 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

And that is all there is to it! I hope this tip is helpful to some of you. Game Over, Man!

 

[DavidRock]

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

 

[JabbaTheNut]

Can you post the code you are using? Game Over, Man!

 

[DavidRock]

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&quot.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

 

[mj4118]

"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."

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

 

[JabbaTheNut]

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:&quot. 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&quot.Activate
Set MyWkSht = ActiveSheet

I don't know if this difference would cause your error. Game Over, Man!

 

[DavidRock]

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

 

[DavidRock]

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

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

 

[JabbaTheNut]

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!

 

[accessguy52]

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

http://www.aspensys.com

 

[mj4118]

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.

 

[beginnerloulou]

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 ?

 

[fzylgk]

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.

 

[fzylgk]

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&quot

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&quot
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

 

[JabbaTheNut]

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!

 

[vjysing]

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

 

[fzylgk]

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.

 

[chadau]

In this line of code:

myPDF.FileToPDF strMenuFilePS, strMenuFilePDF, ""

what does strMenuFilePS and strMenuFilePDF equal?

 

[chadau]

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

 

[azh]

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

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.