Excel to PDF Using Visual Basic
Excel to PDF Using Visual Basic
(OP)
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").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.
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").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!
RE: Excel to PDF Using Visual Basic
Thanks,
David Rock
RE: Excel to PDF Using Visual Basic
Game Over, Man!
RE: Excel to PDF Using Visual Basic
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
RE: Excel to PDF Using Visual Basic
-- 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?
RE: Excel to PDF Using Visual Basic
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!
RE: Excel to PDF Using Visual Basic
Thanks
RE: Excel to PDF Using Visual Basic
Thanks for the initial script - it got me to where I needed to be finally.
RE: Excel to PDF Using Visual Basic
I hope this has not caused anyone else any undue grief.
Game Over, Man!
RE: Excel to PDF Using Visual Basic
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
RE: Excel to PDF Using Visual Basic
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.
RE: Excel to PDF Using Visual Basic
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 ?
RE: Excel to PDF Using Visual Basic
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.
RE: Excel to PDF Using Visual Basic
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
RE: Excel to PDF Using Visual Basic
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!
RE: Excel to PDF Using Visual Basic
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
RE: Excel to PDF Using Visual Basic
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.
RE: Excel to PDF Using Visual Basic
myPDF.FileToPDF strMenuFilePS, strMenuFilePDF, ""
what does strMenuFilePS and strMenuFilePDF equal?
RE: Excel to PDF Using Visual Basic
RE: Excel to PDF Using Visual Basic
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.
RE: Excel to PDF Using Visual Basic
Application.SendKeys ("c:\myPostScript.ps{TAB}~")
It now works fine.
RE: Excel to PDF Using Visual Basic
RE: Excel to PDF Using Visual Basic
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!
RE: Excel to PDF Using Visual Basic
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
RE: Excel to PDF Using Visual Basic
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
RE: Excel to PDF Using Visual Basic
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
RE: Excel to PDF Using Visual Basic
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
RE: Excel to PDF Using Visual Basic
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
RE: Excel to PDF Using Visual Basic
RE: Excel to PDF Using Visual Basic
Kayhan
PAGOS INC.
RE: Excel to PDF Using Visual Basic
RE: Excel to PDF Using Visual Basic
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
RE: Excel to PDF Using Visual Basic
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