Macro to Print to PDF with Auto filename

Calibane

New Member
Joined
Aug 19, 2009
Messages
10
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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

goofy78270

Well-known Member
Joined
May 16, 2007
Messages
555
Try something like this:

Code:
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

New Member
Joined
Aug 19, 2009
Messages
10
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...
 

goofy78270

Well-known Member
Joined
May 16, 2007
Messages
555
The file name is built into the macro. You can simply change it to reference whatever cell or user input you desire.

Current code:
Code:
PDFOptions.OutputPDFFileName = "filename.pdf"

Code Options
Code:
//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.
 

Forum statistics

Threads
1,181,647
Messages
5,931,209
Members
436,784
Latest member
amuljono

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
Top