Automatic Printing to a PDF - here's how to do it

SteveF

New Member
Joined
Jun 15, 2004
Messages
27
I've searched the message board and found several questions, but not a good example of how to print automatically from Excel to a PDF file. After a couple of days of searching and tweaking, I've got what I think is a fairly good solution for automating your printing of worksheets to PDF files.

This solution requires the use of PDF995. This is a free utility available at www.PDF995.com. This is a print driver that allows you to print to a PDF file. The file is compatible with Adobe and can be read with the Adobe reader like any other PDF. The free version does pop-up some advertising with each print, but the automation works with around the pop-ups. I believe the full license version is about $10 and doesn't produce the pop-ups.

The challenge in automating a PDF process is that the PDF driver will prompt the user for a filename. This is ok if you are printing just one sheet, but if you need to automate the production of several PDFs, you need to be able to specify the name of the file in the code. The subroutine SheetToPDF presented below allows you to specify a single worksheet and the full filename for the PDF. I've included all the code and external declarations needed. The two subs at the bottom give examples of how to call SheetToPDF with the passed parameters.

I hope you find this helpful.
Regards,
Steve

-----------------------------------------------------
'Needed to Read INI file settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long

'Needed to Write INI file settings
Declare Function WritePrivateProfileString Lib "kernel32" Alias _
"WritePrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpString As Any, _
ByVal lpFileName As String) As Long

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


Sub SheetToPDF(WS As Worksheet, OutputFile As String)

' This subroutine will print a worksheet to a PDF file using PDF995, a free utility
' to generate PDF files. Download it at www.pdf995.com

' Two arguments must be passed into this routine
' 1. WS - A worksheet pointer
' 2. OutputFile - The full path and name of the desired pdf file

' Be sure to check that the "Generating PDF CS" setting in pdfsync.ini is set to 0
' when pdf995 is idle. This codes uses that as a completion flag as it seems to be
' the most reliable indication that PDF995 is done writing the pdf file.

Dim syncfile As String, maxwaittime As Long
Dim iniFileName As String 'tmpPrinter As Printer
Dim x As Long
Dim tmpoutputfile As String, tmpAutoLaunch As String

' set the location of the PDF995.ini and the pdfsync files
iniFileName = "c:\pdf995\res\pdf995.ini"
syncfile = "c:\pdf995\res\pdfsync.ini"

' save current settings from the PDF995.ini file
tmpoutputfile = ReadINIfile("PARAMETERS", "Output File", iniFileName)
tmpAutoLaunch = ReadINIfile("PARAMETERS", "Autolaunch", iniFileName)

' remove previous pdf if it exists
On Error Resume Next
Kill OutputFile
On Error GoTo Cleanup

' setup new values in PDF995.ini
x = WritePrivateProfileString("PARAMETERS", "Output File", OutputFile, iniFileName)
x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", "0", iniFileName)

'print the worksheet
WS.Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PDF995"

' PDF995 operates asynchronously. We need to determine when it is done so we can
' continue. This is done by checking the "Generating PDF CS" parameter in the pdfsync.ini
' file. A loop with a 2 second delay is used to determine when it is finished.

Sleep (2000) ' pause 2 seconds (1000 = 1 sec)
maxwaittime = 60000 'If pdf995 isn't done in 60 seconds, quit anyway
Do While ReadINIfile("PARAMETERS", "Generating PDF CS", syncfile) = "1" And maxwaittime > 0
Sleep (2000) ' pause 2 seconds and re-check the status
maxwaittime = maxwaittime - 2000
Loop

' restore the original default printer and the PDF995.ini settings
Cleanup:
x = WritePrivateProfileString("PARAMETERS", "Output File", tmpoutputfile, iniFileName)
x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", tmpAutoLaunch, iniFileName)
x = WritePrivateProfileString("PARAMETERS", "Launch", "", iniFileName)
On Error Resume Next


End Sub

Function ReadINIfile(sSection As String, sEntry As String, sFilename As String) As String
Dim x As Long
Dim sDefault As String
Dim sRetBuf As String, iLenBuf As Integer
Dim sValue As String

'Six arguments
'Explanation of arguments:
'sSection: ini file section (always between brackets)
'sEntry : word on left side of "=" sign
'sDefault$: value returned if function is unsuccessful
'sRetBuf$ : the value you're looking for will be copied to this buffer string
'iLenBuf% : Length in characters of the buffer string
'sFileName: Path to the ini file

sDefault$ = ""
sRetBuf$ = String$(256, 0) '256 null characters
iLenBuf% = Len(sRetBuf$)
x = GetPrivateProfileString(sSection, sEntry, _
sDefault$, sRetBuf$, iLenBuf%, sFilename)
ReadINIfile = Left$(sRetBuf$, x)

End Function


Sub PrintToPDF()
' This example prints the first sheet of the workbook. It calls the SheetToPDF subroutine,
' passing it the worksheet pointer, and the PDFFileName (the worksheet name + .pdf)

Dim PDFFileName As String
PDFFileName = "c:\temp\" & Sheets(1).Name & ".pdf"
Call SheetToPDF(Sheets(1), PDFFileName)
End Sub


Sub PrintCPSheets()
' This example prints specific named worksheets. It calls the SheetToPDF subroutine one
' time for each sheet, passing it a worksheet pointer, and PDFFileName.

Dim CS As Worksheet
Dim PDFFileName As String

CurrentPath = "c:\temp\"

Set CS = Sheets("West")
PDFFileName = CurrentPath & CS.Name & ".pdf"
Call SheetToPDF(CS, PDFFileName)

Set CS = Sheets("Northeast")
PDFFileName = CurrentPath & CS.Name & ".pdf"
Call SheetToPDF(CS, PDFFileName)

Set CS = Sheets("Northeast")
PDFFileName = CurrentPath & CS.Name & ".pdf"
Call SheetToPDF(CS, PDFFileName)

Set CS = Sheets("Southeast")
PDFFileName = CurrentPath & CS.Name & ".pdf"
Call SheetToPDF(CS, PDFFileName)

Set CS = Sheets("Central")
PDFFileName = CurrentPath & CS.Name & ".pdf"
Call SheetToPDF(CS, PDFFileName)

End Sub
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I started using PDF955 the other day and its a good product!

1 think I noticed wasthat there is code to set the printer:

Application.ActivePrinter = "PDF995 on Ne00:"

not all PC's will have Ne00 as the print setting, some will be Ne02 and so will error when it tries to print the file.

I put a list of Ne00 to Ne10 with an on error resume next.
This gets round this problem

Scott
 
Upvote 0
Scott, I saw that also in one of the examples. So instead of changing the active printer, I just specify the printer in the PrintOut command itself.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PDF995"

I also found that I don't need to specify "on Ne00:" or on anything. It works fine just by specifying "PDF995". If the printer is installed on your PC with a longer name like "PDF995 on Ne00:", just go into the Printer definition in Control Panel and change the name to "PDF995".

Steve
 
Upvote 0
Steve – this was absolutely fantastic. Thanks so much for posting this – like you I hadn’t been able to find an easy solution like this (however, unlike you, I didn’t have the skills to actually write the macro! :wink: )

A couple related questions if you happen to know - I have tried looking for solutions that would work on this newsgroup and elsewhere that would work - but to no avail.

Do you have any ideas of how to modify the macro to:

1. Give the pdf file a Title. And specifically the Title that can be found on the dialog box by right-clicking on the file, then Properties, then Summary tab (which is different than the title generated by this macro). I haven’t been able to figure out how to do this yet except for going back later and modifying through right-clicking and going to Properties.

2. Give the pdf file a security setting (with password) so the pdf can’t be edited (but can be viewed and printed by anyone). Again, I haven’t been able to figure out how to do this except finding the pdf file later and editing the security setting.

I am using Acrobat 6.0 and the Excel plug-in.
Any advice would be greatly appreciated.

Thanks!
 
Upvote 0
Hizzle - thanks for the comments. On your questions:

1. It appears that the default title is the name of the Excel file. The only way to control it from the macro is to create temporarily, a new workbook for each worksheet you are printing. Before printing, make sure to name the workbook whatever you want to show in the PDF title field.

2. Security settings - I don't know how to access these attributes. I would suggest going out to the pdf995.com website and exploring their pdfedit995 product. It lets you modify many attributes of the PDF files. You could also contact their tech support. They were very helpful when I was looking for help with the print module listed above.

Good luck!,
Steve
 
Upvote 0
Steve,

This code is exactly what I was looking for.

I pasted the code in a module and it works....

I can't seem to figure out the "OutputFile" path.

It won't save the document to the specified folder or filename.

Any help would be appreciated.
------------------------------------------------------
'Needed to Read INI file settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long

'Needed to Write INI file settings
Declare Function WritePrivateProfileString Lib "kernel32" Alias _
"WritePrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpString As Any, _
ByVal lpFileName As String) As Long

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


Sub SheetToPDF(WS As Worksheet, OutputFile As String)

' This subroutine will print a worksheet to a PDF file using PDF995, a free utility
' to generate PDF files. Download it at www.pdf995.com

' Two arguments must be passed into this routine
' 1. WS - A worksheet pointer
' 2. OutputFile - The full path and name of the desired pdf file

' Be sure to check that the "Generating PDF CS" setting in pdfsync.ini is set to 0
' when pdf995 is idle. This codes uses that as a completion flag as it seems to be
' the most reliable indication that PDF995 is done writing the pdf file.

Dim syncfile As String, maxwaittime As Long
Dim iniFileName As String 'tmpPrinter As Printer
Dim x As Long
Dim tmpoutputfile As String, tmpAutoLaunch As String

' set the location of the PDF995.ini and the pdfsync files
iniFileName = "c:\pdf995\res\pdf995.ini"
syncfile = "c:\pdf995\res\pdfsync.ini"

' save current settings from the PDF995.ini file
tmpoutputfile = ReadINIfile("PARAMETERS", "C:\Documents and Settings\afrench\Desktop\testing.pdf", iniFileName)
tmpAutoLaunch = ReadINIfile("PARAMETERS", "Autolaunch", iniFileName)

' remove previous pdf if it exists
On Error Resume Next
Kill OutputFile
On Error GoTo Cleanup

' setup new values in PDF995.ini
x = WritePrivateProfileString("PARAMETERS", "C:\Documents and Settings\afrench\Desktop\testing.pdf", "C:\Documents and Settings\afrench\Desktop\testing.pdf", iniFileName)
x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", "0", iniFileName)

'print the worksheet
Sheet8.Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PDF995"

' PDF995 operates asynchronously. We need to determine when it is done so we can
' continue. This is done by checking the "Generating PDF CS" parameter in the pdfsync.ini
' file. A loop with a 2 second delay is used to determine when it is finished.

Sleep (2000) ' pause 2 seconds (1000 = 1 sec)
maxwaittime = 60000 'If pdf995 isn't done in 60 seconds, quit anyway
Do While ReadINIfile("PARAMETERS", "Generating PDF CS", syncfile) = "1" And maxwaittime > 0
Sleep (2000) ' pause 2 seconds and re-check the status
maxwaittime = maxwaittime - 2000
Loop

' restore the original default printer and the PDF995.ini settings
Cleanup:
x = WritePrivateProfileString("PARAMETERS", "C:\Documents and Settings\afrench\Desktop\testing.pdf", tmpoutputfile, iniFileName)
x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", tmpAutoLaunch, iniFileName)
x = WritePrivateProfileString("PARAMETERS", "Launch", "", iniFileName)
On Error Resume Next


End Sub

Function ReadINIfile(sSection As String, sEntry As String, sFilename As String) As String
Dim x As Long
Dim sDefault As String
Dim sRetBuf As String, iLenBuf As Integer
Dim sValue As String

'Six arguments
'Explanation of arguments:
'sSection: ini file section (always between brackets)
'sEntry : word on left side of "=" sign
'sDefault$: value returned if function is unsuccessful
'sRetBuf$ : the value you're looking for will be copied to this buffer string
'iLenBuf% : Length in characters of the buffer string
'sFileName: Path to the ini file

sDefault$ = ""
sRetBuf$ = String$(256, 0) '256 null characters
iLenBuf% = Len(sRetBuf$)
x = GetPrivateProfileString(sSection, sEntry, _
sDefault$, sRetBuf$, iLenBuf%, sFilename)
ReadINIfile = Left$(sRetBuf$, x)

End Function

Sub PrintCPSheets()
' This example prints specific named worksheets. It calls the SheetToPDF subroutine one
' time for each sheet, passing it a worksheet pointer, and PDFFileName.

Dim CS As Worksheet
Dim PDFFileName As String

CurrentPath = "C:\Documents and Settings\afrench\Desktop\"

Set CS = Sheets("NY")
PDFFileName = CurrentPath & CS.Name & ".pdf"
Call SheetToPDF(CS, PDFFileName)

End Sub
 
Upvote 0
Sheet to PDF question

App Solutions,

Does the file get saved anywhere? Have you tried debugging the code? Use F8 to step through the code and make sure that the PDFFileName is what you expect when you make the call to SheetToPDF.

Steve
 
Upvote 0
Steve,

No. That is the problem. When I run the code, even leaving the code the way you have it posted, it will bring up a FileSaveAs box and will require pressing enter on the box. The defaule name it puts in it to save as is the workbook name, not the worksheet name. If I press enter, it will finish the procedure.

Thanks for the quick response....!!!

Adam
 
Upvote 0
Adam,

It sounds like PDF995 is not working with its ini files correctly. Step through the code with F8. Open up the ini files before and after they get changed by the code. Make sure the ini files are being updated correctly. It is possible that they are not in the right folder.

The whole idea of this approach is to avoid hitting the Save As box. It is kind of a waist of time if we can't get it to work automatically.

Good luck. Let me know what you find.

Steve
 
Upvote 0
Steve,

I tried the code like you said and it still does not work. I am just about to give up on this.

Do I have to change all 4 places where it says OutputFile to a path???

Adam
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top