Excel VBA - Create PDF filename ?

[mr_nismo]

Does anyone know why PrToFileName:="filename" doesn't work when doing a ActiveWindow.SelectedSheets.PrintOut when your active printer is Adobe PDF writer ?

 

[mr_nismo]

Sorry I should explain more .... it does create the filename but the file is unusable. If u leave off the prntofilename it will prompt for a filename and it works fine. How do I automate it ?

 

[bozzo]

I am no VBA expert but I was looking for that last week and found it. I assign a folder where to save it ("Path") and my filename is in Sheets("Sheet1").Range("B1").Value inside my workbook.

The way it works is that it first saves it as a Postscript file (if you change the extension of your unsuable PDF for PS, it should open it), then converts the PS into a PDF and kills the PS. It also deletes the .log that Distiller creates when converting PS into PDF. Make sure you have the proper extensions checked ad you know where is your PDFPrinter (mine is on Ne01).


bozzo



Private Sub Create_PDF_Click()

Dim Path As String
Dim Acro As New ACRODISTXLib.PdfDistiller
Dim KPathPs As String
Dim Logpath As String

Path = "C:\folder_to_save_PDF\"
Application.ActivePrinter = "Adobe PDF on Ne01:"
ActiveSheet.PrintOut _
Copies:=1, _
Collate:=True, PrToFileName:=Path & Sheets("Sheet1").Range("B1").Value & ".ps"

Set Acro = New ACRODISTXLib.PdfDistiller
Acro.bShowWindow = False

'create pdf
Acro.FileToPDF Path & Sheets("Sheet1").Range("B1").Value & ".ps", Path & Sheets("Sheet1").Range("B1").Value & ".pdf", ""
KPathPs = Path & Sheets("Sheet1").Range("B1").Value & ".ps"
Logpath = Path & Sheets("Sheet1").Range("B1").Value & ".log"
'remove ps file
Kill KPathPs
Kill Logpath

Set Acro = Nothing

End Sub

 

[mr_nismo]

Funny you should mention this ... I have tryed this also but my post script file comes out with a load of junk in it ... therefore it fails to do the pdf conversion. I'll check my code with yours but my process seems ok its just the post script creation is not quite right ?? is there any settings u need to have in Writer or Distiller ?

 

[mr_nismo]

Your a champion !!! I checked my code against yours and mine now works, I had 2 very slight differences in my code. Thankyou so much u have saved my life at work ... I need to print about 600 pdfs from my worksheet.

Cheers

 

[Genius_Boy]

hi
I have an sheet by many "break page". my break pages generate by vba from a special value.
Now , if I want get a file name for each break page from a value ( like a field after "CODE" field) which method must be use?
I print my worksheet as separate file , for each BREAK PAGE.
thanks