Macro to Print to PDF with Auto filename

[Calibane]

Hey guys,

I'm trying to write a macro which prints to PDF and saves the file name as the contents of a cell. I've been looking through all the posts currently on this forum to get something working. I'm using the following code -

Sub PrintPDF()
Filename = "C:\Documents and Settings\samb\My Documents\" & ActiveSheet.Range("Z1").Value
SendKeys Filename & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF:", Collate:=True
End Sub

The macro prints to PDF, but then it stops at the Save As stage, where I have to manually enter the name of the file and click Save. I want the macro to automatically name the file with the contents of cell Z1. I then want it to then automatically press enter. Any ideas where I'm going wrong? Any help would be much appreciated!

 

[goofy78270]

Try something like this:

Option Explicit
Public PDFConverter As AdobePDFMakerForOffice.PDFMaker
Public PDFOptions As AdobePDFMakerForOffice.ISettings
Private Sub CreatePDFThroughPDFMaker()
Dim PDFAddInIndex As Integer
Dim PDFAddIn 'As COMAddIn
Dim OriginalFile As String
Dim NewFile As String
Dim temp As Object
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo ErrHandler
For PDFAddInIndex = 1 To Application.COMAddIns.Count
If Application.COMAddIns(PDFAddInIndex).Description = "Acrobat PDFMaker Office COM Addin" Then Exit For
Next PDFAddInIndex
If PDFAddInIndex > Application.COMAddIns.Count Then
MsgBox "You must have Adobe Professional 8 or later installed!", vbCritical, "Adobe Professional NOT Installed"
Else
Set PDFConverter = Application.COMAddIns(PDFAddInIndex).Object
PDFConverter.GetCurrentConversionSettings PDFOptions
 
PDFOptions.AddTags = True
PDFOptions.AddLinks = True
PDFOptions.AddBookmarks = True
PDFOptions.FitToOnePage = True
PDFOptions.OutputPDFFileName = "filename.pdf"
PDFOptions.PromptForPDFFilename = False
PDFOptions.ShouldShowProgressDialog = True
PDFOptions.ViewPDFFile = False
PDFOptions.PromptForSheetSelection = False
 
PDFConverter.CreatePDFEx PDFOptions, 0
End If
Exit Sub
'**********************************Error Handler**********************************
ErrHandler:
'A general error message
MsgBox "Macro Name: CreatePDFThroughPDFMaker" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description
 
Err.Clear 'clear the error
 
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[Calibane]

Err... thanks but that's not what I was looking for.... I can't even see where that macro references a cell for the file name...

 

[wigi]

Double post: http://www.mrexcel.com/forum/showthread.php?t=411130

 

[goofy78270]

The file name is built into the macro. You can simply change it to reference whatever cell or user input you desire.

Current code:

PDFOptions.OutputPDFFileName = "filename.pdf"

Code Options

//Read from a cell
PDFOptions.OutputPDFFileName = Sheets("worksheet name").Cell(Row, Column)
 
//User Input
PDFOptions.OutputPDFFileName = Inputbox(Prompt:="Please enter a filename", Type:=2)

NOTE: If a full path name is not given, the file will be placedin the same location as the current file.