Print to A PDF File

[p2936]

I want to be able to print an Excel sheet to a PDF file and save it. A present I can record a macro that prints the file to a PDF via CutePDF. But in this process it asks for a path to save the file. I would like to be able specify the path and file name in the code so that user doesn't need to. Can some one tell be how to do that.

The recorded macro code is

Application.ActivePrinter = "CutePDF Writer on CPW2:"
ExecuteExcel4Macro _
"PRINT(1,,,1,,,,,,,,2,""CutePDF Writer on CPW2:"",,TRUE,,FALSE)"

 

[Michael M]

hi
This will do Adobe PDf to a folder.
You will have to substitute your "CutePDF" info.

Sub NuCreate_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
 'MsgBox "Under Construction, Try Again Later"
 'Exit Sub
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
Dim mypdfDist As New PdfDistiller
For Each wsEachSheet In ThisWorkbook.Worksheets
    tempPDFRawFileName = "G:\Temp\" & ActiveWorkbook.Name 'Use you path and filename here
    tempPSFileName = tempPDFRawFileName & ".ps"
    tempPDFFileName = tempPDFRawFileName & ".pdf"
    tempLogFileName = tempPDFRawFileName & ".log"
 
'Prints workbook 'Output' as a pdf, an array of sheets can be printed if required
 
    ActiveWorkbook.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
        printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
    mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
 
    Kill tempPSFileName
    Kill tempLogFileName
Next wsEachSheet
Set mypdfDist = Nothing
End Sub

 

[Nooblet]

The macro that I use to print multiple sheets to PDF is:

Sub PDFPrint()

For j = 3 To 22
Sheets(j).Activate

If Range("A27").Value <> 0 Then

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

pdfName = Range("F16").Text
Filename = ThisWorkbook.Path & "\" & pdfName

SendKeys Filename & "{ENTER}", False
 
End If

Next j

End Sub

The text in red is specific to my needs, but you should be able to get the idea of it from this.

Alter the second group of red text to nominate the filename/path that you want.