Creating Individual PDF file from Excel Sheets

[tbiven78]

Good Afternoon Everyone,

This is my first time posting and appreciate any help i can get, I have found this site to be incredibly valuable in my evolution as an Excel and Access user.

Ok Essentially what I'm looking for is an example of VB code to Save multiple sheets in Excel as individual PDF files in a specific folder...

For Example:

My workbook has three sheets: Sheet A, Sheet B, & Sheet C

What I would like to do is run a macro that would save each individual Sheet as it's own PDF file in a folder I specify when running the macro. So essentially the end product would be Sheet A.pdf Sheet B.pdf and so on in a shared folder.

Again I thank everyone who contributes to this site. It is amazing how much one can learn when people share knowledge.

Thanks,

Tim

 

[Datsmart]

Never tried it myself, but this may help.

http://www.primopdf.com/

 

[MrTinkertrain]

You might find this page very useful :

http://www.excelguru.ca/node/22#MultiMulti

 

[tbiven78]

Tinker,

Thanks for the response. I'm trying to fumble my way through the macro as best i can in order to decipher what's going on with it. I also and am waiting on my IT department to install adobe as All i have is the PrimoPDF creater.

If you or anyone else has any more suggestions in the mean time keep them coming. I've spent most of my afternoon trying to teach a newbie about Matching and Indexing..... Teaching someone to fish is much more difficult when all they want you to do is catch fish for them..... Been a long distracting afternoon.

Will update tomorrow on my progress.

Thanks,

Tim

 

[tbiven78]

I get an issue with "Liscensing component not found when i change to the following code:

Sub PrintToPDF_MultiSheet_Late()
Dim pdfjob As Object
Dim sPDFName As String
Dim sPDFPath As String
Dim lSheet As Long
Set pdfjob = CreateObject("PrimoPDF.clsPrimoPDF")
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PrimoPDF.", vbCritical + _
vbOKOnly, "PrtPrimoPDF"
Exit Sub
End If
For lSheet = 1 To ActiveWorkbook.Sheets.Count
'Check if worksheet is empty and skip if so
If Not IsEmpty(ActiveSheet.UsedRange) Then
With pdfjob
'/// Change the output file name here! ///
sPDFName = Sheets(lSheet).Name & ".pdf"
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With

'Print the document to PDF
Worksheets(lSheet).PrintOut copies:=1, ActivePrinter:="PrimoPDF"

'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False

'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
End If
Next lSheet
pdfjob.cClose
Set pdfjob = Nothing
End Sub


Please excuse my ignorance to the VB coding I tried my best to change what i could and then run..... Any idea what my problem could be?

 

[MrTinkertrain]

The code on Ken Puls' site I gave you is based on the open source software PDFCreator.
You must first download and install that before you can use the code on Ken's site.

You can download PDF Creator here :

http://sourceforge.net/projects/pdfcreator/

 

[tbiven78]

Ok so I downloaded the PDF Creater and had it working in a different file.

When i added it to the new file(new Month) I keep getting the error "Can not initialize PDF Creator" which is the messagebox in the code where I'm having the issue. It may be I'm having a bad day but i can't seem to wrap my brain around the issue.

I'm certain the fact that it worked before(I was only allowed to do about 6-10 pdf creations at once) and now i'm having issues in the new file is certainly a me issue......

My code is below. Any thoughts?

Option Base 1
Sub PrintToPDF_MultiSheet_Late()
Dim pdfjob As Object
Dim sPDFName As String
Dim sPDFPath As String
Dim lSheet As Long
Dim vShList As Variant
Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")
sPDFPath = GetFolder(ActiveWorkbook.Path)
If sPDFPath = "" Then Exit Sub
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + vbOKOnly, "PrtPDFCreator"
Exit Sub
End If
Application.ScreenUpdating = False
vShList = Range("ShList")
For lSheet = 1 To UBound(vShList)
'Check if worksheet is empty and skip if so
If vShList(lSheet, 2) = "Yes" Then
Sheets(vShList(lSheet, 1)).Activate
If Not IsEmpty(ActiveSheet.UsedRange) Then
With pdfjob
'/// Change the output file name here! ///
sPDFName = vShList(lSheet, 1) & ".pdf"
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With
'Print the document to PDF
Worksheets(vShList(lSheet, 1)).PrintOut copies:=1, ActivePrinter:="PDFCreator"
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False
'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
End If
End If
Next lSheet
pdfjob.cClose
Set pdfjob = Nothing
Sheets("SHEETSLIST").Select
Range("A2").Select
End Sub
Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function
Sub GetShnames()
Dim lSheet As Long
For lSheet = 1 To ActiveWorkbook.Sheets.Count
Cells(lSheet + 1, 1) = Sheets(lSheet).Name
Next lSheet
End Sub

 

[tbiven78]

I'm a bonehead.... needed to reassign the macro.... I need more sleep or more coffee one of the two.

Thanks everyone for your help