Printing to PDF with an Almost Automatic Filename

[tsk]

I know this is similar to topics I saw here, unfortunately I have a slight twist...

I'm trying to write a macro in Excel to print the sheet to a PDF with a filename that is chosen from a cell. I would however like to give the user the option to make slight tweaks to the filename. The user does things in a fairly standard way and can avoid retyping everything if it's mostly automated.

I have been bungling around grabbing code and I have written a macro that does everything I want except pull up the prompt and allow changes to be made.

Is this possible? Here's what I've got so far.

Sub PrintPDF()

    Set MySheet = ActiveSheet

    Filename = ActiveSheet.Range("K7").Value
        tempPSFileName = Filename & ".ps"
        tempPDFFileName = Filename & ".pdf"
        tempLogFileName = Filename & ".log"
            MySheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
            Dim myPDFDist As New PdfDistiller
            myPDFDist.FileToPDF tempPSFileName, tempPDFFileName, tempShowWindow
        Kill tempPSFileName
        Kill tempLogFileName
End Sub

Thanks.

 

[Kenneth Hobson]

Welcome to the forum!

Do you need all of that?

e.g.

          i = i + 1
          DirectoryLocation = ThisWorkbook.Path
          Name = DirectoryLocation & "\" & Range("E4").Value2 & _
            "_" & i & ".pdf"
    
          ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name _
          , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
          :=False, OpenAfterPublish:=False

 

[tsk]

Thanks for the reply and the welcome. Your code seems to do the trick quicker, but it still doesn't do one thing I wanted which was to pull up the confirmation box before saving.

Is it possible to have the macro pop up the save dialog pre-populated an autoname in the dialogue box. The user wants to be able to confirm the name and make slight changes to the pre-defined format when necessary.

Welcome to the forum!

Do you need all of that?

e.g.

          i = i + 1
          DirectoryLocation = ThisWorkbook.Path
          Name = DirectoryLocation & "\" & Range("E4").Value2 & _
            "_" & i & ".pdf"
    
          ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name _
          , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
          :=False, OpenAfterPublish:=False

 

[Marc]

Try:

Application.Dialogs(xlDialogSaveAs).Show Filename, , , True

from: http://support.microsoft.com/kb/110670

 

[Kenneth Hobson]

Sub Test_PublishToPDF()
  Dim sDirectoryLocation As String, sName As String
  
  sDirectoryLocation = ThisWorkbook.Path
  sName = sDirectoryLocation & "\" & Range("E4").Value2 & ".pdf"
  PublishToPDF sName
End Sub

Sub PublishToPDF(fName As String)
  Dim rc As Variant
  
  'ChDrive "c:"
  'ChDir GetFolderName(fName)
  rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
  If Not rc Then Exit Sub
  
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName _
  , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
  :=False, OpenAfterPublish:=False
End Sub

Function GetFolderName(Filespec As String)  'Returns path with trailing "\"
'Requires GetFileName() function above
  GetFolderName = Left(Filespec, Len(Filespec) - Len(GetFileName(Filespec)))
End Function

Function GetFileName(Filespec As String)
  Dim FSO As Object, s As String
  Set FSO = CreateObject("Scripting.FileSystemObject")
  s = FSO.GetFileName(Filespec)
  Set FSO = Nothing
  GetFileName = s
End Function