Save Excel Sheet As PDF File (SOLVED)

[Domski]

Hi,

I've been working on this problem on and off for a number of months now and have just about got it sussed so thought I'd share it with you as it has mostly been down to postings on this board that I've got it in the end.

Thanks especially to 'biggoan' for his post: http://www.mrexcel.com/board2/viewtopic.php?t=199136&highlight=

Anyway, this seems to avoid the need for a class module but does need you to install the Acrobat Distiller object references in Tools...References in the VBA editor.

You also need to go into the printer properties of the your Adobe PDF 'Printer' and under Printing Preferences...Adobe PDF Settings deselect the Do not send fonts to "Adobe PDF" option. Why, who knows!

Private Sub Create_PDF()

'Created by Dom Hill with considerable asistance from Biggoan and Mr Excel

Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String

Sheets("Sales Data").Activate

tempPDFRawFileName = "C:\" & Range("A1").Value

'Define the postscript and .pdf file names.

tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"

' Print the Excel range to the postscript file

ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, Collate:=True, prtofilename:=tempPSFileName

'Create PDF File

Dim myPDFDist As New PdfDistiller
myPDFDist.FileToPDF tempPSFileName, tempPDFFileName, tempShowWindow

'Delete PS File

Kill tempPSFileName
Kill tempLogFileName

End Sub

Not sure why the macro creates a log file but if you know more about VBA then you probably would.

Hope it helps someone else as it's going to save me a heap loads of time.

 

[Domski]

Thought I'd post the latest version of the code. Only a couple of minor changes but seems to have ironed out a couple of problems:

Sub Create_PDF()
 
'Dom Hill, June 2008
'This code has only been tested with Adobe Acrobat 6.0 Professional
'For this code to work the Adobe Distiller VBA library must be enabled
'Select Tools...References in the VBA editor and check it from the list
 
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
 
'define file name and path in subdirectory of file where code workbook
'is stored called 'Saved Files' and picks file name up from a cell
'named as a named range call 'File_Name'
 
tempPDFRawFileName = ThisWorkbook.Path & "\Print Jobs\" & Range("File_Name")
 
tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"
 
'Prints worksheet 'Output' as a pdf, an array of sheets can be printed if required
 
Sheets("Output").PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
 
Dim mypdfDist As New PdfDistiller
 
mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
 
Kill tempPSFileName
Kill tempLogFileName
 
End Sub

Dom

 

[sassy]

I can not find

Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String

listed in the Tool references in VA Editor. I have 07', does that make a difference?

also, if i just want to save publish the worksheet and not the entire workbook, will that work in this macro?

 

[sassy]

I can not find

Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String

listed in the Tool references in VA Editor. I have 07', does that make a difference?

also, if i just want to save publish the worksheet and not the entire workbook, will that work in this macro?

 

[XLnate]

Hey guys I found this code like you and modified it to my needs. I love it!!! Saved me month of productivity by now. Here is my code, I won't explain all the pieces but I did get it to work with multiple sheets which could easily be adjusted for an entire work book

'Before using this code you must turn Adobe code references on by going to Tools > References and then selecting all boxes refering to Adobe or PDF
'You must also turn off the "Rely on system fonts only; do not use document fonts" option on the Adobe PDF printer

Dim PRD As String
PRD = "July" 'Set Month Name in file here

Dim Folder_Extension As String
Folder_Extension = "R:\OBA\Forecasting\20906 Forecasts\Exports\" 'Set Folder Extension here



'Northeast


' Define the postscript and .pdf file names.
Dim PSFileName As String 'This statement only needed once (first) in code so omitted from rest of locations
Dim PDFFileName As String 'This statement only needed once (first) in code so omitted from rest of locations

PSFileName = Folder_Extension & PRD & " NE Forecast " & Format(Date, "mmddyy") & ".ps"
PDFFileName = Folder_Extension & PRD & " NE Forecast " & Format(Date, "mmddyy") & ".pdf"

' Print the Excel range to the postscript file

Sheets(Array("TOTAL", "NE", "10", "21", "22", "23", "37", "Graph", "Labor Ranking", "Tables and Trend", "NE Trend")).PrintOut copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, _
collate:=True, prtofilename:=PSFileName

Application.ActivePrinter = "Adobe PDF on Ne02:"


' Convert the postscript file to .pdf

Dim myPDF As PdfDistiller 'This statement only needed once (first) in code so omitted from rest of locations
Set myPDF = New PdfDistiller 'This statement only needed once (first) in code so omitted from rest of locations
myPDF.FileToPDF PSFileName, PDFFileName, ""

 

[Domski]

'For this code to work the Adobe Distiller VBA library must be enabled

Not tested this on 2007 as I don't have Adobe Pro at home. The Dim statements are declaring variables used later on in the code. It may be listed as Acrobat Distiller in the list of available libraries but I'm afraid I'm not sure.

This bit of the code declares the sheet to print:

Sheets("Output").PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, Collate:=True, prtofilename:=tempPSFileName

Hope it helps,

Dom

 

[kakan]

Hi honourable guru

I have tried various bits of coding from this thread, I know it shows as solved not really familiar with using these threads. But I am at wits end. The coding here does work with a bit of tweeking. However it only really seems to duplicate what I have. I have some code that allows me to select the sheets for printing (as some are instructions etc not needing printing, plus a template that creates new worksheets for each worker) The problem I have is that I can not make the worksheet name appear in the save as box. It always presents the workbook name not each sheet. This is similar to what I get with any of this coding.

Here is my code

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False
' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0
' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i
' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240
' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With
' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront
' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut
' ActiveSheet.PrintPreview 'for debugging
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If
' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete
' Reactivate original sheet
CurrentSheet.Activate
End Sub
'NOTE: The following modification, which prints the selected sheets as a single print job. This allows the sheet to be printed with continuous page numbers.
'
' If PrintDlg.Show Then
' For Each cb In PrintDlg.CheckBoxes
' If cb.Value = xlOn Then
' Worksheets(cb.Caption).Select Replace:=False
' End If
' Next cb
' ActiveWindow.SelectedSheets.PrintOut copies:=1
' ActiveSheet.Select
'End If

Can any one give me an idea of how to modify to get it to only save the PDF files by worksheet name and present each time the save as screen appears rather than only present the same file name each time.

If any of you have successfully done this with your own code If you could post it that would be great. i have got myself totally lost with modifying the various bits here and am at wits end.

 

[Domski]

Hi,

I can't see your code bears any resemblance to any of the code that I posted in this thread.

If you just want help modifying your code I'd start a new thread and try to remember to use code tags when posting code.

Dom

 

[kakan]

My apologies domski

I have started from scratch again with the script of yours as modified by you(jan 2005). I am geting the following error now. Have checked all of the adobe / pdf items in the references area. Have also tried specify the range in the spreadsheet. Any ideas. I only posted the other as it did much the same as the scripts all of you have here thought it maybe useful also, but as I said I am newby to these thread thingos

tempPDFRawFileName = ThisWorkbook.Path & "\Print Jobs\" & Range("File_Name")
'*receive by kakan Run-time error '1004': method'range' of object'_global' failed'* debug to the above item in the code.

cheers

ron

 

[Domski]

No worries.

The range File_Name should just refer to a single cell within one of your worksheets that contains the name you want to call your pdf file without the .pdf

Dom

 

[kakan]

Thanks Domski

I thought that was the case - will work on it see what I have done age is a terrible thing. Domski I am trying to actually get this to work from the worksheet names. I have a template that is recreated using employee names and names each worksheet based on name of employees. What I want to do is get this macro to use the worksheet name instead of the cell reference (yes I probably could just hide the cell referenced in the sheet). Just wondered if you have been able to do it based on worksheet name. I cant use the array mechanism in the thread as the names can change with each day and also from team to team. Any suggestions mate.

cheers and thanks heaps.
Ron