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

[SteveF]

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

 

[2077delta]

I have two questions for you.

I have a user form that creates a listbox of all the custom views in my workbook. The listbox has the multiselct option enabled. I wrote a macro that prints the selected views. How would I modify your code to print views instead of worksheets and use the PDF995 feature of appending the file onto itself to have all views residing in one PDF file?

I also have a combobox open up when the user clicks the print button on the form. The combobox allows them to select the file name from a controlled list of filenames. How would I pass this along as a variable to your routine to substitute for the file name?

Thanks

 

[SteveF]

I'm not familiar with custom views in Excel. Based on a quick look at them, it appears you can save window settings and print options with each view. Are you intending for some views to print multiple sheets? If so, I'm not sure it will work as I defined the process. If you will be printing one sheet only in a view, I think it should work fine. Use this:

In order to capture the filename from a drop-down list, you will need to capture the selected item in either a program variable or a worksheet cell (whichever is easier). If you can get the filename into a defined cell, just grab its value with a statement like MyFileName = range("A1").value. Then pass MyFileName into the function.

Use this:
Set CS = ActiveSheet 'once the view is selected, one sheet will be active

MyFileName = range("A1").Value 'assuming the filename is in A1
Call SheetToPDF(CS, MyFileName)

 

[2077delta]

Thanks for the info. I already figured out how to capture the file name and print based on custom views, but my dilemma is the routine prints each view into a separate file. I know that PDF995 supports printing multiple items into one file. I'm assuming it keeps a temporary file out there some where that it appends and then writes to a new file. Is there some code in your routine, probably in the ini file portion, where you override printing everything into one file or do you know what code I would need to add to accomplish this?

 

[Jon Peltier]

Adam -

I know this is a relatively old thread, and you're probably off on other things, but someone else may stumble across it and not know why your code falls down. Here are extracts from the code you posted, which you modified from the original:

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.
------------------------------------------------------
'
'
'
tmpoutputfile = ReadINIfile("PARAMETERS", "C:\Documents and Settings\afrench\Desktop\testing.pdf", iniFileName)
'
'
'
x = WritePrivateProfileString("PARAMETERS", "C:\Documents and Settings\afrench\Desktop\testing.pdf", "C:\Documents and Settings\afrench\Desktop\testing.pdf", iniFileName)
'
'
'
Cleanup:
x = WritePrivateProfileString("PARAMETERS", "C:\Documents and Settings\afrench\Desktop\testing.pdf", tmpoutputfile, iniFileName)

You have messed up these lines of code. The syntax of these lines is:

x = WritePrivateProfileString("PARAMETERS", "Output File", tmpoutputfile, iniFileName)

The argument "Output File" must remain intact, because that is the name of the variable in the INI file which the program looks at to find the filename which is passed in the variable tmpoutputfile. Alternatively, if you hard code the file name, the last of your statements should be changed to this:

Cleanup:
x = WritePrivateProfileString("PARAMETERS", "Output File", "C:\Documents and Settings\afrench\Desktop\testing.pdf", iniFileName)

What your code does is create a new variable in the INI file with the variable name matching the file name you're trying to enter, and no assigned value.

 

[mehuljani]

Printing selected worksheets to pdfs with filename and date

Hi all,
I know this thread is a bit old now, but im trying my luck anyway. I'm not guru with VBA but am trying to automate a time consuming process I go through daily. I think the code you have might help, but can you let me know if this will solve what I am trying to do?

I have a workbook with a number of worksheets that need to be printed to PDF and/or saved down in separate excel workbooks daily. I have found a macro which lets me print the worksheets to a printer. I changed it so that it now prints to the Adobe PDF printer but I need to automate the file naming process as well.

Is there anyway I can automate this such that
1) It prints each of the selected worksheets separately
2) Does not prompt for pdf file name and automatically names each file with the worksheet name followed by the date

 

[moneytastesbad]

A few questions about this.

1. How can I change it to work with Adobe 6.0 Standard instead of PDF995
2. Can I just copy and paste the entire code into any module, or do they have to go a certain place?

Thanks,

Travis

 

[richarddec]

This is absolutly amazing! Thanks so much!!!!

 

[mictrine]

Thanks a lot!!!!!

Hi!

Just what i needed, everything works perfect!!!! Thanks!!!!
I paid the 10$ for the PDF995 without advertising...

I have a question. Is it possible to make several sheets to one PDF file?

 

[richarddec]

If you select all the sheets manually, then go to print, it should print all the sheets as one pdf.

 

[mwb]

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

I have just tried this tool and I am getting the prompt to click save. Can someone confirm, does the latest pdf995 use both of the ini files or only one? I can only find the pdf995.ini in program files\pdf995\, am I supposed to create additional ones in a new location "c:\pdf995\...? I am wondering if I have a newer version that does not use both ini's.

P.S. I have pdf995 version 8.0

This function is awsome, I already see huge changes to how we archive and publish excel documents using this.