Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Macro to Print to PDF with Auto filename

  1. #1
    New Member
    Join Date
    Aug 2009
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to Print to PDF with Auto filename

    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!

  2. #2
    Board Regular
    Join Date
    May 2007
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to Print to PDF with Auto filename

    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
    Regards

  3. #3
    New Member
    Join Date
    Aug 2009
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to Print to PDF with Auto filename

    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...

  4. #4
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,944
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to Print to PDF with Auto filename

    Regards,

    Wigi

    http://www.wimgielis.com

    Excel MVP 2011-2014

  5. #5
    Board Regular
    Join Date
    May 2007
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to Print to PDF with Auto filename

    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.
    Regards

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •